How to write a condition with more than one field with jooq

Question

I would like to write a condition like the following with JOOQ

AND (id,name) IN (('id1','name1'),('id2','name2'),...)

I tried this syntax

Condition condition= DSL.concat(idField,nameField).in("");

which generates

concat(cast(`id` as char), cast(`name` as char)) in ('id1name1',"id2name2",....))

But this solution lead to a huge performance issue by skipping indexes

I've no clue of how to get the two fields together

Thanks for your help

Post what you have tried ? – Rahul Bobhate May 28 '13 at 09:36 — Rahul Bobhate, May 28 '13 at 09:36

score 2 · Accepted Answer · answered May 28 '13 at 11:14

Don't use string concatenation, when in fact you want to use row value expression predicates! Use jOOQ's row value expression support as documented here: http://www.jooq.org/doc/3.0/manual/sql-building/column-expressions/row-value-expressions

Or more specifically:

// import static org.jooq.impl.DSL.row;
Condition condition = row(ID, NAME).in(row("id1", "name1"), row("id2", "name2"));

You might need to suppress warnings due to the generic varargs parameter in Row2.in(Row2...)

If your target database doesn't support row value expressions, jOOQ will expand the above to this predicate

(ID = 'id1' AND NAME = 'name1') OR (ID = 'id2' AND NAME = 'name2')

1 Answers1