Spring JPA原生查询报错：PostgreSQL字符串转整数范围查询

问题背景 #

PostgreSQL数据库的data表包含字符串类型列range1和range2，需要查询指定整数处于这两列转换为整数后的区间内的数据。已验证可正常运行的PostgreSQL原生SQL如下：

SELECT * FROM data where code = 'DXB' and 5733 between range1::integer and range2::integer;

尝试通过Spring JPA Repository的原生查询复现该逻辑，但执行报错。

报错信息 #

org.springframework.dao.InvalidDataAccessResourceUsageException: 无法执行查询；SQL [ SELECT r from data r where r.code=? and ? between CAST(r.range as int) and CAST(r.range2 as int)]; 嵌套异常为 org.hibernate.exception.SQLGrammarException: 无法执行查询

错误原因 #

1. 原生SQL语法错误：开启nativeQuery = true后，需使用纯数据库原生SQL语法，不能用JPA实体别名写法SELECT r from data r，原生查询应直接查询表字段或使用*。
2. 列名拼写错误：原SQL中的列名为range1，但JPA查询中误写为r.range，缺少数字后缀1。
3. 类型转换语法不一致：虽然PostgreSQL支持CAST(... as int)，但原运行正常的SQL使用::integer语法，保持一致更稳妥。

修正后的代码 #

@Query(value = "SELECT * FROM data WHERE code = :code AND :serial BETWEEN range1::integer AND range2::integer",
       nativeQuery = true)
Data findByCodeAndSerialInRange(String code, int serial);

修正说明 #

• 将SELECT r from data r替换为原生SQL的SELECT * FROM data，JPA会自动将查询结果映射到Data实体类。
• 修正列名错误，将r.range改为range1。
• 沿用PostgreSQL原生类型转换语法::integer，与原验证通过的SQL保持一致。
• 方法名调整为符合Spring JPA命名规范的findByCodeAndSerialInRange，增强可读性。

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