报错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();
}
}
}
最新发布