LambdaQueryWrapper 日期格式转换,匹配精确日期及为空日期
/** * 日期过滤 */ private void dateFilter(String dateField, LambdaQueryWrapper<TmpOrderAuxiliaryPlanEntity> wrapper, String fieldName) { List<String> dateArray; String sqlDateField = DataConversionUtil.humpToUnderline(fieldName); dateArray = Arrays.stream(dateField.split(Constant.SEP)).collect(Collectors.toList()); StringBuilder sql = new StringBuilder(); for (int i = 0; i < dateArray.size(); i++) { sql.append(Constant.SINGLE_QUOTE).append(dateArray.get(i)).append(Constant.SINGLE_QUOTE).append(Constant.SEP); } String stringSql = sql.deleteCharAt(sql.length() - 1).toString(); //查询日期满足条件且为空的值 //wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ") or " + sqlDateField + " is null"); if (stringSql.contains(Constant.NULL)) { //wrapper.apply("" + sqlDateField + " is null"); if (sqlDateField.equals(DataConversionUtil.humpToUnderline(Constant.PLANNED_DELIVERY_DATE))) { wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ")").or().isNull(TmpOrderAuxiliaryPlanEntity::getPlannedDeliveryDate); //wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ")"); } } else { wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ")"); } }
queryWrapper匹配当前日期(不知道还有没有更好的写法):
String format = new SimpleDateFormat("yyyy-MM-dd").format(new Date()); String startDate = format + " 00:00:00"; String endDate = format + " 23:59:59"; queryWrapper.between(XXXEntity::getTime, startDate, endDate);
// 匹配当前日期 SQL写法
SELECT * FROM `table_xxx` WHERE xxx_time BETWEEN date_sub(now(), INTERVAL 0 DAY) AND DATE_SUB(now(),INTERVAL -1 DAY)
注意事项 :注意括号
错误写法:
wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ") or " + sqlDateField + " is null");
正确写法:
wrapper.apply("DATE_FORMAT("( + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ") or " + sqlDateField + " is null)");
或:
wrapper.apply("DATE_FORMAT(" + sqlDateField + ",'%Y-%m-%d') in (" + stringSql + ")").or().isNull(TmpOrderAuxiliaryPlanEntity::getPlannedDeliveryDate);
说明:
wrapper.apply():sql自由拼接方法 可能会有sql注入风险 也可用于MySql 可用于.last()方法之前
LambdaQueryWrapper:可以直接从实体中获取值,避免硬编码
DATE_FORMAT() sql函数 用于日期格式转换
Constant.SEP == “,” (逗号)
Constant.SINGLE_QUOTE == “'” (单引号)、
DataConversionUtil.humpToUnderline() :驼峰转下划线方法