Java 将 IN 子句列表添加到 JPA 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding IN clause List to a JPA Query
提问by AlanObject
I have built a NamedQuery that looks like this:
我已经构建了一个如下所示的 NamedQuery：
@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:
我想要做的是用项目列表而不是一个项目填写参数 :inclList 。例如，如果我有一个new List<String>() { "a", "b", "c" }如何在 :inclList 参数中获得它？它只让我编码一个字符串。例如：
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?
相关问题：如果列表非常大，有没有什么好的方法可以构建这样的查询？
采纳答案by axtavt
When using INwith a collection-valued parameter you don't need (...):
IN与集合值参数一起使用时，您不需要(...)：
@NamedQuery(name = "EventLog.viewDatesInclude",
query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND "
+ "el.timeMark <= :dateTo AND "
+ "el.name IN :inclList")
回答by user1114134
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
使用 JPA 2、Jboss 7.0.2 为我工作
回答by Jose Ferrer
The proper JPA query format would be:
正确的 JPA 查询格式是：
el.name IN :inclList
If you're using an older version of Hibernate as your provider you have to write:
如果您使用旧版本的 Hibernate 作为提供程序，则必须编写：
el.name IN (:inclList)
but that is a bug (HHH-5126) (EDIT: which has been resolved by now).
但这是一个错误（HHH-5126）（编辑：现在已解决）。
回答by Wesley Rocha
You must convert to Listas shown below:
您必须转换List为如下所示：
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();
