Store SQL Statements External File

Store SQL Statements External File

If you are using spring framework and looking for technique to store SQL Statements in external file to keep code clean from big SQL statements and make it flexible to change by other people as well who is not familiar to coding.

I would suggest you to create XML file and include bean definition with constructor HahsMap as an argument so that you could include multiple entries in it and dynamically get its value using key. Below is detail sample example:

• Create sample maven StoreSQLOutSide project structure:

•  Sample queries.xml with one bean name: SQLFactory:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans    
		http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="queries" class="com.javahonk.util.SQLFactory">
		<constructor-arg>
			<map>
				<entry key="SAMPLE_QUERY1" value="INSERT into TestDataTable values(?,?,?)" />
				<entry key="SAMPLE_QUERY1" value="select * from TestTable where name=? and businessDate=?" />
				
				<!--Procedures -->
				<entry key="SAMPLE_PROC_WITHOUT_PARAMETER" value="GetTestData" />
				<entry key="SAMPLE_PROC_WITH_PARAMETER" value="exec TestEvents ?" />
			</map>
		</constructor-arg>
	</bean>
</beans>

• SQLFactory.java where constructor is loaded with one HashMap parameter

package com.javahonk.util;

import java.util.HashMap;

public class SQLFactory {

private static HashMap<String, String> sqlQueryMap;
	
	public SQLFactory(HashMap<String, String> sqlQueryMap) {

		SQLFactory.sqlQueryMap = sqlQueryMap;
	}

	public static String getQueryById(String queryId) {
		String query = null;
		query = (String) sqlQueryMap.get(queryId);
		return query;
	}
	
	public static HashMap<String, String> getAllQueryFromFile() {
		return sqlQueryMap;
	}
}

• To show you how to retrieve SQL form query.xml file. I have added method in controller to get SQL queries:

package com.javahonk.controller;

import java.util.HashMap;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.javahonk.util.SQLFactory;


@Controller
public class SpringMVCController {
	
	@RequestMapping(value = "/getQueryFromFile/{queryKey}")
	public @ResponseBody String getQueryFromFile(@PathVariable String queryKey) {
		
		return SQLFactory.getQueryById(queryKey);		
	}
	
	@RequestMapping(value = "/getAllQueryFromFile")
	public @ResponseBody HashMap<String, String> getAllQueryFromFile() {
		
		return SQLFactory.getAllQueryFromFile();		
	}
	
}

• dispatcher-servlet.xml where we are importing queries.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans     
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd">

	<context:component-scan base-package="com.javahonk.controller" />
	<mvc:resources mapping="/static/**" location="/static/" />
    <mvc:annotation-driven/>
    <import resource="classpath:queries.xml" />	

</beans>

• Download project and run this on any server. Please use below sample URL to see what queries are there in the file:

• That’s it. For more details please refer spring documentation here

  Download Project:  StoreSQLOutSide