How to pass multiple lists in a query? Using JPQL

Asked

Viewed 66 times

-1

The problem is this: I have a controller that receives a list of Integers: [1245,12346 ...]

In my table I need to consult using this list but the columns are separate, IE, the first digits are in one column and the last in another.

I thought I’d do something like:

@Query("SELECT ep FROM EP ep WHERE ep.numero1 = :numero1 AND ep.numero2 = :numero2 ")
    public EP findByNumeros(@Param("numero1") List<Integer> numero1,
            @Param("numero2") List<Integer> numero2);

The problem: When passing a list with 3 values, it ends up interacting 6 times. Example: If I pass a list [0,1,2] and another [0,1,2] it makes the following query: 0,1 - 0,2 - 0,3 - 1,0 - 1,1 ... and so on.

I would like him to do only: 0,0, - 1,1 - 2,2.

Does anyone know how to solve this? I’ve tried the following: 

@Query("SELECT ep FROM EP ep WHERE (ep.numero1, ep.numero2) IN ((:numero1), (:numero2))")
    public List<EP> findByNumeros(@Param("numero1") List<Integer> numero1,
            @Param("numero2") List<Integer> numero2);

The following message appears: 

WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 920, SQLState: 42000
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00920: invalid relational operator
java sql spring jpa jpql

1 answer

0

If there are two different lists for different fields in the table, use the IN separately:

@Query("SELECT ep FROM EP ep WHERE ep.numero1 in (:numero1) and ep.numero2 in(:numero2)")

Browser other questions tagged java sql spring jpa jpql

You are not signed in. Login or sign up in order to post.