Clean way to externalize long (+20 lines sql) when using spring jdbc?

Question

I want to externalize some large queries in my application to properties\sql\xml files. However I was wondering if anyone has some recommendations as to how achieve this in a clean fashion. Most results recommend using an ORM framework but this isn't applicable due to some data constraints.

I took a look at: Java - Storing SQL statements in an external file but doing this propertyname .1, .2 etc for a few queries that are each longer that 20 lines does not seem that clean.

Answer 1

You can put your queries in a xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

<properties>
<entry key="getPersonById">
    <![CDATA[
        Select Name From Person 
        Where Id =?     
    ]]>

</entry>    
<entry key="getPersonBySSN">
    <![CDATA[

    ]]>
</entry>

</properties>

In Spring application Context, load this xml file

<bean id="queryProps" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="locations" value="classpath:/queries.xml" />
</bean>

Inject this bean in your DAO class

<bean id="myDAO" class="com.xyz.dao.MyDAOImpl">
  <property name="queryProps" ref="queryProps" />
</bean>

Define queryProps in your DAO class and don't forget to have setter method for this

 private Properties queryProps;

Now you can access the query in your DAO like this -

 String query = queryProps.getProperty("getPersonById");

Hope this helps.

Answer 2

I faced the same issue some time ago, and came up with YAML. It supports multi-line string property values, so you can write something like this in your query files:

selectSomething: >
  SELECT column1, column2 FROM SOMETHING

insertSomething: >
  INSERT INTO SOMETHING(column1, column2)
  VALUES(1, '1')

Here, selectSomething and insertSomething are query names. So it's really convenient and contains very few special characters. Queries are separated by blank lines, and each query text must be indented. Note that queries can absolutely contain the indentation of their own, so that the following is perfectly valid:

anotherSelect: <
  SELECT column1 FROM SOMETHING
  WHERE column2 IN (
    SELECT * FROM SOMETHING_ELSE
  )

You can then read the contents of the file into a hash-map with the help of SnakeYAML library, using the code below:

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.FileUtils;
import java.io.FileReader;

import org.yaml.snakeyaml.Yaml;
import java.io.File;
import java.io.FileNotFoundException;

public class SQLReader {
  private Map<String, Map> sqlQueries = new HashMap<String, Map>();

  private SQLReader() {
    try {
      final File sqlYmlDir = new File("dir_with_yml_files");
      Collection<File> ymlFiles = FileUtils.listFiles(sqlYmlDir, new String[]{"yml"}, false);
      for (File f : ymlFiles) {
        final String fileName = FilenameUtils.getBaseName(f.getName());
        Map ymlQueries = (Map)new Yaml().load(new FileReader(f));
        sqlQueries.put(fileName, ymlQueries);
      }
    }
    catch (FileNotFoundException ex) {
      System.out.println("File not found!!!");
    }
  }
}

In the example above a map of maps is created, mapping each YAML file to a map containing query names/strings.

Answer 3

This is in addition to what Pankaj has answered. This one doesn't have CDATA in the Properties XML and uses autowiring. I had to add this as answer, as I cannot format code if I had to do this in comment section.

Make sure you have following namespace in spring application context xml file.

xmlns:util="http://www.springframework.org/schema/util

Add the following bean to spring application context xml

<util:properties id="sqls" location="classpath:oracle/sqls.xml" />

Contents of the file sqls.xml is

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Employee Queries</comment>
    <entry key="employee.insert">
        INSERT
        INTO EMPLOYEE
          (
            ID,
            NAME,
            AGE,
            DEPARTMENT
          )
        VALUES
          (
            EMPLOYEE_SEQ.NEXTVAL,
            ?,
            ?,
            ?
          )
    </entry>
</properties>

Autowired the properties as below

@Autowired
@Qualifier("sqls")
private Properties sqls;

Code to get the sql query from Properties

String sql = sqls.getProperty("employee.insert");

Answer 4

You can do multiline queries in a properties file by putting a \ at the end of the line. For example

queries.myquery = select \
foo, bar \
from mytable \
where baz > 10

Answer 5

Take a loot at JdbcTestUtils and the methods "executeSqlScript" and "readScript".