CODEWITHSUNDEEP
javasqljooq
Jerry Zhou
Jerry Zhou

Reputation: 229

How to modify plain sql text with jOOQ such as appending order-by/limit-offset clause or changing where condition value?

I build a sql template with named params using jOOQ 3.11.11.

select * from table1 where report_date = :bizdate

Then I stored the sql template text. For each query, I will replace the param bizdate with user input.
how to generate sql from template and param placeholder with JOOQ?
the realtime sql like this: 

select * from table1 where report_date = '20190801'

It works well. But there is another question. I want to append an order-by clause and a limit-offset clause to the sql. The order by field is dynamic and decided by user input as well as the limit offset param. The realtime sql like this:

select * from table1 where report_date = '20190801' order by id desc limit 0,10

or

select * from table1 where report_date = '20190801' order by name asc limit 5,20

How to generate the sql from the template and realtime input with jOOQ?

Upvotes: 1

Views: 702

Answers (1)

knutwannheden
knutwannheden

Reputation: 714

As in the answer to the other question I think it would be best if you didn't run dynamic SQL queries based on an SQL string, but rather built a new query using the jOOQ API each time. Then the LIMIT clause is also trivial to add:

ResultQuery<?> query(String bizDate, Field<?> sortField, SortOrder sortOrder, int limitOffset, int limitRows) {
    return context.selectFrom("table1")
                  .where(field("report_date").eq(bizDate))
                  .orderBy(sortField.sort(sortOrder)
                  .limit(limitOffset, limitRows));
}

If you do work with an SQL string you can again use the same pattern as before:

    ResultQuery resultQuery = context.resultQuery(
        sqlTemp,
        DSL.param("bizdate", "20190801"),
        DSL.field(sortField).sort(sortOrder),
        DSL.param("limit", limitOffset),
        DSL.param("rows", limitRows));

As another option you could maybe also use Java serialization to serialize your query instead of storing it as a plain String.

Upvotes: 1

Related Questions