Java - Storing SQL statements in an external file [closed]

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?

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

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.

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.

Java - Storing SQL statements in an external file [closed]

Related

Recent Posts