java JPA where 子句 any
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2437053/
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
JPA where clause any
提问by Ke.
In JPA, the query is:
在 JPA 中，查询是：
Query q = entityManager.createQuery("select o from Product o WHERE o.category = :value");
q.setParameter("category", category);
How can I set category to any category in JPA? So if the null category passed, I simple ignore the category parameter, select all products.
如何将类别设置为 JPA 中的任何类别？所以如果空分类通过，我简单的忽略分类参数，选择所有产品。
回答by Pascal Thivent
How can I set category to any category in JPA? So if the null category passed, I simple ignore the category parameter, select all products.
如何将类别设置为 JPA 中的任何类别？所以如果空分类通过，我简单的忽略分类参数，选择所有产品。
You'll have to build the query dynamically here. With HQL (this is a simplified example):
您必须在此处动态构建查询。使用 HQL（这是一个简化示例）：
Map<String, Object> params = new HashMap<String, Object>();
StringBuffer hql = new StringBuffer("from Product p");
boolean first = true;
if (category != null) {
hql.append(first ? " where " : " and ");
hql.append("p.category = :category");
params.put("category", category);
}
// And so on...
Query query = session.createQuery(hql.toString());
Iterator<String> iter = params.keySet().iterator();
while (iter.hasNext()) {
String name = iter.next();
Object value = params.get(name);
query.setParameter(name, value);
}
List results = query.list()
But, actually, my recommendation would be to use the Criteria API here:
但是，实际上，我的建议是在此处使用 Criteria API：
Criteria criteria = session.createCriteria(Product.class);
if (category != null) {
criteria.add(Expression.eq("category", category);
}
// And so on...
List results = criteria.list();
Much simpler for complicated dynamic queries.
对于复杂的动态查询要简单得多。
回答by palmal
To archive parameters become optional you can write a query without having to use the Criteria API:
要将存档参数变为可选，您可以编写查询而无需使用 Criteria API：
select o from Product o WHERE :value is null or :value='' or o.category = :value
回答by changeme
You are right almost with small change.
您几乎是对的，只需稍加改动即可。
Query query = entityManager.createQuery("select o from Product o WHERE o.category = :value");
query.setParameter("value", category);
in setParamater "value"(exact text) should match with ":value"in query.
在 setParamater 中“value”（精确文本）应该与查询中的“:value”匹配。
回答by Veer Muchandi
How can I set category to any category in JPA? So if the null category passed, I simple ignore the category parameter, select all products.
如何将类别设置为 JPA 中的任何类别？所以如果空分类通过，我简单的忽略分类参数，选择所有产品。
You can set the category to "%". if (category == null) query.setParameter("category", "%"); else query.setParameter("category", category);
您可以将类别设置为“%”。if (category == null) query.setParameter("category", "%"); else query.setParameter("category", category);
回答by Thilo
SELECT * FROM PRODUCT WHERE CATEGORY=*
SELECT * FROM PRODUCT WHERE CATEGORY=*
I think you are new to SQL, too.
我认为您也是 SQL 新手。
WHERE CATEGORY = *does not mean "any category" (it is not even valid SQL).
WHERE CATEGORY = *并不意味着“任何类别”（它甚至不是有效的 SQL）。
In both SQL and JPA, you would just not have the WHEREclause at all if you want any category (or in SQL you could have WHERE CATEGORY IS NOT NULL).
在 SQL 和 JPA 中，WHERE如果您想要任何类别（或在 SQL 中您可以拥有WHERE CATEGORY IS NOT NULL），则根本没有该子句。
回答by J-Alex
Pascal Thiventgave pretty good answerrecommending using Criteria. However the Criteriain his answer is pure Hibernatecriteria. Using JPA Criteriaexample:
Pascal Thivent给出了很好的答案，推荐使用Criteria. 然而Criteria，他的回答是纯粹的Hibernate标准。使用JPA Criteria示例：
private List<Result> getProduct(String category) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> criteria = builder.createQuery(Product.class);
Root<Product> productRoot = criteria.from(Product.class);
if (category != null)
criteria.where(builder.equal(productRoot.get("category"), category))
}
entityManager.createQuery(criteria).getResultList();
}
回答by Rakib
The thing you are trying to achieve is counter intuitive in terms of design pattern. Let's think of the problems in SQL terms ignoring all JPA and other.
您试图实现的目标在设计模式方面与直觉相反。让我们想想 SQL 术语中忽略所有 JPA 和其他的问题。
The corresponding SQL query of your JPQL looks like below
您的 JPQL 的相应 SQL 查询如下所示
SELECT o.* FROM product o WHERE o.category = 'SOME_CAT';
Now if you pass null instead of category SOME_CATthe SQL would be like
现在，如果您传递 null 而不是 category，SOME_CAT则 SQL 会像
SELECT o.* FROM product o WHERE o.category IS NULL;
There is no SQL standard to invert the result set altering param value unless your SQL looks like following
除非您的 SQL 如下所示，否则没有 SQL 标准来反转结果集更改参数值
SELECT o.* FROM product o WHERE o.category IS NOT NULL;
The similar JPQL will look like
类似的 JPQL 看起来像
SELECT o FROM Product o WHERE o.category <> :param
You can see that we need to invert the logical operation instead of manipulating the param. There is no standard way to achive this behavior unless you build JPQL dynamically with if else condition.
可以看到我们需要反转逻辑操作而不是操作参数。除非您使用 if else 条件动态构建 JPQL，否则没有实现此行为的标准方法。
I would prefer a separate method handler one for filtering with category and other for listing all regardless of category.
我更喜欢一个单独的方法处理程序，一个用于过滤类别，另一个用于列出所有类别而不管类别。
