SpringBoot项目中MySQL日期字符串的范围查询问题

现有代码的核心问题 #

1. 日期格式不匹配：Controller中使用SimpleDateFormat("dd-MM-yyyy")解析UI传入的YYYY-MM-DD格式日期，直接会抛出ParseException，导致查询逻辑无法执行。
2. 字符串日期的比较逻辑错误：数据库中date字段存储的是'1/11/2020 17:18'这类非标准格式的字符串，JPA的findBydateBetween方法是按字符串字典序比较，而非日期逻辑，结果必然不符合预期；即使临时把数据库字段改成DATE类型，现有数据的带时间字符串格式也无法正确映射到DATE类型，导致查询失败。

保持字段为Varchar类型的解决方案 #

要实现正确的日期范围查询，关键是在查询阶段将字符串字段转换为数据库可识别的日期类型，再进行范围判断，以下是两种可行方式：

方案1：使用JPQL的FUNCTION函数转换日期 #

修改Repository方法，通过MySQL内置的STR_TO_DATE函数将字符串转成日期后再做范围查询：

public interface BaseDataRepository extends JpaRepository<BaseData,Long>{
    @Query("SELECT bd FROM BaseData bd WHERE FUNCTION('STR_TO_DATE', bd.date, '%d/%m/%Y %H:%i') BETWEEN :startDate AND :endDate")
    List<BaseData> findByDateRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
}

注意：%d/%m/%Y %H:%i要和数据库中字符串的实际格式匹配，你的插入数据是'1/11/2020 17:18'，对应格式是日/月/年 时:分；如果实际存储是月/日/年，要改成%m/%d/%Y %H:%i。

方案2：使用原生SQL查询 #

如果JPQL的FUNCTION不满足需求，直接用原生SQL实现：

public interface BaseDataRepository extends JpaRepository<BaseData,Long>{
    @Query(value = "SELECT * FROM base_data WHERE STR_TO_DATE(date, '%d/%m/%Y %H:%i') BETWEEN :startDate AND :endDate", nativeQuery = true)
    List<BaseData> findByDateRangeNative(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
}

修复Controller的日期解析逻辑 #

同时要修正Controller中的日期解析错误，匹配UI传入的YYYY-MM-DD格式：

//Input params format: 2023-02-27 // YYYY-MM-DD
@RequestMapping("/neteng/topten")
public Iterable<BaseData> getTopTen(@RequestBody Map<String, String> dates) {
    String startDateStr = dates.get("startDate");
    String endDateStr = dates.get("endDate");

    // 使用匹配UI格式的DateFormat，开启严格解析避免格式错误自动转换
    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    sdf.setLenient(false);

    try {
        java.util.Date startDate = sdf.parse(startDateStr);
        java.util.Date endDate = sdf.parse(endDateStr);

        return baseDataRepository.findByDateRange(startDate, endDate);

    } catch (ParseException e) {
        e.printStackTrace();
        // 返回空列表或自定义错误对象，避免返回字符串不匹配方法返回值类型
        return Collections.emptyList();
    }
}

优化建议（可选） #

如果后续有机会，建议将数据库中的字符串日期批量转换为标准的YYYY-MM-DD HH:mm:ss格式，这样可以直接用字符串进行范围查询（该格式的字典序和日期顺序一致），性能会比每次查询转换更高：

1. 执行SQL批量更新：

UPDATE base_data SET date = DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y %H:%i'), '%Y-%m-%d %H:%i:%s');

2. 修改POJO中date字段为String，Repository方法改为：

List<BaseData> findByDateBetween(String startDate, String endDate);

此时Controller只需将UI传入的YYYY-MM-DD拼接成YYYY-MM-DD 00:00:00和YYYY-MM-DD 23:59:59即可直接查询。

内容的提问来源于stack exchange，提问作者Raftery