CODEWITHSUNDEEP
javasqljooqrow-value-expression
mmounirou
mmounirou

Reputation: 759

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

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

Upvotes: 1

Views: 1645

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

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')

Upvotes: 2

Related Questions