Spring Boot查询日期范围时String转Timestamp失败的解决方法问询

问题根源 #

1. Repository查询语句存在参数引用错误，且不必要地对Timestamp参数进行日期转换
2. Spring默认格式化器无法通过@DateTimeFormat直接完成String到java.sql.Timestamp的转换

方案一：改用Java 8 LocalDate（推荐，Spring对现代日期类型支持更完善） #

步骤1：修改Controller参数类型

将Timestamp替换为LocalDate，保留@DateTimeFormat注解：

@GetMapping(FETCH_MODULES)
public HashMap<String, Object> getModules(@RequestParam("page") int page,
                                          @RequestParam("limit") int limit,
                                          @RequestParam(value = "code", required = false) String code,
                                          @RequestParam(value = "name", required = false) String name,
                                          @RequestParam(value = "is_active", required = false) Boolean is_active,
                                          @RequestParam(value = "create_date_before", required = false) @DateTimeFormat(pattern ="yyyy-MM-dd") LocalDate first_date,
                                          @RequestParam(value = "create_date_after", required = false) @DateTimeFormat(pattern ="yyyy-MM-dd") LocalDate second_date){
    // 原有逻辑
}

步骤2：更新Service和Repository方法签名

Service层：

public ArrayList<ModuleEntity> findAllWithFilters(String name, String code, Boolean is_active, LocalDate first_date, LocalDate second_date) {
    return moduleRepository.findAllWithFilters(name, code, is_active, first_date, second_date);
}

Repository层：修正参数引用错误，并调整查询语句以匹配LocalDate类型（注意数据库语法差异）：

@Query(value = "SELECT * FROM module m WHERE (:name is null or m.name ilike :name||'%') AND " +
        "(:code is null or m.code ilike :code||'%' ) AND (:is_active is null or m.is_active = :is_active)" +
        "AND (:first_date is null or m.create_date::date between :first_date and :second_date) ORDER BY id DESC", nativeQuery = true)
ArrayList<ModuleEntity> findAllWithFilters(String name, String code, Boolean is_active, LocalDate first_date, LocalDate second_date);

注意：m.create_date::date是PostgreSQL语法，MySQL请改用DATE(m.create_date)。

方案二：自定义String到Timestamp转换器 #

步骤1：实现转换器类

创建自定义转换器处理字符串到Timestamp的转换：

import org.springframework.core.convert.converter.Converter;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class StringToTimestampConverter implements Converter<String, Timestamp> {
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    @Override
    public Timestamp convert(String source) {
        if (source == null || source.trim().isEmpty()) {
            return null;
        }
        // 将字符串解析为LocalDate，再转换为当天起始时间的Timestamp
        LocalDate localDate = LocalDate.parse(source, DATE_FORMATTER);
        LocalDateTime startOfDay = localDate.atStartOfDay();
        return Timestamp.valueOf(startOfDay);
    }
}

步骤2：注册转换器

在配置类中注册自定义转换器：

import org.springframework.context.annotation.Configuration;
import org.springframework.format.FormatterRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {
    @Override
    public void addFormatters(FormatterRegistry registry) {
        registry.addConverter(new StringToTimestampConverter());
    }
}

步骤3：修正Repository查询语句

移除错误的参数引用和不必要的to_date调用：

@Query(value = "SELECT * FROM module m WHERE (:name is null or m.name ilike :name||'%') AND " +
        "(:code is null or m.code ilike :code||'%' ) AND (:is_active is null or m.is_active = :is_active)" +
        "AND (:first_date is null or m.create_date between :first_date and :second_date) ORDER BY id DESC", nativeQuery = true)
ArrayList<ModuleEntity> findAllWithFilters(String name, String code, Boolean is_active, Timestamp first_date, Timestamp second_date);

若要包含结束日期的全天数据（到23:59:59），可在转换器中对结束日期单独处理，或在Service层将second_date调整为当天的最后一刻。

额外注意事项 #

• 原查询语句中(:create_date is null ...是参数引用错误，应改为:first_date
• 使用Timestamp时，确保起始和结束时间覆盖完整日期范围，避免遗漏数据

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