CODEWITHSUNDEEP
javasqljooq
ashur
ashur

Reputation: 4317

Select multiple rows based on List of arguments in single Transaction

I want to fetch multiple values from the table based on list of arguments in single query (Single transaction). Please note I'm asking how to make this in JOOQ. To make it clear, I have something like this:

Code

List<Integer> id = new ArrayList<>();
id.add(1);
id.add(3);

Table in DB

ID | Title |
------------
 1 |  one  |
 2 |  two  |
 3 | three |
 4 | four  |

Now based on list of id's I want to fetch only first and third row in single transaction. Is it somehow possible ?

I've already tried something like this, but none of methods accept list or any collection as argument.

dsl.select(Tables.TABLE.TITLE)
   .from(Tables.TABLE)
   .where(Tables.TABLE.ID.eq()) // no list argument

Upvotes: 2

Views: 211

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220987

You don't want to use a comparison predicate but an in predicate using Field.in(Collection):

dsl.select(Tables.TABLE.TITLE)
   .from(Tables.TABLE)
   .where(Tables.TABLE.ID.in(id))

Or directly, using Field.in(T...):

dsl.select(Tables.TABLE.TITLE)
   .from(Tables.TABLE)
   .where(Tables.TABLE.ID.in(1, 3))

In other words, this isn't really a question about jOOQ (or using jOOQ with transactions), but it is a question about SQL in general.

Upvotes: 3

Related Questions