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.