报错Caused By: javax.faces.view.facelets.FaceletException: 检验数据查询错误,{endDate=2025-07-24, notLikeKey=T11, startDate=2025-07-18},EJBException: You have attempted to set a parameter value using a name of locator that does not exist in the query string SELECT c.id, c.orgCode, c.locator, c.empNo, c.empName, c.itemCode, c.itemDesc, c.useableQty, c.unUseableQty, c.demageQty, c.offQty, c.signNo, c.checkoutEmpNo, c.checkoutEmpName, c.checkOutDate, c.finishedEmpNo, c.finishedEmpName, c.finishedDate, c.payMoney, c.overTime, MAX(n.oldToolGatherNo), c.requestNo, c.erpState FROM ToolCheckEntity c LEFT JOIN ToolNoteRequestEntity n ON n.requestNo = c.requestNo WHERE c.state='已完成' AND c.locator not like :notLikeKey AND (c.checkOutDate BETWEEN :startDate AND :endDate) group by c.id, c.orgCode, c.locator, c.empNo, c.empName,， // 构建动态查询语句
private String buildDynamicQuery(Map<String, String> queryMap, Map<String, String> sortMap, boolean isCountQuery) {
StringBuilder sqlBuilder = new StringBuilder();
if (isCountQuery) {
// sqlBuilder.append("SELECT COUNT(c) ");
sqlBuilder.append("SELECT COUNT(DISTINCT c.id) FROM ToolCheckEntity c ");
} else {
sqlBuilder.append("SELECT c.id, c.orgCode, c.locator, c.empNo, c.empName, ");
sqlBuilder.append("c.itemCode, c.itemDesc, c.useableQty, c.unUseableQty, c.demageQty, ");
sqlBuilder.append("c.offQty, c.signNo, c.checkoutEmpNo, c.checkoutEmpName, c.checkOutDate, ");
sqlBuilder.append("c.finishedEmpNo, c.finishedEmpName, c.finishedDate, c.payMoney, c.overTime, ");
sqlBuilder.append("MAX(n.oldToolGatherNo), c.requestNo, c.erpState ");
sqlBuilder.append("FROM ToolCheckEntity c LEFT JOIN ToolNoteRequestEntity n ON n.requestNo = c.requestNo ");
}
// StringBuilder sqlBuilder = new StringBuilder();
// sqlBuilder.append("SELECT c.id, c.orgCode, c.locator, c.empNo, c.empName, ");
// sqlBuilder.append("c.itemCode, c.itemDesc, c.useableQty, c.unUseableQty, c.demageQty, ");
// sqlBuilder.append("c.offQty, c.signNo, c.checkoutEmpNo, c.checkoutEmpName, c.checkOutDate, ");
// sqlBuilder.append("c.finishedEmpNo, c.finishedEmpName, c.finishedDate, c.payMoney, c.overTime, ");
// sqlBuilder.append("n.oldToolGatherNo, c.requestNo, c.erpState ");
// sqlBuilder.append("FROM ToolCheckEntity c LEFT JOIN ToolNoteRequestEntity n ON n.requestNo = c.requestNo ");
sqlBuilder.append("WHERE ");
sqlBuilder.append(" c.state='已完成' ");
// 添加WHERE条件
if (queryMap != null && !queryMap.isEmpty()) {
// boolean firstCondition = true;
// 处理orgCode条件
if (queryMap.containsKey("orgCode") && StringUtils.isNotBlank(queryMap.get("orgCode"))) {
sqlBuilder.append(" AND c.orgCode = :orgCode ");
}
// 处理locator条件
if (queryMap.containsKey("locator") && StringUtils.isNotBlank(queryMap.get("locator"))) {
sqlBuilder.append(" AND c.locator = :locator ");
}
// 处理empNo条件
if (queryMap.containsKey("empNo") && StringUtils.isNotBlank(queryMap.get("empNo"))) {
sqlBuilder.append(" AND c.empNo = :empNo ");
}
if (queryMap.containsKey("itemCode") && StringUtils.isNotBlank(queryMap.get("itemCode"))) {
sqlBuilder.append(" AND c.itemCode like :itemCode ");
}
if (queryMap.containsKey("notLikeKey") && StringUtils.isNotBlank(queryMap.get("notLikeKey"))) {
sqlBuilder.append(" AND c.locator not like :notLikeKey ");
}
if (queryMap.containsKey("startDate") && StringUtils.isNotBlank(queryMap.get("startDate"))&&
queryMap.containsKey("endDate") && StringUtils.isNotBlank(queryMap.get("endDate")) ){
// 添加日期范围条件 - 根据业务需求选择合适的日期字段
sqlBuilder.append(" AND (c.checkOutDate BETWEEN :startDate AND :endDate) ");
}
}
if (!isCountQuery) {
String groupByStr=" group by c.id, c.orgCode, c.locator, c.empNo, c.empName, \n" +
" c.itemCode, c.itemDesc, c.useableQty, c.unUseableQty, \n" +
" c.demageQty, c.offQty, c.signNo, c.checkoutEmpNo, \n" +
" c.checkoutEmpName, c.checkOutDate, c.finishedEmpNo, \n" +
" c.finishedEmpName, c.finishedDate, c.payMoney, \n" +
" c.overTime, c.requestNo, c.erpState";
sqlBuilder.append(groupByStr);
String orderBy=" order by c.checkOutDate desc";
sqlBuilder.append(orderBy);
}
return sqlBuilder.toString();
}
// 设置查询参数
private void setQueryParameters(Query query, Map<String, String> queryMap) {
if (queryMap == null) return;
if (queryMap.containsKey("orgCode") && StringUtils.isNotBlank(queryMap.get("orgCode"))) {
query.setParameter("orgCode", queryMap.get("orgCode"));
}
if (queryMap.containsKey("locator") && StringUtils.isNotBlank(queryMap.get("locator"))) {
query.setParameter("locator", queryMap.get("locator"));
}
if (queryMap.containsKey("empNo") && StringUtils.isNotBlank(queryMap.get("empNo"))) {
query.setParameter("empNo", queryMap.get("empNo"));
}
if (queryMap.containsKey("itemCode") && StringUtils.isNotBlank(queryMap.get("itemCode"))) {
query.setParameter("itemCode", "%"+queryMap.get("itemCode")+"%");
}
if (queryMap.containsKey("notLikeKey") && StringUtils.isNotBlank(queryMap.get("notLikeKey"))) {
query.setParameter("locator", queryMap.get("notLikeKey")+"%");
}
if (queryMap.containsKey("startDate") && StringUtils.isNotBlank(queryMap.get("startDate"))&&
queryMap.containsKey("endDate") && StringUtils.isNotBlank(queryMap.get("endDate"))
) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm"); // 定义日期格式
Date startDate = sdf.parse(queryMap.get("startDate").toString()+" 00:00");
Date endDate = sdf.parse(queryMap.get("endDate").toString()+" 23:59");
long betweenDays = (endDate.getTime() - startDate.getTime()) / 24 / 60 / 60 / 1000;
if (betweenDays > 7) {
throw new EJBException("查询时间跨度不能超过7天");
}else {
// 设置查询参数
query.setParameter("startDate", startDate);
query.setParameter("endDate", endDate);
}
} catch (ParseException e) {
e.printStackTrace();
}
}
}
最新发布
本文解决了一个JPA复杂查询中出现的类型不匹配错误。错误发生在使用字符串与枚举类型的比较时，通过直接比较枚举实例而非其名称解决了该问题。
扫一扫
1147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
