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

I have a problem linking to this request. Postgres runs this request without error. In JAVA, this excludes the following:

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 am I doing?

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 CivilRecordSearch class:

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

}

      

+3


source to share


2 answers


I solve my problem with this form: I have used CAST command 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;
    }
  }

      

+6


source


Similar answer by Robson to 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));
}

      

0


source







All Articles