By Jack Dans


2013-03-15 12:53:08 8 Comments

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.

5 comments

@Arun B Chandrasekaran 2014-06-10 12:47:06

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");

@LeOn - Han Li 2016-04-06 19:27:41

I think you will need the <![CDATA[ tag IF your SQL contains something comparison keyword like <, > which are reserved chars in xml. Without enclosed inside CDATA, Spring would throw exception when load the application context.

@Arun B Chandrasekaran 2016-04-07 14:02:54

You can use &gt; for > and &lt; for <.

@LeOn - Han Li 2016-04-07 14:30:40

Yes, that kind of defeat the purpose of this IMHO. One benefit of externalize this way is we can clear see the query and also we can copy the sql directly and paste into a sql tool and run. MY 2 CENTS.

@Dhruvil Thaker 2017-06-01 09:37:52

just to add one more point, it is also necessary to mention the same in schemaLocation (@ArunBC maybe you could edit your answer) xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0‌​.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.x‌​sd">

@siphiuel 2015-06-09 13:24:29

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.

@LoganMzz 2013-10-23 10:27:33

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

@Pankaj 2013-10-02 00:41:23

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.

@Malvon 2014-03-30 21:01:26

This solution is very helpful in cases where DBA applying his/her updates then save it back to the file. Of course, you can employ some sort of change listener for new updates and have the XML property files deployed outside of WEB-INF and another property file visible to your application that reads a location of where the XML property file is located.

@Arun B Chandrasekaran 2014-06-10 12:33:34

It worked without CDATA for me.

@lpacheco 2015-03-06 17:24:14

That's cool and solves the problem, but I would rather have a .sql file per query and having them stored together with the Java code that will run them. And I think it would be nicer to have the query file loaded from the Java code, avoiding the Spring configuration in XML files. Is that possible?

@John 2017-09-07 14:59:06

Hello, Do you know how to do exactly the same with java Config (with springboot annotations) ? I have tried but I don't know much about Spring.. I have directly started my learning by springboot..

@RV. 2019-01-07 23:35:08

This example is perfect. I just one thing. How can you resolve the table name using a table name resolver (Custom java Util class that returns the full table name with schema when shortname of table is passed as input.)?

@Adam 2013-03-15 18:16:29

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

Related Questions

Sponsored Content

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

9 Answered Questions

[SOLVED] Correct way to add external jars (lib/*.jar) to an IntelliJ IDEA project

  • 2009-06-26 22:58:35
  • knorv
  • 779066 View
  • 663 Score
  • 9 Answer
  • Tags:   java intellij-idea

11 Answered Questions

[SOLVED] Dealing with "Xerces hell" in Java/Maven?

8 Answered Questions

[SOLVED] Any way to declare an array in-line?

  • 2009-07-20 14:44:51
  • DivideByHero
  • 305256 View
  • 582 Score
  • 8 Answer
  • Tags:   java arrays

1 Answered Questions

1 Answered Questions

[SOLVED] Is it possible to add SQL comments to a query built with the ORM?

1 Answered Questions

Is there a clean way to read embedded SQL resource files?

Sponsored Content