Select statement with where IN operator

[liran....@gmail.com]

Hi,

I would like to write a simple select statement using the IN operator. [select * from X where id1 = 21 and id2 in (1,2)  ]

I tried the following:

create.select().from(view).where(whereClause, whereBindings);

whereClause = id1 = ? and id2 in ?

whereBindings[0] = 21L

whereBinding[1] = "(1,2)"

Which resulted with:

select *

from X

where (id1 = 21 and id2 in '(1,2)') 

What is the proper way to write such a query? 

Thanks!

Liran

 

[Richard Fuller]

I would try using array of longs instead of a string.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Liran,

Thanks for your enquiry. Unfortunately, there is no JDBC driver out there that lets you bind a collection / array to a single bind variable that you put in an IN predicate. But fortunately, jOOQ's plain SQL templating API has a remedy for this. You can replace your code by this:

String whereClause = "id1 = {0} and id2 in ({1})";
QueryPart whereParts[] = {
DSL.val(21),
DSL.list(DSL.val(1), DSL.val(2))
};

create.select().from(view).where(whereClause, whereParts);

See also:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating

I hope this helps,
Lukas

--

[liran....@gmail.com]

Thanks Lukas!