"operator does not exist: uuid = bytea" Java with Postgres

Question

I have a problem referring to this query. On Postgres, this query executes without errors. On JAVA, it throws the following exception:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
cause
org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea
  Note: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 404

What I do?

My Method:

public List<CivilRecord> dashboardSearch(CivilRecordSearch civilRecordSearch)
    throws MessageException {
    SearchValidation.validateDashboardSearch(civilRecordSearch);
    List<CivilRecord> l = new ArrayList<>();
    try {
        StringBuilder query = new StringBuilder();
        // query.append("select
        // c.id_civil_record\\:\\:text,c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority
        // ");
        query.append("select c.id_civil_record,c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority ");
        query.append("from sc_civil.tb_civil_record c ");
        query.append("inner join sc_civil.tb_workflow_record w ");
        query.append("on w.id_civil_record = c.id_civil_record ");
        query.append("left join sc_civil.tb_lock l ");
        query.append("on l.id_record = c.id_civil_record ");
        query.append("where c.id_site = :idSite ");

        if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
            query.append("and c.bl_priority = :priority ");
        query.append("and c.bl_canceled = :canceled ");
        query.append("and w.id_type_workflow = :idTypeWorkflow ");
        query.append("and w.id_type_status_workflow = :idTypeStatusWorkflow ");

        query.append("and (l is null or l.id_user = :idUser) ");

        if (!StringUtils.isEmpty(civilRecordSearch.getName()))
            query.append("and c.tx_name ilike :name ");
        if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
            query.append("and c.nm_rg like :rg ");

        if (civilRecordSearch.getRequestNumber() != null)
            query.append("and c.nm_request = :request ");

        query.append("order by c.bl_priority desc, c.dt_register ");

        Query q = em.createNativeQuery(query.toString());
        q.setParameter("idSite", civilRecordSearch.getSite().getId());
        if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
            q.setParameter("priority", false);
        q.setParameter("idTypeWorkflow", civilRecordSearch.getTypeworkflow().getId());
        q.setParameter("idTypeStatusWorkflow", civilRecordSearch.getTypestatusworkflow().getId());
        q.setParameter("idUser", civilRecordSearch.getIdUser());
        q.setParameter("canceled", false);
        if (!StringUtils.isEmpty(civilRecordSearch.getName()))
            q.setParameter("name", "%" + civilRecordSearch.getName() + "%");
        if (civilRecordSearch.getRequestNumber() != null)
            q.setParameter("request", civilRecordSearch.getRequestNumber());
        if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
            q.setParameter("rg", civilRecordSearch.getRg());

        q.setMaxResults(maxResult);

        List<Object []> lo = q.getResultList();
        em.clear();
        for (Object [] o : lo) {
            CivilRecord c = new CivilRecord();
            c.setIdCivilRecord(UUID.fromString((String) o[0]));
            c.setRequest((Long) o[1]);
            c.setRg((String) o[2]);
            c.setName((String) o[3]);
            c.setWorkflowRecords(findStatus(c.getIdCivilRecord()));
            l.add(c);
        }
        return l;
    }
    catch (Exception e) {
        log.severe(e.getMessage());
        throw e;
    }
}

My Class CivilRecordSearch:

import java.io.Serializable;
import java.util.UUID;

public class CivilRecordSearch implements Serializable {

    private static final long serialVersionUID = 1701325902333490974L;

    // site, prioridade, tipo wf e status wf

    private Site site;
    private Boolean priority;
    private TypeWorkflow typeworkflow;
    private TypeStatusWorkflow typestatusworkflow;
    private Integer amount;
    private UUID idUser;
    private String name;
    private String rg;
    private Long requestNumber;

    public Site getSite() {
        return site;
    }

    public void setSite(Site site) {
        this.site = site;
    }

    public Boolean getPriority() {
        return priority;
    }

    public void setPriority(Boolean priority) {
        this.priority = priority;
    }

    public TypeWorkflow getTypeworkflow() {
        return typeworkflow;
    }

    public void setTypeworkflow(TypeWorkflow typeworkflow) {
        this.typeworkflow = typeworkflow;
    }

    public TypeStatusWorkflow getTypestatusworkflow() {
        return typestatusworkflow;
    }

    public void setTypeStatusWorkflow(TypeStatusWorkflow typestatusworkflow) {
        this.typestatusworkflow = typestatusworkflow;
    }

    public static long getSerialversionuid() {
        return serialVersionUID;
    }

    public Integer getAmount() {
        return amount;
    }

    public void setAmount(Integer amount) {
        this.amount = amount;
    }

    public UUID getIdUser() {
        return idUser;
    }

    public void setIdUser(UUID idUser) {
        this.idUser = idUser;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRg() {
        return rg;
    }

    public void setRg(String rg) {
        this.rg = rg;
    }

    public Long getRequestNumber() {
        return requestNumber;
    }

    public void setRequestNumber(Long requestNumber) {
        this.requestNumber = requestNumber;
    }

}

Answer 1

Problem:

Hibernate should map the java UUID type to the postgress uuid type. However, if hibernate does not know how to map this, it will just try to serialize the object, resulting in a byte[]. Of course, this just moves the issue to the database level. Values of the uuid postgress type cannot just be compared with byte array type.

PSQLException: ERROR: operator does not exist: uuid = bytea

Possible cause:

I encountered this issue when migrating from Spring Boot 1.x to Spring Boot 2.3.0. In Spring Boot 1.x it was sufficient for me to mark my id fields with @Id and to make them of java type UUID.

Quick and dirty solutions:

A possible solution, is to explicitly state the PSQL type for the id field.

 @Type(type="org.hibernate.type.PostgresUUIDType")
 @Id
 private UUID id;

A better solution, would be to define a system-wide replacement. You could put this declaration on any class or on a package. Defining it just once somewhere impacts all declarations of UUID actually.

 @TypeDef(name="postgres-uuid",
          defaultForType = UUID.class,
          typeClass = PostgresUUIDType.class)

Real solution:

Take a look in your log file, and you may see something like this. Double check the version of this dialect, and see if it matches with the one which you defined in your property files:

Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL81Dialect

In that case, be aware that the following property is outdated:

hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect

Some of the hibernate properties now need to have a spring.jpa.properties prefix. So, in this case, the new property path should be spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect.

Which is the point where everything starts to make sense. This dialect does all required type definitions for you.

Answer 2

I solve my problem with this form: I utilized the command CAST for my UUID fields

  public List<CivilRecord> dashboardSearch(CivilRecordSearch civilRecordSearch)
      throws MessageException {
    SearchValidation.validateDashboardSearch(civilRecordSearch);
    List<CivilRecord> l = new ArrayList<>();
    try {
      StringBuilder query = new StringBuilder();
      //query.append("select c.id_civil_record\\:\\:text,c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority ");
      query.append("select CAST(c.id_civil_record as text),c.nm_request,c.nm_rg,c.tx_name,c.dt_register,c.bl_priority ");
      query.append("from sc_civil.tb_civil_record c ");
      query.append("inner join sc_civil.tb_workflow_record w ");
      query.append("on w.id_civil_record = c.id_civil_record ");
      query.append("left join sc_civil.tb_lock l ");
      query.append("on l.id_record = c.id_civil_record ");
      query.append("where c.id_site = :idSite ");

      if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
        query.append("and c.bl_priority = :priority ");
      query.append("and c.bl_canceled = :canceled ");
      query.append("and w.id_type_workflow = :idTypeWorkflow ");
      query.append("and w.id_type_status_workflow = :idTypeStatusWorkflow ");

      query.append("and (l is null or l.id_user = CAST(:idUser AS uuid)) ");

      if (!StringUtils.isEmpty(civilRecordSearch.getName()))
        query.append("and c.tx_name ilike :name ");
      if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
        query.append("and c.nm_rg like :rg ");

      if (civilRecordSearch.getRequestNumber() != null)
        query.append("and c.nm_request = :request ");

      query.append("order by c.bl_priority desc, c.dt_register ");

      Query q = em.createNativeQuery(query.toString());
      q.setParameter("idSite", civilRecordSearch.getSite().getId());
      if (civilRecordSearch.getPriority() == null || civilRecordSearch.getPriority().equals(false))
        q.setParameter("priority", false);
      q.setParameter("idTypeWorkflow", civilRecordSearch.getTypeworkflow().getId());
      q.setParameter("idTypeStatusWorkflow", civilRecordSearch.getTypestatusworkflow().getId());
      q.setParameter("idUser", civilRecordSearch.getIdUser().toString());
      q.setParameter("canceled", false);
      if (!StringUtils.isEmpty(civilRecordSearch.getName()))
        q.setParameter("name","%" + civilRecordSearch.getName() + "%");
      if (civilRecordSearch.getRequestNumber() != null)
        q.setParameter("request", civilRecordSearch.getRequestNumber());
      if (!StringUtils.isEmpty(civilRecordSearch.getRg()))
        q.setParameter("rg", civilRecordSearch.getRg());

      q.setMaxResults(maxResult);
      List<Object[]> lo = q.getResultList();
      em.clear();
      for(Object[] o : lo){
          CivilRecord c = new CivilRecord();
          c.setIdCivilRecord(UUID.fromString((String)o[0]));
          c.setRequest(((BigInteger)o[1]).longValue());
          c.setRg((String)o[2]);
          c.setName((String)o[3]);
          c.setRegister((Date)o[4]);
          c.setPriority(TypeYesNo.getByKey(((Boolean)o[5]).booleanValue()));
          c.setWorkflowRecords(findStatus(c.getIdCivilRecord()));
          l.add(c);
      }
      return l;
    } catch (Exception e) {
      log.severe(e.getMessage());
      throw e;
    }
  }