Hibernate mapping between PostgreSQL enum and Java enum

Question

Background

• Spring 3.x, JPA 2.0, Hibernate 4.x, Postgresql 9.x.
• Working on a Hibernate mapped class with an enum property that I want to map to a Postgresql enum.

Problem

Querying with a where clause on the enum column throws an exception.

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
... 
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Code (heavily simplified)

SQL:

create type movedirection as enum (
    'FORWARD', 'LEFT'
);

CREATE TABLE move
(
    id serial NOT NULL PRIMARY KEY,
    directiontomove movedirection NOT NULL
);

Hibernate mapped class:

@Entity
@Table(name = "move")
public class Move {

    public enum Direction {
        FORWARD, LEFT;
    }

    @Id
    @Column(name = "id")
    @GeneratedValue(generator = "sequenceGenerator", strategy=GenerationType.SEQUENCE)
    @SequenceGenerator(name = "sequenceGenerator", sequenceName = "move_id_seq")
    private long id;

    @Column(name = "directiontomove", nullable = false)
    @Enumerated(EnumType.STRING)
    private Direction directionToMove;
    ...
    // getters and setters
}

Java that calls the query:

public List<Move> getMoves(Direction directionToMove) {
    return (List<Direction>) sessionFactory.getCurrentSession()
            .getNamedQuery("getAllMoves")
            .setParameter("directionToMove", directionToMove)
            .list();
}

Hibernate xml query:

<query name="getAllMoves">
    <![CDATA[
        select move from Move move
        where directiontomove = :directionToMove
    ]]>
</query>

Troubleshooting

• Querying by id instead of the enum works as expected.

• Java without database interaction works fine:

  public List<Move> getMoves(Direction directionToMove) {
      List<Move> moves = new ArrayList<>();
      Move move1 = new Move();
      move1.setDirection(directionToMove);
      moves.add(move1);
      return moves;
  }
  

• createQuery instead of having the query in XML, similar to the findByRating example in Apache's JPA and Enums via @Enumerated documentation gave the same exception.
• Querying in psql with select * from move where direction = 'LEFT'; works as expected.
• Hardcoding where direction = 'FORWARD' in the query in the XML works.

• .setParameter("direction", direction.name()) does not, same with .setString() and .setText(), exception changes to:

  Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = character varying
  

Attempts at resolution

• Custom UserType as suggested by this accepted answer https://mcmap.net/q/356582/-hibernate-enum-mapping along with:

  @Column(name = "direction", nullable = false)
  @Enumerated(EnumType.STRING) // tried with and without this line
  @Type(type = "full.path.to.HibernateMoveDirectionUserType")
  private Direction directionToMove;
  

• Mapping with Hibernate's EnumType as suggested by a higher rated but not accepted answer https://mcmap.net/q/356582/-hibernate-enum-mapping from the same question as above, along with:

  @Type(type = "org.hibernate.type.EnumType",
      parameters = {
              @Parameter(name  = "enumClass", value = "full.path.to.Move$Direction"),
              @Parameter(name = "type", value = "12"),
              @Parameter(name = "useNamed", value = "true")
      })
  

  With and without the two second parameters, after seeing https://mcmap.net/q/356583/-hibernate-enumtype-instantiation-exception

• Tried annotating the getter and setter like in this answer https://mcmap.net/q/356584/-hibernate-query-using-enum-as-parameter.
• Haven't tried EnumType.ORDINAL because I want to stick with EnumType.STRING, which is less brittle and more flexible.

Other notes

A JPA 2.1 Type Converter shouldn't be necessary, but isn't an option regardless, since I'm on JPA 2.0 for now.

Hardly

This is a very well written question. I wish more questions would clearly state the problem, show relevant code, and show attempts at resolution. Well done. – Cranial

As of 14 Feb 2017, @cslotty's link is dead. – Thorson

I second Todd's comment and will also add a helpful link to medium where I got my solution from: prateek-ashtikar512.medium.com/…. There I didn't need to add Hypersistence Util, but only a package-info.java. – Horney

Answer 1

Update

If you're using Hibernate 5 or 6, Vlad's answer will be more helpful. But if you're stuck on Hibernate 4, read on:

HQL

Aliasing correctly and using the qualified property name was the first part of the solution.

<query name="getAllMoves">
    <![CDATA[
        from Move as move
        where move.directionToMove = :direction
    ]]>
</query>

Hibernate mapping

@Enumerated(EnumType.STRING) still didn't work, so a custom UserType was necessary. The key was to correctly override nullSafeSet like in this answer https://mcmap.net/q/176096/-how-to-map-postgresql-enum-with-jpa-and-hibernate and similar implementations from the web.

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.VARCHAR);
    }
    else {
        st.setObject(index, ((Enum) value).name(), Types.OTHER);
    }
}

Detour

implements ParameterizedType wasn't cooperating:

org.hibernate.MappingException: type is not parameterized: full.path.to.PGEnumUserType

so I wasn't able to annotate the enum property like this:

@Type(type = "full.path.to.PGEnumUserType",
        parameters = {
                @Parameter(name = "enumClass", value = "full.path.to.Move$Direction")
        }
)

Instead, I declared the class like so:

public class PGEnumUserType<E extends Enum<E>> implements UserType

with a constructor:

public PGEnumUserType(Class<E> enumClass) {
    this.enumClass = enumClass;
}

which, unfortunately, means any other enum property similarly mapped will need a class like this:

public class HibernateDirectionUserType extends PGEnumUserType<Direction> {
    public HibernateDirectionUserType() {
        super(Direction.class);
    }
}

Annotation

Annotate the property and you're done.

@Column(name = "directiontomove", nullable = false)
@Type(type = "full.path.to.HibernateDirectionUserType")
private Direction directionToMove;

Other notes

• EnhancedUserType and the three methods it wants implemented

    public String objectToSQLString(Object value)
    public String toXMLString(Object value)
    public String objectToSQLString(Object value)
  

  didn't make any difference I could see, so I stuck with implements UserType.

• Depending on how you're using the class, it might not be strictly necessary to make it postgres-specific by overriding nullSafeGet in the way the two linked solutions did.

• If you're willing to give up the postgres enum, you can make the column text and the original code will work without extra work.

Hardly

So, what is the final code? I see a lot of excerpts but they don't gather in a solid solution in my mind. I need a solution instead of plenty of scattered improvements, a bunch of hyperlinks, that I don't want to follow, ideas and thoughts. – Tier

Answer 2

Hibernate 6

Hibernate 6 has built-in support for PostgreSQL Enum Types via the PostgreSQLEnumJdbcType, which can be mapped like this:

@Entity(name = "Post")
@Table(name = "post")
public static class Post {
 
    @Id
    private Long id;
 
    private String title;
 
    @Enumerated
    @JdbcType(PostgreSQLEnumJdbcType.class)
    private PostStatus status;
 
    //Getters and setters omitted for brevity
}

Here's an example on GitHub that shows how it works with Hibernate 6.

Hibernate 5

If you're using Hibernate 5, you can get this feature via the Hypersistence Util library.

You can get the dependency from Maven Central:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-55</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

Next, you need to annotate the field with the Hibernate @Type annotation, as illustrated in the following examples.

If you're using Hibernate 5, map it like this:

@Entity(name = "Post")
@Table(name = "post")
@TypeDef(
    name = "pgsql_enum",
    typeClass = PostgreSQLEnumType.class
)
public static class Post {
 
    @Id
    private Long id;
 
    private String title;
 
    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "post_status_info")
    @Type(type = "pgsql_enum")
    private PostStatus status;
 
    //Getters and setters omitted for brevity
}

Here's an example on GitHub that shows how it works with Hibernate 5.6.

This mapping assumes you have the post_status_info enum type in PostgreSQL:

CREATE TYPE post_status_info AS ENUM (
    'PENDING', 
    'APPROVED', 
    'SPAM'
)

That's it.

Silicate

As you've said, works like a charm! Should have more upvotes. – Prognathous

That's the spirit! – Silicate

Fantastic, should be accepted as best answer, works great! Upvoted – Subaquatic

@VladMihalcea: I'm little bit confused about your library. Because I'm not really sure if your library now supports the Postgress enum types or not. So, IFFF I add the library to my project do I need the code or not? The reason for the confusion is that in your articles you explain how to set it up, but still leaves me doubts about functionality. – Wilbourn

Yes, it does support it. This answer shows how to write a type that supports PostgreSQL Enum, and, it's exactly what the hibernate-types library dies in fact. Now, you can just use the type written by me or you can write it yourself. It's as simple as that. – Silicate

@VladMihalcea: Somehow I didn't figure out how to use it properly with your package :-/ The other problem I'm facing is that for production we have Postgres but for testing we have h2. Is there a way to use them both for the same Java enum? Don't know if there is better place for a followup – Wilbourn

I'd just like to point everyone who it trying to do this in a native query to this article: vladmihalcea.com/…. I was looking for the way to cast this properly everywhere. Thanks @VladMihalcea – Kordofan

Any way to support hbm2ddl? – Grondin

how about Hibernate 6 ? – Coridon

org.hibernate.type.EnumType is deprecated and marked for removal. – Wenz

I am getting Hibernate validation errors reported, although it works. – Relieve

Answer 3

Hibernate < 6.3 version

@Enumerated(EnumType.STRING)
@ColumnTransformer(write = "?::yours_enum_type")
private YoursEnumType enumType;

Hibernate 6.3

Looks like it's supported out of the box since 6.3.0. https://hibernate.atlassian.net/browse/HHH-16125

Sonatina

Answer 4

As said in 8.7.3. Type Safety of Postgres Docs:

If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:

so if you want a quick and simple workaround, do like this:

<query name="getAllMoves">
<![CDATA[
    select move from Move move
    where cast(directiontomove as text) = cast(:directionToMove as text)
]]>
</query>

Unfortunately, you can't do it simply with two colons:

Kemppe

Answer 5

Let me start off saying I was able to do this using Hibernate 4.3.x and Postgres 9.x.

I based my solution off something similar to what you did. I believe if you combine

@Type(type = "org.hibernate.type.EnumType",
parameters = {
        @Parameter(name  = "enumClass", value = "full.path.to.Move$Direction"),
        @Parameter(name = "type", value = "12"),
        @Parameter(name = "useNamed", value = "true")
})

and this

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
  if (value == null) {
    st.setNull(index, Types.VARCHAR);
  }
  else {
    st.setObject(index, ((Enum) value).name(), Types.OTHER);
  }
}

You should be able to get something along the lines of this, without having to make either above change.

@Type(type = "org.hibernate.type.EnumType",
parameters = {
        @Parameter(name  = "enumClass", value = "full.path.to.Move$Direction"),
        @Parameter(name = "type", value = "1111"),
        @Parameter(name = "useNamed", value = "true")
})

I believe that this works since you're essentially telling Hibernate to map the enum to a type of other (Types.OTHER == 1111). It may be a slightly brittle solution since the value of Types.OTHER could change. However, this would provide significantly less code overall.

Roderick

Answer 6

I have another approach with a persistence converter:

import javax.persistence.Column;
import javax.persistence.Convert;

@Column(name = "direction", nullable = false)
@Convert(converter = DirectionConverter.class)
private Direction directionToMove;

This is a converter definition:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter
public class DirectionConverter implements AttributeConverter<Direction, String> {
    @Override
    public String convertToDatabaseColumn(Direction direction) {
        return direction.name();
    }

    @Override
    public Direction convertToEntityAttribute(String string) {
        return Diretion.valueOf(string);
    }
}

It does not resolve mapping to psql enum type, but it can simulate @Enumerated(EnumType.STRING) or @Enumerated(EnumType.ORDINAL) in a good way.

For ordinal use direction.ordinal() and Direction.values()[number].

Bouley