title: Java - Storing SQL statements in an external file
body: <p>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.</p>
  
  <p>Here are the requirements:</p>
  
  <ul>
  <li>The file must be readable from a Java
  application but also must be editable
  by the support team without the need
  of fancy editors</li>
  <li>Ideally, the file should be in plain
  text format but XML is OK too</li>
  <li>Allow DML as well as DDL statements
  to be stored / retrieved</li>
  <li>New statements can be added at a later stage (the application is flexible enough to pick them up and execute them)</li>
  <li>Statements can be grouped (and executed as a group by the application)</li>
  <li>Statements should allow parameters</li>
  </ul>
  
  <p>Notes:</p>
  
  <ul>
  <li>Once retrieved, the statements will
  executed using Spring’s JDBCTemplate</li>
  <li>Hibernate or Spring’s IOC container
  will not be used</li>
  </ul>
  
  <p>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”.</p>
  
  <ul>
  <li><p><a href="http://www.slamb.org/projects/axamol/sql-library/" rel="noreferrer">Axamol SQL Library</a></p>
  
  <p>Sample file content:</p>
  
  <pre><code>&lt;s:query name="get_emp"&gt;
    &lt;s:param name="name" type="string"/&gt;
    &lt;s:sql databases="oracle"&gt;
      select    *
      from      scott.emp
                join scott.dept on (emp.deptno = dept.deptno)
      where     emp.ename = &lt;s:bind param="name"/&gt;
    &lt;/s:sql&gt;
  &lt;/s:query&gt;
  </code></pre></li>
  <li><p><a href="http://ibatis.apache.org/" rel="noreferrer">iBATIS</a></p>
  
  <p>Sample file content:</p>
  
  <pre><code>&lt;sqlMap namespace="Contact""&gt;
      &lt;typeAlias alias="contact"
          type="com.sample.contact.Contact"/"&gt;
      &lt;select id="getContact"
          parameterClass="int" resultClass="contact""&gt;
              select CONTACTID as contactId,
                     FIRSTNAME as firstName,
                     LASTNAME as lastName from
                     ADMINISTRATOR.CONTACT where CONTACTID = #id#
      &lt;/select&gt;
  &lt;/sqlMap&gt;
  &lt;insert id="insertContact" parameterClass="contact"&gt;
  INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)
          VALUES(#contactId#,#firstName#,#lastName#);
   &lt;/insert&gt;
  &lt;update id="updateContact" parameterClass="contact"&gt;
  update ADMINISTRATOR.CONTACT SET
  FIRSTNAME=#firstName# ,
  LASTNAME=#lastName#
  where contactid=#contactId#
  &lt;/update&gt;
  &lt;delete id="deleteContact" parameterClass="int"&gt;
  DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#
  &lt;/delete&gt;
  </code></pre></li>
  <li><p><a href="http://www.web4j.com/" rel="noreferrer">WEB4J</a></p>
  
  <pre><code>-- 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}
  }
  </code></pre></li>
  </ul>
  
  <p>Can anyone recommend a solution that is tried and tested?</p>