PostgreSQL查询在Java中报unexpected token: ON语法错误排查

问题场景 #

有一条PostgreSQL查询语句，用distinct on获取table1.profile_id并按table3.creation_date排序，在DBeaver客户端能正常执行，但嵌入Java代码通过Hibernate的createSQLQuery执行时，抛出java.sql.SQLSyntaxErrorException: unexpected token: ON错误。把Java生成的SQL复制回数据库客户端却又能正常运行。

原始查询语句 #

select profileId from (
    select distinct on (p.profile_id) p.profile_id as profileId, pr.creation_date as creationDate
    from table1 p
             
             join table2 o on (p.profile_id = o.offeree_profile_id) 
             join table3 pr on (o.offer_id = pr.offer_id ) 
    where 
     
             o.offer_status_id = 'ACCEPTED' 
             
    and (pr.status != 'TERMINATED') 
             and o.offeror_profile_id in 
    
    (select p.profile_id from table4 u 
    join table1 p on (p.user_id = u.user_id) 
    where customer_id = 'A'
    AND o.offeror_profile_id != o.offeree_profile_id)
    ) sub
order by creationDate desc limit 6

Java代码实现 #

String queryString = "select profileId from (" +
    "select distinct on (p.profile_id) p.profile_id as profileId, pr.creation_date as creationDate " +
    "from table1 p " +
    "join table2  o on (p.profile_id = o.offeree_profile_id ) " +
    "join table3 pr on (o.offer_id = pr.offer_id ) " +
    "where " +
    "o.offer_status_id = 'ACCEPTED' " +
    "and (pr.status != 'TERMINATED') " +
    "and o.offeror_profile_id in " +
    "(select p.profile_id from table4 u " +
    "join table1 p on (p.user_id = u.user_id) " +
    "where customer_id = :CustomerId " +
    "and o.offeror_profile_id != o.offeree_profile_id)" +
    ") sub " +
    "order by creationDate desc limit 6";


final Query profileIdQuery = getCurrentSession().createSQLQuery(queryString).setString("CustomerId", CustomerId);

List<BigInteger> profileIdList = profileIdQuery.list();

抛出错误信息 #

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ON

解决办法 #

1. 确保Hibernate使用PostgreSQL方言 #

检查Hibernate配置文件，确认hibernate.dialect设置为对应版本的PostgreSQL方言：

# 示例：PostgreSQL 10+ 适用
hibernate.dialect=org.hibernate.dialect.PostgreSQL10Dialect

若方言设置错误，Hibernate会使用通用SQL解析器，无法识别PostgreSQL特有的distinct on语法。

2. 替换distinct on为窗口函数写法 #

改用row_number()窗口函数实现相同逻辑，避免Hibernate对distinct on的解析问题，改写后的SQL如下：

select profileId from (
    select p.profile_id as profileId, pr.creation_date as creationDate,
           row_number() over (partition by p.profile_id order by pr.creation_date) as rn
    from table1 p
    join table2 o on (p.profile_id = o.offeree_profile_id) 
    join table3 pr on (o.offer_id = pr.offer_id ) 
    where 
        o.offer_status_id = 'ACCEPTED' 
        and (pr.status != 'TERMINATED') 
        and o.offeror_profile_id in 
        (select p.profile_id from table4 u 
         join table1 p on (p.user_id = u.user_id) 
         where customer_id = :CustomerId
         AND o.offeror_profile_id != o.offeree_profile_id)
) sub
where rn = 1
order by creationDate desc limit 6

该写法与distinct on效果一致：按p.profile_id分区，每个分区取第一条数据，且Hibernate对窗口函数的兼容性更好，不会触发语法解析错误。

3. 绕过Hibernate解析，直接用JDBC执行 #

若上述方法仍无效，可直接获取JDBC连接执行原生SQL：

String sql = "你的改写后SQL";
PreparedStatement stmt = getCurrentSession().doReturningWork(connection -> 
    connection.prepareStatement(sql)
);
stmt.setString("CustomerId", CustomerId);
ResultSet rs = stmt.executeQuery();
List<BigInteger> profileIdList = new ArrayList<>();
while(rs.next()) {
    profileIdList.add(rs.getBigDecimal("profileId").toBigInteger());
}

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