Query for rows with combination of fields and in statement

user_1870913 0
Viewed 43

On a database level I have a table product that has two columns: gtin and market along with other columns.

I want to write a java code that would be transformed to the following query:

select * from product where (gtin, market) IN (('USA', 1), ('CA',2), ('PL', 3), ('CZ, 4))

On a java code level I have special entity GtinMarket that is dedicated to store these two gtin and market values:

GtinMarket {
    String gtin;
    String market;
}

On a code level I would expect something like:

using(configuration)
                .select()
                .from(table(PRODUCT_TABLE))
                .where((field(GTIN, Long.class),field(MARKET, Long.class)).in(gtinMarketList))
                .fetch()

After some investigation I was unable to find a way to write such a code,

I would really really appreciate any help,

Thanks,

jooq java
1 Answers

Use DSL.row(Field, Field) for type safe row value expression construction:

using(configuration)
    .select()
    .from(table(PRODUCT_TABLE))
    .where(row(field(GTIN, Long.class), field(MARKET, Long.class)).in(gtinMarketList))
    .fetch()

This is assuming that your gtinMarketList is a Collection<? extends Row2<Long, Long>> as expected by the relevant Row2.in() method. The in() method has several overloads, so you can pick whatever suits you.

More information here: https://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate-degree-n

user_5217990
Related