CODEWITHSUNDEEP
javasqljooq
Adam Siemion
Adam Siemion

Reputation: 16029

Combine plain SQL and DSL in SQL query in jOOQ

I have a complex plain SQL query (with subselects, multiple joins, database specific functions) but I would like to use the jOOQ's DSL for generating e.g. the order by clause.

What I would like to achieve is:

DSL
    .using(datasource)
    .select("select column from table")
    .orderBy(DSL.fieldByName("column"))

which jOOQ could be transforming to:

select * (select column from table) q order by q.column;

Can this be done?

Upvotes: 1

Views: 1325

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You're close. The following is possible:

DSL.using(datasource, dialect)
   .select()
   .from("(select column from table) t")
   .orderBy(DSL.field("t.column"));

You have to wrap (and depending on the SQL dialect, also rename) your derived table explicitly yourself.

Note that I'm using DSL.field(), not DSL.fieldByName(), as the latter produces a case-sensitive column reference. Your plain SQL query would then also have to produce a case-sensitive column reference.

Upvotes: 1

Related Questions