Java - Storing SQL statements in an external file

Question

I am looking for a Java library/framework/technique of storing SQL statements in an external file. The support team (including DBAs) should be able to alter (slightly) the statement to keep them in sync in case database schema changes or for tuning purposes.

Here are the requirements:

• The file must be readable from a Java application but also must be editable by the support team without the need of fancy editors
• Ideally, the file should be in plain text format but XML is OK too
• Allow DML as well as DDL statements to be stored / retrieved
• New statements can be added at a later stage (the application is flexible enough to pick them up and execute them)
• Statements can be grouped (and executed as a group by the application)
• Statements should allow parameters

Notes:

• Once retrieved, the statements will executed using Spring’s JDBCTemplate
• Hibernate or Spring’s IOC container will not be used

So far, I managed to find the following Java libraries, which use external files for storing SQL statements. However, I am mainly interested in the storage rather than a library that hides all JDBC “complexities”.

• Axamol SQL Library

  Sample file content:

  <s:query name="get_emp">
    <s:param name="name" type="string"/>
    <s:sql databases="oracle">
      select    *
      from      scott.emp
                join scott.dept on (emp.deptno = dept.deptno)
      where     emp.ename = <s:bind param="name"/>
    </s:sql>
  </s:query>
  

• iBATIS

  Sample file content:

  <sqlMap namespace="Contact"">
      <typeAlias alias="contact"
          type="com.sample.contact.Contact"/">
      <select id="getContact"
          parameterClass="int" resultClass="contact"">
              select CONTACTID as contactId,
                     FIRSTNAME as firstName,
                     LASTNAME as lastName from
                     ADMINISTRATOR.CONTACT where CONTACTID = #id#
      </select>
  </sqlMap>
  <insert id="insertContact" parameterClass="contact">
  INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)
          VALUES(#contactId#,#firstName#,#lastName#);
   </insert>
  <update id="updateContact" parameterClass="contact">
  update ADMINISTRATOR.CONTACT SET
  FIRSTNAME=#firstName# ,
  LASTNAME=#lastName#
  where contactid=#contactId#
  </update>
  <delete id="deleteContact" parameterClass="int">
  DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#
  </delete>
  

• WEB4J

  -- This is a comment 
   ADD_MESSAGE   {
   INSERT INTO MyMessage -- another comment
    (LoginName, Body, CreationDate)
    -- another comment
    VALUES (?,?,?)
   }
  
  -- Example of referring to a constant defined above.
  FETCH_RECENT_MESSAGES {
   SELECT 
   LoginName, Body, CreationDate 
   FROM MyMessage 
   ORDER BY Id DESC LIMIT ${num_messages_to_view}
  }
  

Can anyone recommend a solution that is tried and tested?

Answer 1

Just create a simple Java Properties file with key-value pairs like this one:

users.select.all = select * from user

Declare a private field of type Properties in your DAO class and inject it using Spring configuration which will read the values from the file.

UPDATE: if you want to support SQL statements in multiple lines use this notation:

users.select.all.0 = select *
users.select.all.1 = from   user

Answer 2

Pasting here my answer to Clean way to externalize long (+20 lines sql) when using spring jdbc?:

I faced the same issue some time ago, and came up with YAML. It supports multi-line string property values, so you can write something like this in your query files:

selectSomething: >
  SELECT column1, column2 FROM SOMETHING

insertSomething: >
  INSERT INTO SOMETHING(column1, column2)
  VALUES(1, '1')

Here, selectSomething and insertSomething are query names. So it's really convenient and contains very few special characters. Queries are separated by blank lines, and each query text must be indented. Note that queries can absolutely contain the indentation of their own, so that the following is perfectly valid:

anotherSelect: <
  SELECT column1 FROM SOMETHING
  WHERE column2 IN (
    SELECT * FROM SOMETHING_ELSE
  )

You can then read the contents of the file into a hash-map with the help of SnakeYAML library, using the code below:

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.FileUtils;
import java.io.FileReader;

import org.yaml.snakeyaml.Yaml;
import java.io.File;
import java.io.FileNotFoundException;

public class SQLReader {
  private Map<String, Map> sqlQueries = new HashMap<String, Map>();

  private SQLReader() {
    try {
      final File sqlYmlDir = new File("dir_with_yml_files");
      Collection<File> ymlFiles = FileUtils.listFiles(sqlYmlDir, new String[]{"yml"}, false);
      for (File f : ymlFiles) {
        final String fileName = FilenameUtils.getBaseName(f.getName());
        Map ymlQueries = (Map)new Yaml().load(new FileReader(f));
        sqlQueries.put(fileName, ymlQueries);
      }
    }
    catch (FileNotFoundException ex) {
      System.out.println("File not found!!!");
    }
  }
}

In the example above a map of maps is created, mapping each YAML file to a map containing query names/strings.

Answer 3

If you must do this, you should look at the MyBatis project. I haven't used it, but have heard it recommended a number of times.

Separating SQL and Java isn't my favorite approach, since SQL is actually code, and is tightly coupled to the Java code that calls it. Maintaining and debugging the separated code can be challenging.

Absolutely don't used stored procs for this. They should only be used to improve performance by reducing traffic between the DB and the application.

Answer 4

A simple solution we have implemented when faced with this was to externalize the SQL/DML into a file (mySql.properties), then use MessageFormat.format(String[] args) to inject dynamic properties into the SQL.

For example: mySql.properties:

select    *
    from      scott.emp
              join scott.dept on (emp.deptno = dept.deptno)
    where     emp.ename = {0}

Utility methods:

public static String format(String template, Object[] args) {
    String cleanedTemplate = replaceSingleQuotes(template);
    MessageFormat mf = new MessageFormat(cleanedTemplate);
    String output = mf.format(args);
    return output;
}
private static String replaceSingleQuotes(String template) {
    String cleaned = template.replace("'", "''");
    return cleaned;
}

Then use it like so:

String sqlString = youStringReaderImpl("/path/to/file");
String parsedSql = format(sqlString, new String[] {"bob"});

Answer 5

The ElSql library provides this functionality.

ElSql consists of a small jar file (six public classes) that allows an external SQL file (elsql) to be loaded. The file uses a simple format to optionally provide slightly more behaviour than simply loading a file:

-- an example comment
@NAME(SelectBlogs)
  @PAGING(:paging_offset,:paging_fetch)
    SELECT @INCLUDE(CommonFields)
    FROM blogs
    WHERE id = :id
      @AND(:date)
        date > :date
      @AND(:active)
        active = :active
    ORDER BY title, author
@NAME(CommonFields)
  title, author, content

// Java code:
bundle.getSql("SelectBlogs", searchArgs);

The file is broken up into @NAME blocks which can be referred to from code. Each block is defined by significant whitespace indentation. @PAGING will insert the necessary code for paging such as FETCH/OFFSET. @AND will only be output if the specified variable exists (helping build dynamic searches). The DSL also handles LIKE vs = for wildcards in searches. The goal of the optional DSL tags is to provide the common basics that often hit when trying to build dynamic SQL in a database-neutral way.

More info on the blog or user guide.

Answer 6

You can also use the QueryLoader class in Apache Commons DbUtils, which will read the sql from a properties file. However, you will have to use DbUtils which sort of serves the same purpose as the JDBCTemplate.

Answer 7

You can use Spring and have your sql statements stored in your beans file that are injected when you get the class from your bean factory. That class can also to use an instance of SimpleJDBCTemplate that can be configured via the bean file to help simplify your code.

Answer 8

It's simple and reliable to do using classes from Spring. Take your SQL files and save them in some location on your classpath. This can be in a JAR file that only contains SQL if you want. Then use Spring's ClassPathResource to load the file into a stream and use Apache IOUtils to convert it into a String. You can then execute the SQL using SimpleJdbcTemplate, or DB code of your choice.

I suggest you create a utility class that takes a simple Java class with public String fields that correspond to the SQL file names following a convention of your choosing. Then use reflection in conjunction with the ClassPathResource class to go find the SQL files conforming to your naming convention and assign them to the String fields. After that just refer to the class fields when you need the SQL. It's simple, works great, and achieves the goal you want. It also uses well worn classes and techniques. Nothing fancy. I did it couple years back. Works great. Too lazy to go get the code. You'll have no time figuring it out yourself.

Answer 9

You can use the localization facilities to do this. You then use the name of the database as the locale to get the "oraclish" version of "insert-foo-in-bar" instead of the English or French version.

The translations are usually stored in property files, and there are good tools for localizing applications by allowing editing these property files.

Answer 10

dynamic-query is a good open source framework for those want something between JDBC and ORM.

1 plain SQL. - It saves plain sql to external files. no redundant tags, supports comments.

/* It also supports comment.
This code is in an external file 'sample.sql', Not inisde java code.*/
listUsers : select * from user_table
where user_id= $$;  /* $$ will automatically catch a parameter userId */

2 expandable SQL. -It supports parameters, including other files and sub query.

listUsers:
select
    id, amount, created
    @checkEmail{ ,email } 
from user_table
where amount > $amt and balance < $amt
    @checkDate { and created = $$ }
    @checkEmail{ and email in (
        select email from vip_list ) } ;        
/* Above query can be four queries like below.
1. listUsers
2. listUsers.checkDate 
3. listUsers.checkEmail
4. listUsers.checkDate.checkEmail 
*/



-- It can include other files like below
& ../hr/additional hr.sql ; 
& ../fi/additional fi.sql ;

Java example code using above. setting values to db.

QueryUtil qu = qm.createQueryUtil("selectAll");
try {
    qu.setConnection(conn);

    // with native jdbc
    qu.setString("alpha");
    qu.setDouble(10.1);
    qu.executeQuery();

    // or with bean
    qu.executeQuery(new User("alpha", 10.1));

    // or with map
    Map<String, Object> map=new HashMap<String, Object>();
    map.put("userName", "alpha");
    map.put("amt", 10.1);
    qu.executeQuery(map);

    // or with array
    qu.executeQueryParameters("alpha", 10.1);

Java example code using above. getting values from db.

    while (qu.next()) // == qu.rs.next()
    {
        // native jdbc
        String usreName = qu.getString("user_name"); 
        double amt = qu.getDouble("amt");

        // or bean
        User user = new User();
        qu.updateBean(user);

        // or array
        Object[] values = qu.populateArray();
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    qu.closeJust();
}

Answer 11

I would strongly encourage you to use Stored Procedures. This kind of thing is exactly what they're for.

Answer 12

You can use velocity to have "scriptable" sql templates that you can use to work with the files in a flexible way. You have primitive statements like conditionals and loops to build your sql commands.

But I strongly suggest to use prepared statements and/or stored procedures. Building your SQL the way you're planning will make you vulnerable to SQL injection, the DB server will not be able to cache the sql queries (which will lead to bad performance).

BTW: You can store the definition of the prepared statements in files too. This is not the best solution but pretty close to it and you get the benefit of SQL-injection protection and performance.

When your SQL schema is not build to work with prepared statements or stored procedures you might want to rethink your schema. Maybe it needs to be refactored.