In clause for a list of pair of conditions

Question

There is a table from where I need to fetch paginated records by applying and condition in a list of paired values, Below is the explanation

Lets say I have a class Billoflading and there are various fields in it The two important fields in the table are

1. tenant
2. billtype

I have a list of pairs which contain values as

[
   {`tenant1`, `billtype1`},
   {`tenant2`, `billtype2`},
   {`tenant3`, `billtype3`},
   ....
  ]

I need a JPA query where the fetch will be like findByTenantAndBilltypeOrTenantAndBillTypeOr.....

in simple sql query it will be like

Select * from `Billoflading` where 
`tenant` = 'tenant1' and billtype = 'billtype1'
OR `tenant` = 'tenant2' and billtype = 'billtype2'
OR `tenant` = 'tenant3' and billtype = 'billtype3'
OR ......... so on..

I tried writing a JPA query as follows

Page<Billoflading> findByTenantInAndBillTypeIn(List<String> tenants, List<String> billTypes, Page page);

but this had crossover records as well i.e it gave records for tenant1 and billtype2, benant2 and billtype 3 so on... which are not needed in the result set

can anyone please solve this and help me finding a simple solution like

Page<Billoflading> findByTenantAndBillTypeIn(Map<String, String> tenantsAndBilltyes, Page page);

I am also ready for the native queries in JPA all I need is there should be no crossovers as this is a very sensitive data

The other workaround I had was fetching the records and applying java 8 filters and that works but the no. of records in a page gets reduced

Answer 1

Section 4.6.9 of the JPA specification makes it clear that this is not supported by JPQL, at least not in the form of an in-clause:

4.6.9 In Expressions The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

in_expression ::=
    {state_valued_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter } 

in_item ::= literal | single_valued_input_parameter

The state_valued_path_expression must have a string, numeric, date, time, timestamp, or enum value.

The literal and/or input parameter values must be like the same abstract schema type of the state_valued_path_expression in type. (See Section 4.12).

The results of the subquery must be like the same abstract schema type of the state_valued_path_expression in type.

It just doesn't operate on tuples.

Your best bet is probably to create a Specification to construct the combination of AND and OR you require. See this blog article how to create Specifications