CODEWITHSUNDEEP
javapostgresqlenumsannotationshibernate-mapping
Kenny Linsky
Kenny Linsky

Reputation: 1776

Hibernate mapping between PostgreSQL enum and Java enum

Background

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

Attempts at resolution

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.

Upvotes: 49

Views: 39143

Answers (6)

Kenny Linsky
Kenny Linsky

Reputation: 1776

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://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.

Upvotes: 8

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154070

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.

Upvotes: 75

vvserdiuk
vvserdiuk

Reputation: 69

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

Upvotes: 3

Marko Novakovic
Marko Novakovic

Reputation: 480

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

Upvotes: 0

lew
lew

Reputation: 54

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.

Upvotes: 0

Dmitrii Bocharov
Dmitrii Bocharov

Reputation: 935

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:

Upvotes: 1

Related Questions