java 在@NamedQuery (JPA QL 1.0) 中设置为空的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6214142/
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
set parameter which is null in @NamedQuery (JPA QL 1.0)
提问by Lukasz
I am writing a JPA QL named query that would search for documents. This should be done in only one query and I cannot switch to native SQL (that's non functional requirement). The query I wrote looks as follows:
我正在编写一个 JPA QL 命名查询来搜索文档。这应该只在一个查询中完成，我不能切换到本机 SQL（这是非功能要求）。我写的查询如下所示：
query = "select doc from Doc doc " +
" join doc.Csts cst " +
" where cst.cstFrstNm like :FrstNm " +
" and cst.cstLastNm like :LastNm " +
" and doc.resId = :id ";
This query is parametrized using following instructions:
此查询使用以下说明进行参数化：
query.setParameter("FrstNm ", firstName + '%');
query.setParameter("LastNm ", lastName + '%');
query.setParameter("id", resId);
firstName, lastName, resIdin the above code are my search criteria and they are all Strings and they can be null. When one of them is null, it shouldn't be taken into consideration when the query is invoked.
firstName, lastName, resId在上面的代码中是我的搜索条件，它们都是字符串，它们可以为空。当其中之一为空时，在调用查询时不应考虑它。
For instance:
例如：
Let's say that: firstName = "John", lastName = "Doe" and resId is null, then the query should return all the entries from Doc table that are for user John Doe, no matter what is their resId.
假设：firstName = "John", lastName = "Doe" 并且 resId 为空，那么查询应该返回 Doc 表中所有属于用户 John Doe 的条目，不管他们的 resId 是什么。
I was trying to put additional OR and AND conditions into query that'd check if resId is null but it didn't work. I'm testing it on HSQLDB.
我试图将额外的 OR 和 AND 条件放入查询中，以检查 resId 是否为空，但它不起作用。我正在 HSQLDB 上测试它。
Is it any way to modify this JPA query to handle null values?
有没有办法修改这个 JPA 查询来处理空值？
回答by Vivien Barousse
Two solutions:
两种解决方案：
First one, don't use a named query and craft a new query for each request:
第一个，不要使用命名查询并为每个请求制作一个新查询：
boolean first = true;
query = query = "select doc from Doc doc " +
" join doc.Csts cst " +
if (firstName != null) {
query += (first ? " where " : " and ");
query += " cst.cstFrstNm like :FrstNm ";
first = false;
}
query += ";";
// ...
Second solution, you are using wildcard. If your parameter is empty, just put a '%' in your query, which will match all corresponding strings:
第二种解决方案，您正在使用通配符。如果您的参数为空，只需在查询中添加一个 '%'，它将匹配所有相应的字符串：
query.setParameter("FrstNm ", (firstName != null ? firstName : "") + '%');
query.setParameter("LastNm ", (lastName != null ? lastName : "") + '%');
If firstName is empty, your query will look like:
如果 firstName 为空，您的查询将如下所示：
... cst.cstFrstNm like '%' ...
and will match all the values. It will result in the parameter not filtering any result, with a behaviour similar as if it wasn't present.
并将匹配所有值。这将导致参数不过滤任何结果，行为类似于它不存在。
回答by Edwin Dalorzo
You may need to build the query dynamically to satisfy your parameters. For instance:
您可能需要动态构建查询以满足您的参数。例如：
public List<Employee> findEmployees(String name, String deptName, String projectName, String city) {
StringBuffer query = new StringBuffer();
query.append("SELECT DISTINCT e ");
query.append("FROM Employee e LEFT JOIN e.projects p ");
query.append("WHERE ");
List<String> criteria = new ArrayList<String>();
if (name != null) { criteria.add("e.name = :name"); }
if (deptName != null) { criteria.add("e.dept.name = :dept"); }
if (projectName != null) { criteria.add("p.name = :project"); }
if (city != null) { criteria.add("e.address.city = :city"); }
if (criteria.size() == 0) {
throw new RuntimeException("no criteria");
}
for (int i = 0; i < criteria.size(); i++) {
if (i > 0) { query.append(" AND "); }
query.append(criteria.get(i));
}
Query q = em.createQuery(query.toString());
if (name != null) { q.setParameter("name", name); }
if (deptName != null) { q.setParameter("dept", deptName); }
if (projectName != null) { q.setParameter("project", projectName); }
if (city != null) { q.setParameter("city", city); }
return (List<Employee>)q.getResultList();
}
You migt also like to consider Criteria API if you are using JPA 2.0 for this kind of task.
如果您将 JPA 2.0 用于此类任务，您还喜欢考虑 Criteria API。
