13

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;
    }

}
4
  • What type is tb_lock.idUser in the database? Can you try with q.setParameter("idUser", civilRecordSearch.getIdUser().toString())? Commented Oct 31, 2014 at 12:50
  • tb_lock.iduser is UUID In the database. Commented Oct 31, 2014 at 13:20
  • Are you using hibernate? Annotations? Commented Oct 31, 2014 at 13:25
  • 1
    Yes. I'm using hibernate Commented Oct 31, 2014 at 17:41

4 Answers 4

12

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.

Sign up to request clarification or add additional context in comments.

1 Comment

I am getting this specifically when uuid is null and doing this check does not help as well: :param is null OR a.uuid = :param
11

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;
    }
  }

1 Comment

Why on earth is this the solution?
1

The same error can be caused by accidentally passing null as an argument, which is then regarded as a bytea type, making the cast to uuid impossible.

So, before going into the solutions above, I would recommend checking one's business logic and quickly activating the hibernate binding parameters logs in application.yml:

logging:
  level:
    org:
      hibernate:
        type: TRACE

The faulty argument then becomes apparent, as in my case:

....
binding parameter [6] as [VARBINARY] - [null]
...

To also see the sql queries, add:

spring:
  jpa:
    show-sql: true

Comments

-1

Similar response to Robson Silveira.

Pay attention to the last bit of the query string.

public Account getAccount(String name, String password, String tenantId) {
    Query q = em.createNativeQuery(
            "SELECT a.id, a.name, a.password, a.email FROM accounts a WHERE name = :name and password = :password and tenant_fk = CAST(:tenant_fk AS uuid)"
    );
    q.setParameter("name", name);
    q.setParameter("password", password);
    q.setParameter("tenant_fk", tenantId);

    List<Object[]> results = q.getResultList();
    if (results.size() == 0) {
        return null;
    }

    return mapFromObject(results.get(0));
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.