JPA查询拼接日期时间列并与指定日期时间比较的实现问题

使用Spring Data JPA实现会话数据筛选逻辑，核心需求为：

• 将Session实体中存储日期的startDt字段、存储时间的startTime字段拼接后转换为datetime类型
• 将转换后的时间值与目标时间（传入的LocalDateTime类型参数/当前系统时间）做大于等于匹配，拉取符合条件的会话数据
  原有实现代码如下：

@Query(select s from Session s where ( datetime(concat(s.startDt,'',s.startTime) ) >= :lDate))
List<Session> getUpcommingSessionList(@Param("lDate")LocalDateTime lDate);

• 语法错误：@Query注解的查询语句缺失前后闭合的双引号，编译阶段直接报错
• 函数不兼容：标准JPQL不支持直接调用MySQL的datetime()转换函数，直接运行会抛出函数未定义异常
• 拼接逻辑错误：concat函数内日期和时间的连接符是空字符串，拼接后会生成类似2024-06-1519:30:00的非法时间格式，无法转换为合法时间类型
• 未指定查询类型：如果要使用数据库原生函数，需要显式声明nativeQuery = true，否则JPA会按JPQL语法解析语句，导致原生函数无法识别

方案1：原生SQL写法（兼容性最好，无JPA方言限制） #

针对MySQL数据库，使用STR_TO_DATE显式指定时间转换格式，避免隐式转换带来的格式匹配问题：

// 匹配传入的指定时间
@Query(
    value = "SELECT * FROM session s WHERE STR_TO_DATE(CONCAT(s.start_dt, ' ', s.start_time), '%Y-%m-%d %H:%i:%s') >= :lDate",
    nativeQuery = true
)
List<Session> getUpcommingSessionList(@Param("lDate") LocalDateTime lDate);

// 直接匹配当前系统时间，无需传参
@Query(
    value = "SELECT * FROM session s WHERE STR_TO_DATE(CONCAT(s.start_dt, ' ', s.start_time), '%Y-%m-%d %H:%i:%s') >= NOW()",
    nativeQuery = true
)
List<Session> getUpcomingSessionListWithCurrentTime();

如果使用PostgreSQL等其他数据库，只需将时间转换函数替换为对应数据库的实现即可，例如PostgreSQL可使用TO_TIMESTAMP(CONCAT(s.start_dt, ' ', s.start_time), 'YYYY-MM-DD HH24:MI:SS')

方案2：JPQL写法（适配多数据库，无需写原生SQL） #

JPA 2.1及以上版本支持通过function关键字调用数据库函数，无需开启原生查询：

@Query("SELECT s FROM Session s WHERE FUNCTION('STR_TO_DATE', CONCAT(s.startDt, ' ', s.startTime), '%Y-%m-%d %H:%i:%s') >= :lDate")
List<Session> getUpcommingSessionList(@Param("lDate") LocalDateTime lDate);

如果startDt为LocalDate类型、startTime为LocalTime类型，可直接用JPQL的类型转换，完全不依赖数据库函数：

@Query("SELECT s FROM Session s WHERE CAST(CONCAT(s.startDt, ' ', s.startTime) AS LocalDateTime) >= :lDate")
List<Session> getUpcommingSessionList(@Param("lDate") LocalDateTime lDate);

• 拼接日期和时间时，两个字段中间必须加半角空格，否则生成的时间字符串格式非法，转换会失败
• 优先用显式格式声明做时间转换，不要依赖数据库的隐式转换，避免因字段存储格式变化导致查询失效
• 若需要匹配当前系统时间，既可以用数据库内置时间函数（如NOW()），也可以在调用Repository方法时直接传入LocalDateTime.now()作为参数，后者更方便做时间偏移的灵活计算

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