IN-clause in HQL or Java Persistence Query Language

Question

I have the following parametrised JPA, or Hibernate, query:

SELECT entity FROM Entity entity WHERE name IN (?)

I want to pass the parameter as an ArrayList<String>, is this possible? Hibernate current tells me, that

java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String

Is this possible at all?

ANSWER: Collections as parameters only work with named parameters like ":name", not with JDBC style parameters like "?".

Would you mind adding your answer to this question? I know it's an old one, but self-answering your question is not only permitted, it's explicitly encouraged. — Makoto, May 07 '15 at 06:47

score 173 · Accepted Answer · edited Jan 29 '11 at 09:24

173

Are you using Hibernate's Query object, or JPA? For JPA, it should work fine:

String jpql = "from A where name in (:names)";
Query q = em.createQuery(jpql);
q.setParameter("names", l);

For Hibernate's, you'll need to use the setParameterList:

String hql = "from A where name in (:names)";
Query q = s.createQuery(hql);
q.setParameterList("names", l);

edited Jan 29 '11 at 09:24

Daniel

27,718
20
89
133

answered Jan 28 '11 at 12:28

jpkroehling

13,881
1
37
39

Okey, the problem was using ? to designate the parameters. With named parameters like :peter this works. – Daniel Jan 28 '11 at 12:57
so you don't need the quotes for each string in the list? – CCC Nov 26 '13 at 16:15
How to set String[] array as query parameter – priyadarshini May 31 '17 at 11:58
2

you could convert it to a list: `List list = Arrays.asList(yourArray);` – MarkyDD Jul 19 '17 at 15:01
1

IMP: Parenthesis are required for older versions of hibernate. Newer versions will not support () - at least in my case. Also, see this solution https://stackoverflow.com/a/21341663/6043669 – HopeKing Jun 11 '18 at 08:48
Boom, champion! – Adrian M. Jan 29 '20 at 22:07

score 24 · Answer 2 · answered Jan 28 '11 at 12:28

24

in HQL you can use query parameter and set Collection with setParameterList method.

    Query q = session.createQuery("SELECT entity FROM Entity entity WHERE name IN (:names)");
    q.setParameterList("names", names);

answered Jan 28 '11 at 12:28

michal.kreuzman

12,170
10
58
70

score 5 · Answer 3 · answered Aug 28 '16 at 18:08

5

Leaving out the parenthesis and simply calling 'setParameter' now works with at least Hibernate.

String jpql = "from A where name in :names";
Query q = em.createQuery(jpql);
q.setParameter("names", l);

answered Aug 28 '16 at 18:08

Steven Spungin

27,002
5
88
78

score 4 · Answer 4 · answered Jan 29 '16 at 15:29

Using pure JPA with Hibernate 5.0.2.Final as the actual provider the following seems to work with positional parameters as well:

Entity.java:

@Entity
@NamedQueries({
    @NamedQuery(name = "byAttributes", query = "select e from Entity e where e.attribute in (?1)") })
public class Entity {
    @Column(name = "attribute")
    private String attribute;
}

Dao.java:

public class Dao {
    public List<Entity> findByAttributes(Set<String> attributes) {
        Query query = em.createNamedQuery("byAttributes");
        query.setParameter(1, attributes);

        List<Entity> entities = query.getResultList();
        return entities;
    }
}

score 2 · Answer 5 · answered Aug 07 '19 at 10:25

2

query.setParameterList("name", new String[] { "Ron", "Som", "Roxi"}); fixed my issue

answered Aug 07 '19 at 10:25

Shahid Hussain Abbasi

2,508
16
10

IN-clause in HQL or Java Persistence Query Language

5 Answers5

Linked

Related