Adding IN clause List to a JPA Query

Question

I have built a NamedQuery that looks like this:

@NamedQuery(name = "EventLog.viewDatesInclude",
        query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND "
        + "el.timeMark <= :dateTo AND "
        + "el.name IN (:inclList)")

What I want to do is fill in the parameter :inclList with a list of items instead of one item. For example if I have a new List<String>() { "a", "b", "c" } how do I get that in the :inclList parameter? It only lets me codify one string. For example:

setParameter("inclList", "a") // works

setParameter("inclList", "a, b") // does not work

setParameter("inclList", "'a', 'b'") // does not work

setParameter("inclList", list) // throws an exception

I know I could just build a string and build the whole Query from that, but I wanted to avoid the overhead. Is there a better way of doing this?

Related question: if the List is very large, is there any good way of building query like that?

This is a duplicate of https://stackoverflow.com/questions/1557085/setting-a-parameter-as-a-list-for-an-in-expression#7967570 but this thread provides useful answers. — Mike Ryan, Jul 27 '18 at 20:26

score 218 · Accepted Answer · answered Dec 07 '10 at 16:29

218

When using IN with a collection-valued parameter you don't need (...):

@NamedQuery(name = "EventLog.viewDatesInclude", 
    query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " 
    + "el.timeMark <= :dateTo AND " 
    + "el.name IN :inclList")

answered Dec 07 '10 at 16:29

axtavt

239,438
41
511
482

9

Nope ... I my case its opposite. If I use :inclList then its not working. If I use IN (:inclList) then it works. – Gunjan Shah Nov 24 '12 at 10:27
2

Also note mentioning: the type of your param has to be an collection (not an array) of objects. The objects must match the type of the field. .toString() is no substitute for class String – dube Mar 21 '14 at 10:47
2

I think this is something that has changed with the versions of Hibernate, as far as I can remember I have gotten an error when not having the paranthesis around the variable when using IN. Strange if it's not backwards compatible.. – Tobb Aug 13 '14 at 14:10
1

This was indeed an hibernate bug (the need of parenthesis) that's been fixed in 3.6.1 – Mat Mar 11 '16 at 09:00
2

For related question: In case of very large list there could be limitations for the in implementation. E.g. oracle 11g. max. 1000 list elements as parameter are possible. A workaround is to chop the list in subLists and collect the results. JPA itself does not restrict the list size. – Mahttias Schrebiér Oct 25 '16 at 08:00
It did work. Thanks for that. Now, how do I make this an optional filter? `:inclList IS NULL OR el.name IN : inclList` throws `QuerySyntaxException: unexpected AST node` because it concatenates the whole list in the first part as well. – Guilherme Taffarel Bergamin Aug 13 '21 at 17:45

score 102 · Answer 2 · edited Aug 27 '14 at 08:03

102

The proper JPA query format would be:

el.name IN :inclList

If you're using an older version of Hibernate as your provider you have to write:

el.name IN (:inclList)

but that is a bug (HHH-5126) (EDIT: which has been resolved by now).

edited Aug 27 '14 at 08:03

wolφi

8,091
2
35
64

answered Jan 24 '14 at 20:14

Jose Ferrer

1,188
1
7
9

6

Thank you for distinguishing the older versions of Hibernate use () – Rob L Jul 24 '15 at 17:11

score 37 · Answer 3 · edited Dec 24 '11 at 01:55

37

public List<DealInfo> getDealInfos(List<String> dealIds) {
        String queryStr = "SELECT NEW com.admin.entity.DealInfo(deal.url, deal.url, deal.url, deal.url, deal.price, deal.value) " + "FROM Deal AS deal where deal.id in :inclList";
        TypedQuery<DealInfo> query = em.createQuery(queryStr, DealInfo.class);
        query.setParameter("inclList", dealIds);
        return query.getResultList();
    }

Works for me with JPA 2, Jboss 7.0.2

edited Dec 24 '11 at 01:55

Mark Hall

53,938
9
94
111

answered Dec 24 '11 at 00:02

user1114134

598
1
6
10

Thank you for showing a brief but complete example. – Queeg Dec 27 '22 at 13:22

Wesley Rocha · Answer 4 · 2023-03-21T20:38:13.010

21

You should convert to List as shown below:

    String[] valores = hierarquia.split(".");       
    List<String> lista =  Arrays.asList(valores);
    
    String jpqlQuery = "SELECT a " +
            "FROM AcessoScr a " +
            "WHERE a.scr IN :param ";
    
    Query query = getEntityManager().createQuery(jpqlQuery, AcessoScr.class);                   
    query.setParameter("param", lista);     
    List<AcessoScr> acessos = query.getResultList();

edited Mar 21 '23 at 20:38

answered Dec 15 '15 at 19:38

Wesley Rocha

311
2
4

Thx this answer helped me – cabaji99 Dec 01 '17 at 20:21
This is best answer. – Sami Haroon Nov 07 '20 at 19:10

Adding IN clause List to a JPA Query

4 Answers4

Linked

Related