CODEWITHSUNDEEP
javasqljooq
Thilo
Thilo

Reputation: 262794

How to use row value expressions in jOOQ?

The jOOQ blog claims that it is one of ten more common mistakes that Java developers make when writing SQL to not use row value expressions where they are supported.

Their recommendation is to issue SQL like the following (instead of chaining a couple of single-column conditions with AND):

SELECT c.address
FROM   customer c,
WHERE (c.first_name, c.last_name) = (?, ?)

SELECT c.first_name, c.last_name, a.street
FROM   customer c
JOIN   address a
ON  (c.id, c.tenant_id) = (a.id, a.tenant_id)

But how do I actually code this in jOOQ?

Upvotes: 2

Views: 1807

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221195

Short of language support for tuples / records in Java, you will need to use one of the many overloaded DSL.row() methods.

Your examples (from the question) would then translate to:

// Assuming static imports:
import static org.jooq.impl.DSL.row;
import static your.generated.code.Tables.CUSTOMER;

Customer c = CUSTOMER.as("c");
Address a = ADDRESS.as("a");

DSL.using(configuration)
   .select(c.ADDRESS)
   .from(c)
   .where(row(c.FIRST_NAME, c.LAST_NAME).eq("Jon", "Doe"))
   //         ^^^^^^^^^^^^  ^^^^^^^^^^^ <-> ^^^^^  ^^^^^ Types must match
   .fetch();

DSL.using(configuration)
   .select(c.FIRST_NAME, c.LAST_NAME, a.STREET)
   .from(c)
   .join(a)
   .on(row(c.ID, c.TENANT_ID).eq(a.ID, a.TENANT_ID))
   //      ^^^^  ^^^^^^^^^^^ <-> ^^^^  ^^^^^^^^^^^ Types must match
   .fetch();

In the above example, Row2<T1, T2> DSL.row(Field<T1>, Field<T2>) would be applicable and ensure that left and right hand side row value expressions are of the same degree and type.

Note that according to the SQL:1999 standard, ROW is an optional keyword to be used with <row value constructor>, i.e. ROW(a, b) is the same as (a, b).

Upvotes: 1

Related Questions