PostgreSQL枚举和Java枚举之间的Hibernate映射

Question

• Spring 3.x、JPA 2.0、Hibernate 4.x、Postgresql 9.x.
• 使用希望映射到Postgresql枚举的枚举属性处理Hibernate映射类。

使用枚举列上的where子句进行查询会引发异常。

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.

SQL：

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

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

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

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

Hibernate xml查询：

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

>

按ID而不是按枚举查询工作正常。

没有数据库交互的Java工作良好：

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

.setParameter（“direction”,direction.name())不是，与.setString()和.setText()相同，异常更改为：

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

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

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

在查看https://stackoverflow.com/A/13241410/1090474之后，可以使用和不使用第二个参数

JPA2.1类型的转换器不应该是必需的，但无论如何也不是一个选项，因为我现在正在使用JPA2.0。

Answer 1

您可以通过Maven Central使用Hibernate类型依赖关系获得这些类型：

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

如果使用以下自定义类型轻松地将Java枚举映射到PostgreSQL枚举列类型：

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 
            );
        }
    }
}

要使用它，您需要使用Hibernate@type注释对字段进行注释，如下例所示：

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

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