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://stackoverflow.com/a/1594020/1090474 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://stackoverflow.com/a/1604286/1090474 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://stackoverflow.com/a/13241410/1090474

• Tried annotating the getter and setter like in this answer https://stackoverflow.com/a/20252215/1090474.
• 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.

Answer 1

You don't have to create all the following Hibernate Types manually. You can simply get them via Maven Central using the following dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

For more info, check out the hibernate-types open-source project.

As I explained in this article, if you easily map Java Enum to a PostgreSQL Enum column type using the following custom Type:

public class PostgreSQLEnumType extends org.hibernate.type.EnumType {

    public void nullSafeSet(
            PreparedStatement st, 
            Object value, 
            int index, 
            SharedSessionContractImplementor session) 
        throws HibernateException, SQLException {
        if(value == null) {
            st.setNull( index, Types.OTHER );
        }
        else {
            st.setObject( 
                index, 
                value.toString(), 
                Types.OTHER 
            );
        }
    }
}

To use it, you need to annotate the field with the Hibernate @Type annotation as illustrated in the following example:

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

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, it works like a charm. Here's a test on GitHub that proves it.