I am a big Hibernate fan, but sometimes you can’t use Hibernate. When you can’t, Spring’s JDBC support is awesome. One of the reasons I encounter frequently for not being able to use Hibernate is some ridiculously complicated queries (I’m looking at you, Peoplesoft). When you have a giant long SQL query, you often encounter some code that looks like this:

String sql = “SELECT ” +
“RM.RTITLE1, ” +
“RM.RNAME, ” +
“SR.USER_SEQ_NO, ” +
“SR.INT_SEQ_NO AS SUBREQ, ” +
“SRT.USER_SEQ_NO, ” +
“SRT.TEXT, ” +
“(SELECT SRA.COURSE FROM SUB_REQ_AR SRA WHERE SR.INT_SEQ_NO = SRA.SUB_INT_SEQ_NO AND SRA.USER_SEQ_NO = 1) AS COURSE ” +
“FROM DPROG D JOIN DPROG_REQ DR ON D.INSTIDQ = DR.INSTIDQ ” +
“AND D.INSTID = DR.INSTID ” +
“AND D.INSTCD = DR.INSTCD ” +
“AND D.DPROG = DR.DPROG ” +
“AND D.DPFYT = DR.DPFYT JOIN REQ_MAIN RM ON D.INSTIDQ = RM.INSTIDQ ” +

Ugly, isn’t it? Well, some of my coworkers that were running into this came up with the idea of putting the SQL into its own files. It is a great idea and I doubt they are the first to think of it. They don’t use Spring, though, and I thought Spring would be able to provide some good glue to put it all together.

It turns out, Spring has something called Resource that can automatically load things from various places. I chose to use the classpath, and put the SQL files into the same package as the DAO. The only problem here, is with Maven, you end up with the SQL file in src/main/resources and the code in src/main/java, which is a little annoying. The Resource object doesn’t get the whole job done either; it just provides an InputStream, and having to write code to read the contents of the file has no place in my elegant solution. So, this is where commons-io comes into play. In the setter for the Resource object, I use IOUtils to read the file and stick it in a String.

So, the overall strategy goes like this:

• Put SQL in file in src/main/resources
• Create DAO object with a Resource setter for the SQL file
• Wire the SQL file resource in your applicationContext.xml
• Read in the file using IOUtils in the setter

Here’s the DAO definition in applicationContext.xml

<bean id="darsCourseDao" class="edu.asu.eadvisor.onoff.dao.DarsCourseDaoImpl">
 <property name="dataSource" ref="darsDataSource">
 <property name="subreqsForCourseQuery" value="classpath:/edu/asu/eadvisor/onoff/dao/GetSubreqForCourse.sql">
</property>
</property></bean>

And the setter in the DAO looks like this:

    public void setSubreqsForCourseQuery(Resource subreqsForCourseQuery) throws IOException {
                InputStream is = subreqsForCourseQuery.getInputStream();
                try {
                        this.subreqsForCourseQuery = IOUtils.toString(is);
                } finally {
                        IOUtils.closeQuietly(is);
                }
        }