CODEWITHSUNDEEP
javajpajpql
Muhammad Hewedy
Muhammad Hewedy

Reputation: 30056

multiple IN conditions in JPQL

How can I express the following SQL in JPQL:

select * from table where 
( ( table.col1 , table.col2) in 
   (col1val1, col2val1),
   (col1val2, col2val2),
   (col1val3, col2val3)
)

BTW: The above is valid Oracle SQL syntax

Upvotes: 4

Views: 799

Answers (2)

Gary
Gary

Reputation: 7257

Edit: Forget what follows, it's not correct. Left in to show thinking

I think you'd first have to split out the multi-dimensional statement into it's constituents:

select * from table 
where table.col1 in (col1val1, col1val2, col1val3)
and table.col2 in (col2val1, col2val2, col2val3)

which would translate in JPQL (assuming that "table" is mapped to an entity TableDto) like this:

select tableDto from TableDto tableDto 
where tableDto.col1 in(col1val1, col1val2, col1val3)
and tableDto.col2 in(col2val1, col2val2, col2val3)

The above is untested, but further information can be found in the JPQL reference documentation.

Upvotes: 0

Tom Anderson
Tom Anderson

Reputation: 47183

My JPQL is terrible, but how about something like:

select tableDto from TableDto tableDto 
where (tableDto.col1 = col1val1 and tableDto.col2 = col2val1)
or (tableDto.col1 = col1val2 and tableDto.col2 = col2val2)
or (tableDto.col1 = col1val3 and tableDto.col2 = col2val3)

It's not pretty.

Upvotes: 1

Related Questions