How to externalize the queries to xml files using spring

Question

I am using spring and their JDBC template to do read/write operations to the database. I am facing a problem in my reporting module that i have to frequently change the query sqls to cater to frequent changes.

Though using spring jdbc ORM, is there a way to externalize my query parameters such that i just change it in the XML & restart and there is no need to rebuild my source again for deployment. Any approach ORM (preferred) or simple Sql will do. As of now i have to change the query again and again ,rebuild the source and deploy.

Answer 1

I am not sure if Spring provides some out of the box solutions to implement what you want. But here is one way to get it done, which i had implemented ones. So i will try to reduce some hardwork for you.

You might need to implement a utility to load from resources xml file. Something like this.

public final class LoadFromResourceFileUtils {


public static String loadQuery(final String libraryPath,
        final String queryName) {
    final InputStream is = StreamUtils
            .streamFromClasspathResource(libraryPath);
    if (is == null) {
        throw new RuntimeException(String.format(
                "The SQL Libary %s could not be found.", libraryPath));
    }
    final Document doc = XMLParseUtils.parse(is);
    final Element qryElem = (Element) doc.selectSingleNode(String.format(
            "SQLQueries/SQLQuery[@name='%s']", queryName));
    final String ret = qryElem == null ? null : qryElem.getText();
    return ret;
}

}

You would need to store your queries in an XML say queries.xml and keep it in your classpath, for e.g

<?xml version="1.0" encoding="UTF-8"?>
<SQLQueries>
    <SQLQuery name="myQuery">
        <![CDATA[
            your query
        ]]>
    </SQLQuery>
</SQLQueries>

And in your DAO you can do this to get the query

String query = LoadFromResourceFileUtils.loadQuery(
        "queries.xml", "myQuery");

XMLParseUtils and StreamUtils for your reference

    public final class XMLParseUtils {

    public static Document parse(final InputStream inStream) {
        Document ret = null;
        try {
            if (inStream == null) {
                throw new RuntimeException(
                        "XML Input Stream for parsing is null"); 
            }
            final SAXReader saxReader = new SAXReader();
            ret = saxReader.read(inStream);
        } catch (final DocumentException exc) {
            throw new RuntimeException("XML Parsing error", exc); 
        }
        return ret;
    }
}

    public final class StreamUtils {
    public static InputStream streamFromClasspathResource(
            final String resourceClassPath) {
        final Class<StreamUtils> clazz = StreamUtils.class;
        final ClassLoader clLoader = clazz.getClassLoader();
        final InputStream inStream = clLoader
                .getResourceAsStream(resourceClassPath);
        if (inStream == null) {
            if(LOGGER.isDebugEnabled()){
                LOGGER.debug(String.format("Resource %s NOT FOUND.",
                    resourceClassPath));
            }
        }
        return inStream;
    }

}