Finding a way to create dynamic SQL from a given SQL query in Java

Question

As part of the workflow mechanism, I implement a common database step that will execute any given SQL query and return the results as XML. This workflow can be dynamically configured, so the query passed to the run level will be a fully qualified static SQL query, for example,

SELECT * FROM USER WHERE USERID = 10

. The disadvantage of this approach is that the database compiles the query every time it is run. Is there a way that I can create a dynamic SQL query from a query programmatically. Does Java or Hibernate JPA support this feature?

Answer 1

kuriouscoder, . JDBC. -, , , (.. ). , .

XML, jOOQ ( ). 1.6.2 (XML, HTML, JSON, CSV). API- jOOQ, :

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

( XSL ):

<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>

. http://www.jooq.org

Answer 2

MyBatis , . . .

MyBatis SQL; SqlMapClient , (, POJO ), :

• # parameters # (, PreparedStatements)
• $variables $, SQL
• SQL SQL

SQL- Java: , , , POJO; MyBatis HashMap , , queryWithRowHandler() SqlMapClient .

HashMap , XML.

Answer 3

, . : i) ( ); ii) .

vanilla JDBC. Spring JDBC . , JDBCTemplate

Answer 4

, , empiredb http://incubator.apache.org/empire-db/ - , , , , . python sqlalchemy, SQL- .

:

, API. SQL, select, POJO, beans.