JPA select IN multiple fields

Question

Is it possible to achieve something like the below mySQL in jpql?

select * from PERSON p where (p.name, p.country)
IN (('Bryan', 'Netherlands'), ('Candice', 'New Zealand'), ...);

where "..." is a continuation of the list. I thought of creating a list of new entities just as a placeholder for the values, but I'm not sure how to make it work. I'm thinking of something along the lines of:

    @Query(
        value = "select p from Person p where (p.name, p.country) in :personList")
    List<Person> findPersonsIn(@Param("personList") List<Person> personList);

I know this is wildly incorrect, but I hope someone can shed light on how do I achieve the above MySQL statement in JPA or JDBC.

possible duplicate https://stackoverflow.com/a/37212946/7546121 — Amir Schnell, Apr 14 '20 at 10:10
ahh yes. It is indeed a duplicate. Thanks for the reference! — ixxbcrl, Apr 14 '20 at 10:20
Does this answer your question? [JPA Criteria : in clause with many columns](https://stackoverflow.com/questions/37190664/jpa-criteria-in-clause-with-many-columns) — Amir Schnell, Apr 14 '20 at 11:05
Does this answer your question? [Hibernate Criteria Query for multiple columns with IN clause and a subselect](https://stackoverflow.com/questions/19776826/hibernate-criteria-query-for-multiple-columns-with-in-clause-and-a-subselect) — Eklavya, Apr 14 '20 at 11:07

score 0 · Answer 1 · answered Apr 14 '20 at 10:16

0

Yes. Use the AND operator:

select * from PERSON p where ( p.name IN ('Bryan', 'Netherlands') ) AND ( p.country
IN ('Candice', 'New Zealand') ), ...);

answered Apr 14 '20 at 10:16

The Bitman

1,279
1
11
25

This is semantically different from my query. This would get all name, then filter it again with all the countries. What I'm looking for is sorta a tuple of filter criteria, i.e both the name 'Bryan' and country 'Netherlands' have to exist in a single row for it to be valid. For your case if another record exist with ('Bryan', 'New Zealand'), this would pop up too which is not what I want. – ixxbcrl Apr 14 '20 at 10:25

score 0 · Answer 2 · answered Apr 14 '20 at 10:22

Multiple columns in a IN clause is not provided for in JPA

@Query(
            value = "select p from Person p where (p.name, p.country) in :personList")
        List<Person> findPersonsIn(@Param("personList") List<Person> personList);

can you try the following code

 @Query(
        value = "select p from Person p where p.name in (:personName) or p.country in (:personCountry) ")
    List<Person> findPersonsIn(@Param("personName") List<String> personName,@Param("personCountry") List<String> personCountry);

or you can write queries more easily using "querydsl"

Thanks for your answer. But I need to have a strict filter criteria where a record has to contain the unique keys of (person+country). A combination is not acceptable, i.e based on my example above, if ('Bryan' , 'New Zealand') exists, it should not appear. — ixxbcrl, Apr 14 '20 at 10:28

JPA select IN multiple fields

2 Answers2