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 stackoverflow.com/questions/1557085/… but this thread provides useful answers. — Mike Ryan
– Mike Ryan, Commented Jul 27, 2018 at 20:26

axtavt · Accepted Answer · 2010-12-07 16:29:12Z

225

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 7, 2010 at 16:29

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gunjan Shah Over a year ago

Nope ... I my case its opposite. If I use :inclList then its not working. If I use IN (:inclList) then it works.

dube Over a year ago

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

Tobb Over a year ago

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..

Mat Over a year ago

This was indeed an hibernate bug (the need of parenthesis) that's been fixed in 3.6.1

Mahttias Schrebiér Over a year ago

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.

|

wolφi · Accepted Answer · 2014-08-27 08:03:59Z

107

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, 2014 at 8:03

wolφi

8,3814 gold badges42 silver badges71 bronze badges

answered Jan 24, 2014 at 20:14

Jose Ferrer

1,2381 gold badge8 silver badges9 bronze badges

1 Comment

Rob L Over a year ago

Thank you for distinguishing the older versions of Hibernate use ()

Mark Hall · Accepted Answer · 2011-12-24 01:55:46Z

38

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, 2011 at 1:55

Mark Hall

54.7k9 gold badges101 silver badges116 bronze badges

answered Dec 24, 2011 at 0:02

user1114134

6082 gold badges7 silver badges10 bronze badges

1 Comment

queeg Over a year ago

Thank you for showing a brief but complete example.

Wesley Rocha · Accepted Answer · 2023-03-21 20:38:13Z

24

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, 2023 at 20:38

answered Dec 15, 2015 at 19:38

Wesley Rocha

3412 silver badges4 bronze badges

2 Comments

cabaji99 Over a year ago

Thx this answer helped me

Sami Haroon Over a year ago

This is best answer.

Collectives™ on Stack Overflow

Adding IN clause List to a JPA Query

4 Answers 4

6 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related