Java 将空列表作为参数传递给 JPA 查询会引发错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2488930/
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
Passing empty list as parameter to JPA query throws error
提问by Tuukka Mustonen
If I pass an empty list into a JPA query, I get an error. For example:
如果我将一个空列表传递给 JPA 查询，则会出现错误。例如：
List<Municipality> municipalities = myDao.findAll(); // returns empty list
em.createQuery("SELECT p FROM Profile p JOIN p.municipality m WHERE m IN (:municipalities)")
.setParameter("municipalities", municipalities)
.getResultList();
Because the list is empty, Hibernate generates this in SQL as "IN ()", which gives me error with Hypersonic database.
因为列表是空的，Hibernate 在 SQL 中将其生成为“IN()”，这给我带来了 Hypersonic 数据库的错误。
There is a ticket for this in Hibernate issue trackingbut there are not many comments/activity there. I don't know about support in other ORM products or in JPA spec either.
在Hibernate 问题跟踪中有一张票，但那里没有很多评论/活动。我也不知道其他 ORM 产品或 JPA 规范中的支持。
I don't like the idea of having to manually check for null objects and empty lists every time. Is there some commonly known approach/extension to this? How do you handle these situations?
我不喜欢每次都必须手动检查空对象和空列表的想法。是否有一些众所周知的方法/扩展？你如何处理这些情况？
采纳答案by Pascal Thivent
According to the section 4.6.8 In Expressionsfrom the JPA 1.0 specification:
根据JPA 1.0 规范中的第4.6.8节在表达式中：
There must be at least one element in the comma separated list that defines the set of values for the
INexpression.
逗号分隔列表中必须至少有一个元素来定义
IN表达式的一组值。
In other words, regardless of Hibernate's ability to parse the query and to pass an IN(), regardless of the support of this syntax by particular databases (PosgreSQL doesn't according to the Jira issue), you should use a dynamic query here if you want your code to be portable (and I usually prefer to use the Criteria API for dynamic queries).
换句话说，无论Hibernate的分析查询和传递的能力IN()，不管是支持这一语法通过特定的数据库（PosgreSQL不根据JIRA问题），你应该在这里，如果你想使用动态查询您的代码是可移植的（我通常更喜欢使用 Criteria API 进行动态查询）。
回答by Tuukka Mustonen
After having no actual solution as replies, I created a proxy class to handle these situations. The idea is to retain native syntax where possible.
在没有实际解决方案作为答复后，我创建了一个代理类来处理这些情况。这个想法是在可能的情况下保留本机语法。
WARNING:This is work-in-progress and very hazardous approach. The code below is by no mean meant as complete solution and quite possibly contains zillions of bugs and scary cases.
警告：这是正在进行的工作并且非常危险的方法。下面的代码绝不是完整的解决方案，很可能包含无数错误和可怕的案例。
That being said, the BlankAwareQuery class wraps the javax.persistence Query and is initialized with EntityManager and the core query string (which cannot contain empty lists or lists of enumerations).
话虽如此，BlankAwareQuery 类包装了 javax.persistence Query 并使用 EntityManager 和核心查询字符串（不能包含空列表或枚举列表）进行初始化。
BlankAwareQuery query = new BlankAwareQuery(em, "SELECT p FROM Profile p");
After creation of class, dynamic parts are inserted with
创建类后，动态部分插入
query.from("p.address a");
query.where("a IN (:addresses)");
Parameters are inserted as always:
一如既往地插入参数：
query.setParameter("addresses", addresses);
The point here is that the class removes these (their from-part as well) from query if they are empty lists or manipulates them if they are lists of enumerations.
这里的要点是，如果它们是空列表，则该类从查询中删除它们（它们的源部分也是如此），或者如果它们是枚举列表，则操作它们。
Then call:
然后调用：
query.getResultList();
So, for example:
因此，例如：
List<Profile> profiles = new BlankAwareQuery(em, "SELECT p FROM Profile p")
.from("p.address a JOIN a.municipality m").where("m IN (:municipalities)")
.where("p.gender IN (:genders)")
.where("p.yearOfBirth > :minYear")
.where("p.yearOfBirth < :maxYear")
.from("p.platforms f").where("f IN (:platforms)")
.setParameter("municipalities", municipalities)
.setParameter("genders", genders)
.setParameter("minYear", minYear)
.setParameter("maxYear", maxYear)
.setParameter("platforms", platforms)
.getResultList();
The actual code (code uses Lombok for @Data and @NonNull annotations and Apache commons lang for StringUtils):
实际代码（代码使用 Lombok 作为 @Data 和 @NonNull 注释，使用 Apache commons lang 作为 StringUtils）：
public class BlankAwareQuery {
private @Data class Parameter {
private @NonNull String fieldName;
private @NonNull Object value;
}
private @Data class ClausePair {
private @NonNull String from;
private @NonNull String where;
}
private EntityManager em;
private List<String> select = Lists.newArrayList();
private List<ClausePair> whereFrom = Lists.newArrayList();
private String from;
private List<Parameter> parameters = Lists.newArrayList();
Query query;
public BlankAwareQuery(EntityManager em, String query) {
this.em = em;
/** Select **/
int selectStart = StringUtils.indexOf(query, "SELECT ") + 7;
int selectEnd = StringUtils.indexOf(query, " FROM ");
select(StringUtils.substring(query, selectStart, selectEnd));
/** From **/
int fromStart = selectEnd + 6;
int fromEnd = StringUtils.indexOf(query, " WHERE ");
if (fromEnd == -1) fromEnd = query.length();
from(StringUtils.substring(query, fromStart, fromEnd));
/** Where **/
String where = "";
if (StringUtils.contains(query, " WHERE ")) {
where = StringUtils.substring(query, fromEnd + 7);
}
where(where);
}
private BlankAwareQuery select(String s) {
select.add(s);
return this;
}
public BlankAwareQuery from(String s) {
from = s;
return this;
}
public BlankAwareQuery where(String s) {
ClausePair p = new ClausePair(from, s);
whereFrom.add(p);
from = "";
return this;
}
public BlankAwareQuery setParameter(String fieldName, Object value) {
/** Non-empty collection -> include **/
if (value != null && value instanceof List<?> && !((List<?>) value).isEmpty()) {
/** List of enums -> parse open (JPA doesn't support defining list of enums as in (:blaa) **/
if (((List<?>) value).get(0) instanceof Enum<?>) {
List<String> fields = Lists.newArrayList();
/** Split parameters into individual entries **/
int i = 0;
for (Enum<?> g : (List<Enum<?>>) value) {
String fieldSingular = StringUtils.substring(fieldName, 0, fieldName.length() - 1) + i;
fields.add(":" + fieldSingular);
parameters.add(new Parameter(fieldSingular, g));
i++;
}
/** Split :enums into (:enum1, :enum2, :enum3) strings **/
for (ClausePair p : whereFrom) {
if (p.getWhere().contains(":" + fieldName)) {
int start = StringUtils.indexOf(p.getWhere(), ":" + fieldName);
int end = StringUtils.indexOfAny(StringUtils.substring(p.getWhere(), start + 1), new char[] {')', ' '});
String newWhere = StringUtils.substring(p.getWhere(), 0, start) + StringUtils.join(fields, ", ") + StringUtils.substring(p.getWhere(), end + start + 1);
p.setWhere(newWhere);
}
}
}
/** Normal type which doesn't require customization, just add it **/
else {
parameters.add(new Parameter(fieldName, value));
}
}
/** Not to be included -> remove from and where pair from query **/
else {
for (Iterator<ClausePair> it = whereFrom.iterator(); it.hasNext();) {
ClausePair p = it.next();
if (StringUtils.contains(p.getWhere(), fieldName)) {
it.remove();
}
}
}
return this;
}
private String buildQueryString() {
List<String> from = Lists.newArrayList();
List<String> where = Lists.newArrayList();
for (ClausePair p : whereFrom) {
if (!p.getFrom().equals("")) from.add(p.getFrom());
if (!p.getWhere().equals("")) where.add(p.getWhere());
}
String selectQuery = StringUtils.join(select, ", ");
String fromQuery = StringUtils.join(from, " JOIN ");
String whereQuery = StringUtils.join(where, " AND ");
String query = "SELECT " + selectQuery + " FROM " + fromQuery + (whereQuery == "" ? "" : " WHERE " + whereQuery);
return query;
}
public Query getQuery() {
query = em.createQuery(buildQueryString());
setParameters();
return query;
}
private void setParameters() {
for (Parameter par : parameters) {
query.setParameter(par.getFieldName(), par.getValue());
}
}
public List getResultList() {
return getQuery().getResultList();
}
public Object getSingleResult() {
return getQuery().getSingleResult();
}
}
回答by Samanta
Solution:
解决方案：
if (municipalities==null || municipalities.isEmpty())
.setParameter("municipalities", "''")
else
.setParameter("municipalities", municipalities)
回答by PeterS
If you are using spring/hibernate annotations then one of the easiest ways of working around this is having two parameters. First lets define our JPA annotation:
如果您使用的是 spring/hibernate 注释，那么解决此问题的最简单方法之一是使用两个参数。首先让我们定义我们的 JPA 注释：
+ "AND (p.myVarin :state OR :nullCheckMyVar is null) "
Then pass those parameters in as normal:
然后像往常一样传递这些参数：
@Param("myVarin") List<Integers> myVarin,
@Param("nullCheckMyVar") Integer nullCheckMyVar,
Finally in what ever service call you can do the following:
最后，在任何服务调用中，您都可以执行以下操作：
Integer nullCheckInteger = null;
if (myVarIn != null && !myVarIn.isEmpty()) {
// we can use any integer
nullCheckInteger = 1;
}
repo.callService(myVarIn, nullCheckInteger);
And then pass those two parms into the call.
然后将这两个参数传递给调用。
What does this do? If myVarIn is not null and filled then we can specify "all" as 1 != null. Otherwise we pass in our list and null is null and therefore ignored. This either selects "all" when empty or something when not empty.
这有什么作用？如果 myVarIn 不为 null 并且已填充，那么我们可以将“all”指定为 1 != null。否则，我们传入我们的列表，而 null 为 null，因此被忽略。这要么在空时选择“全部”，要么在非空时选择某些内容。
回答by user10747457
Since you're querying DB sequence IDs that typically start at 1, you can add 0 to the list
由于您要查询的数据库序列 ID 通常从 1 开始，因此您可以将 0 添加到列表中
if (excludeIds.isEmpty()) {
excludeIds.add(new Long("0"));
}
List<SomeEntity> retval = someEntityRepo.findByIdNotIn(excludeIds);
Maybe -1 works too. Small work around for the use of jpa repos.
也许 -1 也有效。使用 jpa repos 的小工作。
回答by r_divyas
Assuming the SQL query to be like
假设 SQL 查询类似于
(COALESCE(:placeHolderName,NULL) IS NULL OR Column_Name in (:placeHolderName))
Now, If the List is of type String then you can pass as
现在，如果 List 是 String 类型，那么您可以传递为
query.setParameterList("placeHolderName",
!CollectionUtils.isEmpty(list)? list : new ArrayList<String>(Arrays.asList("")).
And If the List is containing the Integer values then the syntax is like below:
如果列表包含整数值，则语法如下所示：
If(!CollectionUtils.isEmpty(list)){
query.setParameterList("placeHolderName",list)
}else{
query.setParameter("placeHolderName",null, Hibernate.INTEGER)
}
回答by Reneta
I struggled with this issue, too. I found out that the Hibernate community has RESOLVED the issue in Hibernate version 5.4.10, here is the ticket: https://hibernate.atlassian.net/browse/HHH-8091
我也在这个问题上挣扎。我发现 Hibernate 社区已经解决了Hibernate 版本 5.4.10 中的问题，这是票证：https: //hibernate.atlassian.net/browse/HHH-8091
You can check your Hibernate version System.out.println(org.hibernate.Version.getVersionString());
您可以检查您的 Hibernate 版本 System.out.println(org.hibernate.Version.getVersionString());
And you can UPDATE the version of Hibernate to the latest, here is a useful link: https://bintray.com/hibernate/artifacts/hibernate-orm#
您可以将 Hibernate 的版本更新到最新版本，这是一个有用的链接：https: //bintray.com/hibernate/artifacts/hibernate-orm#
