Java调用PostgreSQL查询报错：语法错误缺失冒号

直接在PostgreSQL中运行以下查询语句可正常返回各最新版本的包：

select distinct on (a.id) a.id, p.app
from application a inner join package p on a.app_id = p.app_id 
where p.status = 'r'
ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[1]::integer DESC,
          (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[2]::integer DESC,
          (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[3]::integer DESC

但通过Java的EntityManager调用该原生查询时，出现语法错误：ERROR: syntax error at or near ":"，日志显示生成的查询语句中PostgreSQL的类型转换符::被变成了单个:：

select distinct on (a.id) a.id, p.app
from application a inner join package p on a.app_id = p.app_id 
where p.status = 'r'
ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[1]:integer DESC,
          (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[2]:integer DESC,
          (regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[3]:integer DESC

同时伴随报错：Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet。

Java代码中的查询语句拼接方式如下：

String query = BASE_QUERY +
                "WHERE  p.status = :status ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[1]::integer DESC,\n" +
                "          (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[2]::integer DESC,\n" +
                "          (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[3]::integer DESC";
        List<Application> fullList = entityManager.createNativeQuery(appListQuery, "Application")
            .setParameter("status", PACKAGE_READY.getValue())
            .getResultList();

表中app_ver字段的数据样例：

21.9.0-3-dev
21.9.0-3-dev-03
21.9.0-6
3.0.13-1
...

EntityManager的底层实现Hibernate在解析原生SQL时，会将连续的::错误识别为参数占位符:的异常情况，自动吃掉其中一个冒号，导致最终传递给PostgreSQL的SQL丢失了一个冒号，PostgreSQL无法识别残缺的类型转换语法，从而抛出语法错误。

Hibernate的参数解析逻辑会扫描SQL中的:来定位命名参数（比如代码中的:status），当遇到::时，它会误判为是占位符的笔误，进而修改为单个:，破坏了PostgreSQL特有的类型转换语法。

有两种常用的修复方式：

1. 替换为标准SQL的CAST()函数
   把PostgreSQL专属的::integer类型转换语法替换为标准SQL的CAST(xxx AS integer)，示例：

   ORDER BY a.app_id, CAST((regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[1] AS integer) DESC,
            CAST((regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[2] AS integer) DESC,
            CAST((regexp_matches(p.app_ver, '^(\d+)\.(\d+)\.(\d+)'))[3] AS integer) DESC
   

   这种方式兼容性更强，不会被Hibernate的参数解析逻辑干扰。

2. 转义冒号保留原语法
   在Java字符串中将::转义为\\:\\:，让Hibernate解析时保留完整的两个冒号，修改后的代码片段：

   String query = BASE_QUERY +
                   "WHERE  p.status = :status ORDER BY a.app_id, (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[1]\\:\\:integer DESC,\n" +
                   "          (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[2]\\:\\:integer DESC,\n" +
                   "          (regexp_matches(p.app_ver, '^(\\\\d+)\\\\.(\\\\d+)\\\\.(\\\\d+)'))[3]\\:\\:integer DESC";
   

   这样Hibernate会将转义后的字符串解析为原始的::，确保传递给PostgreSQL的SQL语法正确。

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