JPQL query to find all entities that match to list of Embeddable objects

Question

I have an entity with an embedded field that can be used as a business identifier of this entity.

@Entity
public class Person {

    @Id
    @Column(name = "ID")
    private Long id;

    @Embedded
    private Identifier identifier;
}

Embeddable
public class Identifier {
    @Column(name = "business_id")
    private String businessId;

    @Column(name = "client_id")
    private Integer clientId;

    @Column(name = "dep_id")
    private Integer depId;

When I'm using Spring JPARepository to search for all entities where identifier is in a list with the method: List<Person> findByIdentifierIn(List<Identifier> identifiers);

It works fine no matter how many identifiers I provide in list.

But I wanted to write a query to join some other table and tried to use such method with a query:

@Query("select p " +
            "from Person p " +
            "left join fetch p.status status " +
            "where p.identifier = :identifiers")
List<Person> findByIdentifierIn(@Param("identifiers") List<Identifier> identifiers);

And now if I pass a single element list as identifiers it still works fine, but if there are multiple elements I'm ending with an error for example with 2 elements I get:

the column index is out of range:3 , number of columns:2

I've checked the queries that are executed and JPARepository generates query where parameters are handled properly:

//select fields...
from users.person person0_
where (person0_.business_id, person0_.client_id, person0_.dep_id) in ((?, ?, ?), (?, ?, ?), (?, ?, ?))

but the query that i've written ends as this:

where (person0_.business_id, person0_.client_id, person0_.dep_id) = (?, ?);

How can I write a query that will be properly handled for multiple identifiers in the list?

I expected that my query would work in the same way that the one generated by JPARepository.

I've tried to remove any extra parts from the query like joins, to exclude their influence, but I'm not sure how can I modify it to work as expected.

Answer 1

To have the same result as JpaRepository generate. You should change a little the bit query.

Something like this:

@Query("select p " +
            "from Person p " +
            "left join fetch p.status status " +
            "where p.identifier IN (:identifiers)")
List<Person> findByIdentifierIn(@Param("identifiers") List<Identifier> identifiers);