CODEWITHSUNDEEP
javasqldsljooq
Tim Falony
Tim Falony

Reputation: 125

Creating a plain JOOQ SQL query using the Model API

I have been using JOOQ and it seems to be a really useful library to generate SQL queries. However, I'm stuck at generating them dynamically. For example, the following is what I want to do (Note that I just want use this to do SQL building without any code generation)

DSLContext context = DSL.using(SQLDialect.DEFAULT);

List<Field<?>> select = getSelect(String fields ...); // gets the selection fields
Select query = create.select(select)
      .from(name("table"));

if(where)
   query = query.addWhere(Conditions...);
if(groupBy)
   query = query.groupBy(List<Field<?>> ...);
if(orderBy)
   query = query.orderBy(List<Field<?>> ...);

Unfortunately, I can only find a way to do them all in single step like below:

query.select()
      .from(...)
      .where(...)
      .orderBy(...)
      .fetch();

The above one could be useful if the .where, .orderBy can accept null and then not add those clauses to the SQL if they are null, but seems like that's not possible.

Here are my questions:

Is there a way to create a SELECTION query dynamically and then VALIDATE it without the use of any code generation as my use case involves just constructing a plain String SQL? This answer: https://stackoverflow.com/a/14053108/1787599f seems no longer valid as I don't find a Factory method in 3.6.x, and what is the best approach for this kind of use case?

While I can set values and do a .getSQL() to get a SQL with ?, there is another step of getting the bind variables for me to pass this on to PreparedStatement, can it be done in a single step?

How to integrate this with JDBCTemplate?

Upvotes: 0

Views: 1458

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

First off, you put the term model API in the question title, but you're not using the model API, you're using the DSL API, which is more suitable for "static SQL queries". So, this would be a more suitable API usage:

List<Field<?>> select = getSelect(String fields ...);
SelectQuery<?> query = create.selectQuery();
query.addFrom(table(name("table")));

if(where)
   query.addWhere(Conditions...);
if(groupBy)
   query.addGroupBy(List<Field<?>> ...);
if(orderBy)
   query.addOrderBy(List<Field<?>> ...);

Regarding your concrete questions:

Using getSQL() with bind variables

Indeed, by default, jOOQ will always generate bind variables in its SQL statement, which is the better default choice in terms of performance in most databases. Here's some background information on that topic.

If you really want to have inlined bind variables, use Query.getSQL(ParamType.INLINED). However, you probably want to keep using bind variables, so you can extract them from the query as follows, using Query.getBindVariables():

String sql = query.getSQL();
List<Object> variables = query.getBindVariables();

More information in these sections of the manual:

Executing a query with JDBCTemplate

Here's an example how to bind execution to JPA's native query API using the above getSQL() and getBindVariables() methods (from the manual):

static List<Object[]> nativeQuery(EntityManager em, org.jooq.Query query) {

    // Extract the SQL statement from the jOOQ query:
    Query result = em.createNativeQuery(query.getSQL());

    // Extract the bind values from the jOOQ query:
    List<Object> values = query.getBindValues();
    for (int i = 0; i < values.size(); i++) {
        result.setParameter(i + 1, values.get(i));
    }

    return result.getResultList();
}

It works the same way with JDBCTemplate.

Upvotes: 2

Related Questions