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.

Solution 1

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://stackoverflow.com/a/7614642/1090474 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.

Solution 2

You can simply get these types via Maven Central using the Hibernate Types dependency:

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

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.

Solution 3

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:

Comments

[leventunver]

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

[Vlad Mihalcea]

That's the spirit!

[Kevin Orriss]

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

[LeO]

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

[Vlad Mihalcea]

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.

[LeO]

@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

[Hans Wouters]

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