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

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 a开发者_开发知识库pproach 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 ?

---

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

---

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.

---

In general you need to parametrize your input to the query template using bind variables. There are two form of bind variables, viz., i) unnamed bind variables (or positional bind variables); and ii) named bind variables.

This can be done via vanilla JDBC. I have found Spring JDBC particularly easy to maneuver. Specifically look at JDBCTemplate interface and it's implementations

---

if i understand what you are asking, then you should look at empiredb http://incubator.apache.org/empire-db/ - it is quite a small project but, at least when i was using it, was actively and helpfully supported. it is like python's sqlalchemy, in that it lets you construct sql queries using classes and functions within the language.

quoting from the link:

Type-safe, string-free and intuitive API. Build any SQL statement including the select clause and use it with any POJO not just full featured entity beans.