Databases
StackOverflow2013
Postsdbo
POJava - Storing SQL statements in an external file
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJava - Storing SQL statements in an external file
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTapas Bose
    1. USAdrian
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJava - Storing SQL statements in an external file
      1. USKLE
      1. VTFavorite
    2. VO
      singulars
      1. POJava - Storing SQL statements in an external file
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POJava - Storing SQL statements in an external file
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you sure you want to centralise all your SQL into a file that the support team can edit? How badly could they break things?
      singulars
      1. POJava - Storing SQL statements in an external file
      1. USmartin clayton
    2. COOnce deployed, the application is unlikely to change unless major bugs are found. The support team will not have the expertise to tinker with a complex Java applications by they know how to tinker with SQL statements. Usually they will need to do DBA type changes rather than actual application changes
      singulars
      1. POJava - Storing SQL statements in an external file
      1. USAdrian
    3. COWEB4J example is not properly formatted, could somebody with edit privileges fix that?
      singulars
      1. POJava - Storing SQL statements in an external file
      1. USJuha Syrjälä
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload