Hibernate+PostgreSQL执行原生查询报错：integer = bytea运算符不存在

问题场景 #

使用Hibernate执行PostgreSQL原生查询时出现类型不匹配错误，查询代码如下：

String query = "SELECT  requester_id,operator_id,date(qr_gen_date_time) as date, sum(ticket_fare) as total_amount, count(*) as ticket_count "
+ "FROM ticket_info tktInfo INNER JOIN operator_info op on tktInfo.operator_info_id = op.id" + "INNER JOIN ticket tkt on op.ticket_id =  tkt.id "
+ "WHERE (requester_id = :requesterId or :requesterId isNull) "
+ " AND (operator_id = :operatorId or :operatorId isNull) "
+ " AND (date(qr_gen_date_time) >= :dateFrom or :dateFrom isNull) "
+ " AND (date(qr_gen_date_time) <= :dateTo or :dateTo isNull) "
+ "GROUP BY date(qr_gen_date_time),requester_id,operator_id "
+ "ORDER BY date(qr_gen_date_time) DESC "
+ "LIMIT 10 OFFSET ( :pageNum -1 ) * 10 ";

List<Tuple> result= entityManager.createNativeQuery(query, Tuple.class)
                                .setParameter("requesterId", requesterId)
                                .setParameter("operatorId", operatorId)
                                .setParameter("dateFrom", dateFrom)
                                .setParameter("dateTo", dateTo)
                                .setParameter("pageNum", pageNum).getResultList();

报错信息 #

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 282

中文翻译：

触发异常：org.postgresql.util.PSQLException: 错误：不存在操作符: integer = bytea
提示：没有匹配给定名称和参数类型的操作符，需添加显式类型转换。
位置：282

原因分析 #

当传入的参数（如requesterId、operatorId）为null时，Hibernate无法正确推断参数的JDBC类型，PostgreSQL会将null参数默认识别为bytea类型，而数据库中requester_id、operator_id是integer类型，两种类型无法直接比较，导致报错。

解决方案 #

方案1：显式指定参数JDBC类型 #

在设置参数时，明确指定参数对应的JDBC类型，避免PostgreSQL对null参数的类型误判：

List<Tuple> result= entityManager.createNativeQuery(query, Tuple.class)
                                .setParameter("requesterId", requesterId, JDBCType.INTEGER)
                                .setParameter("operatorId", operatorId, JDBCType.INTEGER)
                                .setParameter("dateFrom", dateFrom, JDBCType.DATE)
                                .setParameter("dateTo", dateTo, JDBCType.DATE)
                                .setParameter("pageNum", pageNum, JDBCType.INTEGER).getResultList();

注：需要导入java.sql.JDBCType包

方案2：修改查询条件写法 #

使用PostgreSQL的COALESCE函数重构null判断逻辑，避免直接比较null参数：

String query = "SELECT  requester_id,operator_id,date(qr_gen_date_time) as date, sum(ticket_fare) as total_amount, count(*) as ticket_count "
+ "FROM ticket_info tktInfo INNER JOIN operator_info op on tktInfo.operator_info_id = op.id" + "INNER JOIN ticket tkt on op.ticket_id =  tkt.id "
+ "WHERE requester_id = COALESCE(:requesterId, requester_id) "
+ " AND operator_id = COALESCE(:operatorId, operator_id) "
+ " AND date(qr_gen_date_time) >= COALESCE(:dateFrom, date(qr_gen_date_time)) "
+ " AND date(qr_gen_date_time) <= COALESCE(:dateTo, date(qr_gen_date_time)) "
+ "GROUP BY date(qr_gen_date_time),requester_id,operator_id "
+ "ORDER BY date(qr_gen_date_time) DESC "
+ "LIMIT 10 OFFSET ( :pageNum -1 ) * 10 ";

原理：当参数为null时，COALESCE会返回字段本身的值，条件变为字段=字段，等价于恒真，同时避免了null参数的类型匹配问题

方案3：确保非空参数有默认值 #

对于pageNum这类不允许为null的参数，提前设置默认值（如默认值1），避免因null引发的类型问题；对于其他可选参数，若业务允许，也可设置合理默认值替代null传入。

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