sql报错识别不了 BETWEEN :startDate and :endDate , public List<EventDataReportDm> getEventDataReportList(int first, int pageSize, Map<String, String> queryMap,
Map<String, String> sortMap) {
try {
this.evictAllEm();
String querySql = this.buildEventDataReportQuery(queryMap, sortMap, false);
Query query = this.em.createNativeQuery(querySql);
query.setFirstResult(first);
query.setMaxResults(pageSize);
this.setQueryParameters(query, queryMap);
List<Object> list = query.getResultList();
return this.mapResultsToEntities(list);
} catch (Exception e) {
throw new EJBException(e.getMessage());
}
}
private String buildEventDataReportQuery(Map<String, String> queryMap, Map<String, String> sortMap, boolean isCountQuery) {
StringBuilder sqlBuilder = new StringBuilder();
if (isCountQuery) {
//查询记录条数
} else {
sqlBuilder.append("SELECT \n" +
" a.tag4 AS empNo,\n" +
" COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, a.confirm_time, b.first_transfer) <= 15 THEN 1 END) AS normalCount,\n" +
" COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, a.confirm_time, b.first_transfer) > 15 THEN 1 END) AS timeoutCount\n" +
"FROM (\n" +
" SELECT \n" +
" tag4,\n" +
" tag5,\n" +
" SUBSTRING_INDEX(data_key, ',', 1) AS order_num,\n" +
" SUBSTRING_INDEX(SUBSTRING_INDEX(data_key, ',', 2), ',', -1) AS plate_num,\n" +
" MIN(update_date) AS confirm_time -- 唯一标识的最早下机时间\n" +
" FROM LKM_EVENT_DATA WHERE data_type = '下机确认' ");
if (queryMap != null && !queryMap.isEmpty()) {
if (queryMap.get("updateDate") != null && queryMap.get("updateDate").matches("(?i)BETWEEN_.*")) {
sqlBuilder.append(" AND update_date BETWEEN :startDate and :endDate ");
}
}
sqlBuilder.append(" GROUP BY \n" +
" tag4,\n" +
" tag5,\n" +
" SUBSTRING_INDEX(data_key, ',', 1),\n" +
" SUBSTRING_INDEX(SUBSTRING_INDEX(data_key, ',', 2), ',', -1) ");
sqlBuilder.append(" ) a ");
sqlBuilder.append(" JOIN (\n" +
" SELECT \n" +
" tag4,\n" +
" tag5,\n" +
" SUBSTRING_INDEX(data_key, ',', 1) AS order_num,\n" +
" SUBSTRING_INDEX(SUBSTRING_INDEX(data_key, ',', 2), ',', -1) AS plate_num,\n" +
" MIN(update_date) AS first_transfer -- 唯一标识的最早转序时间\n" +
" FROM LKM_EVENT_DATA\n" +
" WHERE data_type = '转序'\n" +
" GROUP BY \n" +
" tag4,\n" +
" tag5,\n" +
" SUBSTRING_INDEX(data_key, ',', 1),\n" +
" SUBSTRING_INDEX(SUBSTRING_INDEX(data_key, ',', 2), ',', -1)\n" +
") b ON a.tag4 = b.tag4\n" +
" AND a.tag5 = b.tag5\n" +
" AND a.order_num = b.order_num\n" +
" AND a.plate_num = b.plate_num\n" +
"GROUP BY a.tag4; ");
}
logger.info("ZhuanXuSql:"+sqlBuilder.toString());
return sqlBuilder.toString();
}
private void setQueryParameters(Query query, Map<String, String> queryMap) {
if (queryMap != null) {
if (queryMap.containsKey("tag4") && StringUtils.isNotBlank((String) queryMap.get("tag4"))) {
query.setParameter("tag4", queryMap.get("tag4"));
}
if (queryMap.get("updateDate") != null && queryMap.get("updateDate").matches("(?i)BETWEEN_.*")) {
String[] val = queryMap.get("updateDate").substring(8).split(",");
if (val != null && val.length == 2) {
logger.info("startDate:" + val[0]);
logger.info("endDate:" + val[1]);
query.setParameter("startDate", val[0]);
query.setParameter("endDate", val[1]);
}
}
}
}
本文为一篇示例转载文章，具体细节与技术内容未给出，通常这类文章会包含作者的观点或者对原文的理解与总结。
扫一扫
19
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
