How to externalize SQL into separate sql files for JDBC queries

The other day I was working on a project where I had to troubleshoot some sql that was nested in a java class file. After copying and pasting the sql statement, I had to spend sometime removing all of the concatenation symbols used to make it readable on a java page…you know instead of having one long line scrawled across the page. After I fixed it,  I had to go back and re-add all of those nasty “+” symbols and extra quotes. I thought to myself wouldn’t be nice if I could just store that sql in a separate file like a normal sql file.

So, I came up with idea of a Java class that could reference those files and read them back in. Its a first stab at it but it worked and made debugging the sql file much easier as well as easy to read.

package com.mdrsolutions.dao.sql;

import java.io.InputStream;
import java.io.InputStreamReader;
import com.google.common.io.CharStreams;
import com.google.common.io.Closeables;
import java.io.BufferedReader;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.springframework.core.io.FileSystemResource;

public class SqlCalls {

    public static final String RETRIEVE_IMAGE_ALL_SQL = "db/sql/retrieve_group_all.sql";
    public static final String UPDATE_IMAGE_AUDIT_SQL = "db/sql/update_group.sql";

    private SqlCalls() {}

    /**
    * Method takes a file path String and returns the String representation of
    * that sql file
    *
    * @param filePath of location of the sql file
    * @return a String of the contents of the SQL file
    */
    public static final String getSql(String filePath) {
        String sql = "";

        try {
          //get the file stream object
          InputStream is = SqlCalls.class.getClassLoader().getResourceAsStream(filePath);

          //convert to String and close stream
          sql = CharStreams.toString(new InputStreamReader(is));
          if (null == sql || sql.isEmpty()){
            Closeables.closeQuietly(is);
            throw new IOException("File path to SQL file could not be read!");
          }

          Closeables.closeQuietly(is);

          return sql;
       } catch (IOException ex) {
           //log your error
           return sql;
       }
    }
}

On the calling class I simply called the static method

SqlCalls.getSql(String path)

like so with a little Spring JdbcTemplate magic:


@Repository("groupDao")
public class GroupDaoImpl extends BaseDaoImpl implements GroupDao {

    public GroupDaoImpl(DataSource dataSource){
        super(dataSource)
    }

    @Override
    public void update(final List<Group> groups){
        String updateSQL = SqlCalls.getSql(SqlCalls.UPDATE_GROUP_SQL);
        int[] batchUpdate = getJdbcTemplate().batchUpdate(updateSQL, new BatchPreparedStatementSetter(){
              @Override
              public void setValues(PreparedStatement ps, int i) throws SQLException {

                   Group grp = groups.get(i);
                   ps.setString(1, grp.getName());
                   ps.setString(2, grp.getLocation());
              }

              @Override
              public int getBatchSize() {
                   return groups.size();
              }
        }
    }
}

I supplied the path as static variable to the sql file and it worked like a charm. I was able to externalize my sql, maintain nice formatting, and be able to quickly pull it up and test and debug it in my favorite SQL developer IDE.

I stored my sql files under the project directory structure like so:


src/main/resources/db

In the future, it might be cool to take this to a different level and allow the application to scan the directory of sql files on start up of application and store them in a HashMap for easier retrieval, then maybe use the key to get the sql…just some thoughts…