Java Enum to Postgres Enum Conversation

Question

Below is my query

public int updateUser(UserStatus status, UserStatus[] statuses) {
        Query query = entityManager.createQuery("UPDATE UserAccount u SET u.status = :status WHERE u.userId = :userId AND (u.status IN (:statuses))");
        query.setParameter("status", status);
        query.setParameter("userId", userId);
        query.setParameter("statuses", Arrays.asList(statuses));
        return query.executeUpdate();
    }

I am passing status as UserStatus type but still I am getting error

operator does not exist: user_status = character varying

I have column in my entity as

@Column(name = "status")
    @Enumerated(EnumType.STRING)
    private UserStatus status;   // tried using columnTransformer

@ColumnTransformer also I tried its not working for update query. Its working in case of insert.

How can I update enum column.

Thank you.

This question has a few possibilities for you: https://stackoverflow.com/questions/851758/java-enums-jpa-and-postgres-enums-how-do-i-make-them-work-together — egeorge, Mar 17 '23 at 22:35

AndOliver46 · Answer 1 · 2023-03-17T20:41:12.663

0

I think you should try to force convert the status enum like this:

public int updateUser(UserStatus status, UserStatus[] statuses) {
        Query query = entityManager.createQuery("UPDATE UserAccount u SET u.status = :status WHERE u.userId = :userId AND (u.status IN (:statuses))");
        query.setParameter("status", (UserStatus)Enum.Parse(typeof(UserStatus), status));
        query.setParameter("userId", userId);
        query.setParameter("statuses", Arrays.asList(statuses));
        return query.executeUpdate();
    }

You're comparing u.status(VARCHAR) with :status(UserStatus).

edited Mar 17 '23 at 20:41

answered Mar 17 '23 at 20:06

AndOliver46

1
1

Can you give any example I tried name() also which returns string but still shrows error – Mayank Gupta Mar 17 '23 at 20:09
The same error? – AndOliver46 Mar 17 '23 at 20:19
Yes same error it shows. – Mayank Gupta Mar 17 '23 at 20:25
Try the update at the main answer. – AndOliver46 Mar 17 '23 at 20:42
Does not work still throws error – Mayank Gupta Mar 17 '23 at 20:51
Try this query: UPDATE UserAccount u SET u.status::text = :status WHERE u.userId = :userId AND (u.status::text IN (:statuses))" – AndOliver46 Mar 17 '23 at 21:19

score 0 · Answer 2 · answered Aug 16 '23 at 20:15

0

The easiest way forward would be to upgrade to Hibernate 6.2, as that supports database native enum types out of the box.

answered Aug 16 '23 at 20:15

Christian Beikov

15,141
2
32
58

Java Enum to Postgres Enum Conversation

2 Answers2