Jpql in Clause: Java-Arrays (Or Lists, Sets...)

JPQL IN clause: Java-Arrays (or Lists, Sets...)?

I'm not sure for JPA 1.0 but you can pass a Collection in JPA 2.0:

String qlString = "select item from Item item where item.name IN :names"; 
Query q = em.createQuery(qlString, Item.class);

List<String> names = Arrays.asList("foo", "bar");

q.setParameter("names", names);
List<Item> actual = q.getResultList();

assertNotNull(actual);
assertEquals(2, actual.size());

Tested with EclipseLInk. With Hibernate 3.5.1, you'll need to surround the parameter with parenthesis:

String qlString = "select item from Item item where item.name IN (:names)";

But this is a bug, the JPQL query in the previous sample is valid JPQL. See HHH-5126.

How to set collection items for in-clause in jpql?

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.

JPA passing list to IN clause in named native query

A list is not a valid parameter for a native SQL query, as it cannot be bound in JDBC. You need to have a parameter for each argument in the list.

where u.user_id in (?id1, ?id2)

This is supported through JPQL, but not SQL, so you could use JPQL instead of a native query.

Some JPA providers may support this, so you may want to log a bug with your provider.

Enumeration List with IN operator in JPQL

I think that problem lies in representation of your Enums in DB. How are your enums mapped in the database Ordinal or String?

The best would be just putting your enums as parameter placeholder in your JPQL query:

SELECT h FROM WorkFlowHistory h WHERE h.referenceNo = :referenceNo 
AND h.workflowTask IN :workflowTasks

and than adding it as parameter to your query, something like this:

...
List<WorkflowTask> workflowTasks=Arrays.asList({WorkflowTask.CONFIRMATION, WorkflowTask.PAYMENT WorkflowTask.ISSUING });
...
Query q = em.createQuery(buffer.toString());
q.addParameter("workflowTasks", workflowTasks);

This way you don't have to think about how are your Enums represented in DB.

JPQL in clause with enum

Something like this would be valid JPQL, available for use in any compliant JPA 2 provider

SELECT c FROM Myobject c WHERE :category MEMBER OF c.categories

Setting a parameter as a list for an IN expression

Your JPQL is invalid, remove the brackets

List<String> logins = em.createQuery("SELECT a.accountManager.loginName " +
    "FROM Account a " +
    "WHERE a.id IN :ids")
    .setParameter("ids",Arrays.asList(new Long(1000100), new Long(1000110)))
    .getResultList();

---

Related Topics