Creating a plain JOOQ SQL query using the Model API

Question

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?

"Is there a way to create a SELECTION query dynamically" - I'm sorry, I'm not sure what you mean by this. What are you struggling with? / "I don't find a Factory method in 3.6.x" - You're right, I'll edit my answer there. — Lukas Eder
– Lukas Eder, Commented Jul 25, 2017 at 7:23

Lukas Eder · Accepted Answer · 2017-07-25 07:22:56Z

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.

Collectives™ on Stack Overflow

Creating a plain JOOQ SQL query using the Model API

1 Answer 1

Using `getSQL()` with bind variables

Executing a query with JDBCTemplate

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using getSQL() with bind variables

Executing a query with JDBCTemplate

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Using `getSQL()` with bind variables