java 在休眠中如何处理查询参数为空或为空？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11411641/
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
How to handle if query parameter is null or empty in hibernate?
提问by Prathap
I'm using JPA, hibernate 3.
我正在使用 JPA，休眠 3。
String sqlQuery = " FROM TraceEntityVO where lotNumber =:lotNumber and mfrLocId=:mfrLocId and mfrDate=:mfrDate and qtyInitial=:qtyInitial and expDate=:expDate";
Query query = entityManager.createQuery(sqlQuery)
.setParameter("lotNumber", traceEntityVO.getLotNumber())
.setParameter("mfrLocId", traceEntityVO.getMfrLocId())
.setParameter("mfrDate", traceEntityVO.getMfrDate())
.setParameter("qtyInitial", traceEntityVO.getQtyInitial())
.setParameter("expDate", traceEntityVO.getExpDate());
This query works like a charm when the there were no empty or null values. But there could be possible of null or empty value for traceEntityVO.getLotNumber(),traceEntityVO.getMfrLocId(),traceEntityVO.getExpDate().
当没有空值或空值时，此查询就像一个魅力。但是，traceEntityVO.getLotNumber()、traceEntityVO.getMfrLocId()、traceEntityVO.getExpDate()可能存在空值或空值。
In this case the value 'null' or '' is checked against the variable instead of is nullcondition. How do I handle when I'm not sure about the parameter value, either null or empty?
在这种情况下，根据变量而不是空条件检查值 'null' 或 '' 。当我不确定参数值是 null 还是空时，我该如何处理？
I don't want to construct the query dynamically based on the values if empty or null.
如果为空或为空，我不想根据值动态构造查询。
Is this possible?
这可能吗？
Thanks in advance..
提前致谢..
采纳答案by Flavio
回答by Java SE
I hope following code will sort your problem. Assuming getMfrDate and getExpDate will return Date Object and others either Number or String objects. But you can modify IsEmpty according to return types.
我希望以下代码可以解决您的问题。假设 getMfrDate 和 getExpDate 将返回日期对象和其他数字或字符串对象。但是您可以根据返回类型修改 IsEmpty。
String sqlQuery = " FROM TraceEntityVO where lotNumber :lotNumber
and mfrLocId :mfrLocId and mfrDate :mfrDate and qtyInitial :qtyInitial and
expDate :expDate";
Query query = entityManager.createQuery(sqlQuery)
.setParameter("lotNumber", isEmpty(traceEntityVO.getLotNumber()))
.setParameter("mfrLocId", isEmpty(traceEntityVO.getMfrLocId()))
.setParameter("mfrDate", isEmpty(traceEntityVO.getMfrDate()))
.setParameter("qtyInitial", isEmpty(traceEntityVO.getQtyInitial()))
.setParameter("expDate", isEmpty(traceEntityVO.getExpDate()));
private String isEmpty(Object obj) {
if(obj!=null) {
if (obj instanceof java.util.Date) {
return " = to_date('"+obj.toString()+"') ";
} else if(obj instanceof String) {
return " = '"+obj.toString()+"' ";
} else if (obj instanceof Integer) {
return " = "+obj.toString()+" ";
}
}
return new String(" is null ");
}
回答by Hafaiedh Chedly
build your query according to your parameters (null or not) using the conditional statement if() like this example for the date:
使用条件语句 if() 根据您的参数（null 或 not）构建查询，例如此日期示例：
String jpqlQuery = "select f from Formation f where f.libelleFormation Like :libelleFormation and f.statutFormation Like :statutFormation and f.codeFormation Like :codeFormation";
if (critereRecherche.getDateDebFormation() != null) {
jpqlQuery = jpqlQuery.concat(" and f.dateDebFormation > :dateDebFormation");
}
if (critereRecherche.getDateFinFormation() != null) {
jpqlQuery = jpqlQuery.concat(" and f.dateFinFormation < :dateFinFormation");
}
Query query= em.createQuery(jpqlQuery);
query.setParameter("libelleFormation","%"+critereRecherche.getLibelleFormation()+"%");
query.setParameter("statutFormation","%"+critereRecherche.getStatutFormation()+"%");
query.setParameter("codeFormation","%"+critereRecherche.getCodeFormation()+"%");
if (critereRecherche.getDateDebFormation() != null) {
query.setParameter("dateDebFormation",critereRecherche.getDateDebFormation(),TemporalTyp.DATE);
}
if (critereRecherche.getDateFinFormation() != null) {
query.setParameter("dateFinFormation",critereRecherche.getDateFinFormation(),TemporalType.DATE);
}
List<Formation> formations = query.getResultList();
return formations;
