Hibernate ERROR: operator does not exist: integer = bytea No operator matches the given name and arg-CodePudding

While executing the following native query I am getting the above mentioned error. I am using hibernate with postgresql database.

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();

Exception :

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

CodePudding user response：

When passing null for a parameter, Hibernate usually relies on inference in HQL to determine the type of the parameter. Since this is a native query though, Hibernate does not know the type of the parameter and in 5.6 by default chooses to bind such nulls with PreparedStatement#setBytes. The JDBC driver then assumes that this is of the type bytea and a predicate like operator_id = ? will compare an integer against a bytea in case you bind null.

In your particular case, the only way you can make this work is by specifying the type during parameter binding. You can do that through the org.hibernate.query.Query API which allows you to specify an optional third argument in setParameter/setParameterList, which represents the type of the value. Use StandardBasicTypes.INTEGER as argument and the query should work as intended.