Looking for a way to create dynamic SQL from a given SQL Query in Java

Question

As part of a workflow engine i am implementing a generic DB step that would execute any given SQL query and return the results as XML. This workflow can be dynamically configured , hence the Query passed to the execution layer will be a fully qualified static SQL query for e.g.,

SELECT * FROM USER WHERE USERID = 10

. Downside of this approach is that the database compiles the query every time its run. Is there a way i can create a dynamic SQL query from the query programmatically. Does Java or Hibernate like JPAs support such feature ?

Answer 1

I agree with user kuriouscoder, that you should use bind variables. This works with plain JDBC. Most RDBMS have a cursor cache, which keeps both parsed versions of the query as well as execution plans in memory, if the queries are identical (i.e. no inlined variables). So the parser overhead will be minimal.

As far as the XML output is concerned, I can recommend jOOQ (which I am the developer of). In the upcoming version 1.6.2, I will add exporting functionality for various formats (XML, HTML, JSON, CSV). Your query would be constructed with jOOQ's fluent API and it would look like this:

String xml = create.selectFrom(USER)
                   .where(USERID.equal(10))
                   .fetch()
                   .formatXML();

The output will look like this (and can be XSL transformed to any other format):

<result>
  <fields>
    <field name="USERID"/>
    <field name="FIRSTNAME"/>
    <field name="LASTNAME"/>
    ...
  </fields>
  <records>
    <record>
      <value field="USERID">1</value>
      <value field="FIRSTNAME">Lukas</value>
      <value field="LASTNAME">Eder</value>
      ...
    </record>
    ...
  </records>
</result>

For more details, see http://www.jooq.org

Answer 2

I used MyBatis in a project to obtain a very similar behaviour. Take also a look at the User Guide for complete information.

In MyBatis you can define queries with dynamic SQL; you can pass to the SqlMapClient an object containing properties (e.g. a POJO or a Map) and use them in the query text as:

• #parameters# (like the ? in PreparedStatements)
• $variables$ that became directly portions of SQL string
• Dynamic SQL feature to include portions of SQL based on properties values

Then your SQL mapper will execute the query and return a Java object: if your record returning from query is known, you can map columns to a POJO; otherwise in MyBatis you can have a HashMap as a resulting class and, eventually, treat every record with a handler through the queryWithRowHandler() method of the SqlMapClient to post process returned data.

Once you have at least a HashMap representation of your query you can then easily transform it in XML.