Adding a list of IN clauses to a JPA request

Question

I created 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 to specify a parameter: inclList with a list of elements instead of a single element. For example, if I have a new List<String>() { "a", "b", "c" } , how can I get this in the parameter: inclList? This allows me to codify a single 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 that I can just build a string and build the whole query out of it, but I wanted to avoid the overhead. Is there a better way to do this?

Related question: if the List is very large, is there a good way to build such a query?

Answer 1

When using IN with a parameter containing a collection, you do not need (...) :

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

Answer 2

The correct JPA request format will be:

 el.name IN :inclList 

If you are using an older version of Hibernate as your provider, you need to write:

 el.name IN (:inclList) 

but this is an error ( HHH-5126 ) (EDIT: this is already resolved).

Answer 3

 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

Answer 4

You must 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();