Jpa Repository原生查询中单引号内@Param参数绑定失败如何解决

问题原因 #

你将绑定参数:timeThreshold放在了单引号内，JPA会将单引号内的内容识别为固定字符串常量，不会把它解析为待绑定的参数占位符，所以Hibernate找不到可绑定的timeThreshold参数，自然绑定失败。

解决方案 #

方案1：移除参数外的单引号，直接使用interval语法 #

绝大多数支持interval语法的数据库（PostgreSQL、MySQL 8.0+等）都支持interval后直接跟参数占位符，你只需要删掉包裹timeThreshold的单引号即可：

@Transactional(readOnly = true)
@Query(value = " SELECT c.ID " +
               " FROM table1 clh " +
               " LEFT JOIN table2 nks " +
               " on clh.SERIAL = nks.SERIAL_REF " +
               " WHERE clh.CREATED_DATE >= :now - interval :timeThreshold HOUR " +
               " AND nks.SERIAL_REF IS NULL" , nativeQuery = true)
List<Long> getIdsWithoutAnswer (@Param("timeThreshold") Integer timeThreshold, @Param("now") LocalDateTime now);

方案2：使用数据库内置的日期计算函数（兼容性更好） #

如果你使用的数据库不支持上述interval加参数的写法，可以换用对应数据库的日期操作函数，完全避免单引号问题，比如MySQL用DATE_SUB：

@Transactional(readOnly = true)
@Query(value = " SELECT c.ID " +
               " FROM table1 clh " +
               " LEFT JOIN table2 nks " +
               " on clh.SERIAL = nks.SERIAL_REF " +
               " WHERE clh.CREATED_DATE >= DATE_SUB(:now, INTERVAL :timeThreshold HOUR) " +
               " AND nks.SERIAL_REF IS NULL" , nativeQuery = true)
List<Long> getIdsWithoutAnswer (@Param("timeThreshold") Integer timeThreshold, @Param("now") LocalDateTime now);

方案3：拼接数值和单位为完整字符串参数（极端场景用） #

如果你的业务场景必须要把interval的参数作为整体字符串传入，可以直接把X HOUR作为完整参数绑定，不需要在SQL里写死单位：

// 调用层提前拼接好单位
Integer hour = 24;
String timeThreshold = hour + " HOUR";

// Repository层接口写法
@Transactional(readOnly = true)
@Query(value = " SELECT c.ID " +
               " FROM table1 clh " +
               " LEFT JOIN table2 nks " +
               " on clh.SERIAL = nks.SERIAL_REF " +
               " WHERE clh.CREATED_DATE >= :now - interval :timeThreshold " +
               " AND nks.SERIAL_REF IS NULL" , nativeQuery = true)
List<Long> getIdsWithoutAnswer (@Param("timeThreshold") String timeThreshold, @Param("now") LocalDateTime now);

注意：不要通过Java字符串拼接的方式把参数值拼到SQL语句里，会存在SQL注入风险，也会导致数据库无法复用SQL执行计划，性能下降。

内容的提问来源于stack exchange，提问作者Martin Sørensen