4

I am having an issue updating enum fields in my postgres database from enums in my java entity using hibernate leading to the error in the title.

My database looks like this 

\dT fulfillment_status
            List of data types
 Schema |        Name        | Description 
--------+--------------------+-------------
 public | fulfillment_status | 
(1 row)

This sets the type for a specific column fulfillment_status i.e.

       Column           |              Type              | Collation | Nullable |               Default                
----------------------------+--------------------------------+-----------+----------+--------------------------------------

fulfillment_status         | fulfillment_status             |           | not null |

Relevant sections of the java entity class

@Entity
@Table(name = "payments", uniqueConstraints = { @UniqueConstraint(columnNames = {"id"})})
public class Payment {
 private FulfillmentStatus fulfillmentStatus;

public enum FulfillmentStatus {
        PENDING, FULFILLED, FAILED, ABORTED
    }

@Enumerated(EnumType.STRING)
    @Column(name = "fulfillment_status", nullable = false, columnDefinition = "enum('PENDING', 'FULFILLED', 'FAILED', 'ABORTED')")
    public FulfillmentStatus getFulfillmentStatus() {
        return fulfillmentStatus;
    }

    public void setFulfillmentStatus(FulfillmentStatus fulfillmentStatus) {
        this.fulfillmentStatus = fulfillmentStatus;
    }

And the code used to update the class

        Session session = sessionFactory.getCurrentSession();
        session.beginTransaction();
        CriteriaBuilder builder = session.getCriteriaBuilder();
        CriteriaUpdate<Payment> update = 
        builder.createCriteriaUpdate(Payment.class);
        Root root = update.from(Payment.class);
        update.set(root.get("fulfillmentStatus"), 
        Payment.FulfillmentStatus.FULFILLED);
        update.where(builder.equal(root.get("requestID"), UUID.fromString(requestID)));
        session.createQuery(update).executeUpdate();
        session.getTransaction().commit();
        session.close();

This generates the error 

Hibernate: 
    update
        payments 
    set
        fulfillment_status=?
2018-05-02 08:54:11 WARN  SqlExceptionHelper:129 - SQL Error: 0, SQLState: 42804
2018-05-02 08:54:11 ERROR SqlExceptionHelper:131 - ERROR: column "fulfillment_status" is of type fulfillment_status but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 40

The issue here seems how to use the CriteriaUpdate to map java enum types to postgres enum types ... hopefully without creating additional mapping classes.

reayn3
  • 363
  • 5
  • 16
  • Possible duplicate of [Trying to map postgres enum to Hibernate/JPA pojo](https://stackoverflow.com/questions/7603500/trying-to-map-postgres-enum-to-hibernate-jpa-pojo) – Łukasz Kamiński May 02 '18 at 07:17
  • could also be caused by not having cast from enum to varchar! – Gaurav Sep 02 '21 at 20:36

1 Answers1

1

this solved my probleme :

@Column(name = "fulfillment_status")
@ColumnTransformer(write="?::fulfillment_status")
public FulfillmentStatus getFulfillmentStatus() {
    return fulfillmentStatus;
}
T. Mehdi
  • 59
  • 5