Is there a possiblity in JPA 2.0 to set a collection for in-clause in jpql-query? (I'm using EclipseLink)
The next example fails:
TypedQuery<Person> q = em.createQuery("select p from Person p where p.name in (?1)", Person.class);
List<String> names = Arrays.asList(new String[] { "Bill Gates", "Steve Jobs" });
// THIS FAILS
q.setParameter(1, names);
List<Person> persons = q.getResultList();
for (Person p: persons) {
System.out.println(p.getName());
}
Is there another way to do it?
Yes, It is possible to use subquery with IN operator in JPQL.
JPQL can retrieve information or data using SELECT clause, can do bulk updates using UPDATE clause and DELETE clause.
Query createQuery(String name) - The createQuery() method of EntityManager interface is used to create an instance of Query interface for executing JPQL statement.
This example shows how to use JPQL keyword MEMBER OF to determine whether a value is an element of a collection.
Here is what the JPA 2.0 specification says about IN expressions:
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_field_path_expression | type_discriminator} [NOT] IN { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter } in_item ::= literal | single_valued_input_parameter
...
So according to the specification, the correct syntax when passing a collection_valued_input_parameter is without parenthesis:
select p from Person p where p.name in ?1
And this works with EclipseLink.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With