JPA Query using " where in" and setParameter

[Wally Hartshorn]

I'm using the JPA Query Language for the first time and have run into a problem. I'm trying to do a "select where in (list of values)" query, but can't figure out the syntax to avoid hard-coding the list of values.

This works:

List<Thing> results = em.createQuery(
    "select t from Thing t where t.status in ('A', 'E')")
    .getResultList();

Replacing the hard-coded values with a parameter and an array gives me a class cast exception:

String[] statusCodes = { "A", "E" };
List<Thing> results = em.createQuery(
    "select t from Thing t where t.status in (:statusCodes)")
    .setParameter("statusCodes", statusCodes)
    .getResultList();

I'm using JDK 1.5, Hibernate 3, and JPA.

Can anyone give me some pointers on how to do this? (Or a better way to accomplish it?)

Thanks!
Wally

[Debu Panda]

JPA supports IN clause either with a static list or dynamic list with a sub query.

You can probably work around by dynamically building the query:

"select t from Thing t where t.status in ("+ statusCodes +")" )
.getResultList();

[Dejan Mratinkovic]

Any updates on this? It's almost two years since original post. Is it possible that this is only way to create dynamic "where in"?

Can it be achieved in more elegant way?

[Shailen Karambe]

You can check this link JPA doc

and query help

let me know whether your problem got resolved..

[Konstantin Yankovski]

I do following and it works fine:

public void doSmth(final String type, final Collection<Long> storeIds) {

        getJpaTemplate().execute(new JpaCallback() {
            public Object doInJpa(EntityManager entityManager) throws PersistenceException {
                return entityManager.createQuery(
                        "update Offer o SET o.hidden = true WHERE o.type= ?1 AND o.store.id IN (?2)")
                        .setParameter(1, type)
                        .setParameter(2, storeIds)
                        .executeUpdate();
            }
        });

    }

Hibernate generates following SQL for this JPA query:

update OFFER set HIDDEN=1 where TYPE=? and (STORE_ID in (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?))

'storeIds' parameter must be Collection, it doesn't work with an array!

[Mandar Kaduskar]

absolutely there is a solution two ways you can achieve this, as mentioned by Debu Panda you can create a comma separated String and dynamically add it to a query
like
String statesCode= "'A', 'E'";
select t from Thing t where t.status in (statesCode)")
.getResultList();

OR

List<String> inList = new ArrayList<String>();
inList.add("'A'");inList.add("'E'");
select t from Thing t where t.status in (:statesCode)")
.getResultList();
query.setParameter("statesCode", inList );

Hope this helps.

[Mandar Kaduskar]

Also dont forget that if your IN parameter has numeric data type like Long, smallInt, BigInt etc then you will need to create the list of the appropriate data type instead of Strings. Hope makes sense.

[Amit Ghorpade]

"app developer " please check your private messages for an important administrative matter. You can check them by clicking the My Private Messages link above.

[lilili bbb]

try to change setParameter(1,something);
to setLong(1,something);
(there are setString etc..)
that helps me....
good luck!

[Yasin Turk]

You should use setParameterList() method for "in" type parameters