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

问题描述：

Spring 3.x，JPA 2.0，Hibernate 4.x，Postgresql 9.x。

使用我想要映射到Postgresql枚举的枚举属性的Hibernate映射类。

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

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.

代码（大量简化）

SQL ：

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映射类：

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：

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

Hibernate xml查询：

Hibernate xml query:

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

疑难解答

查询 id 而不是枚举按预期工作。

没有数据库交互的Java工作正常：

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 在XML中查询，类似于 findByRating 示例。 htmlrel =nofollow noreferrer> Apache的JPA和枚举通过@Enumerated文档也提供了相同的例外。
- 使用select *从psql查询$>
- Hardcoding where direction ='FORWARD'在XML中的查询工作。
```
@Column(name = "direction", nullable = false)
@Enumerated(EnumType.STRING) // tried with and without this line
@Type(type = "full.path.to.HibernateMoveDirectionUserType")
private Direction directionToMove;
```

将Hibernate的 EnumType 映射为由相同问题的 https://stackoverflow.com/a/1604286/1090474 提供较高评分但不接受的答案如上所述，连同：
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")
    })
```
有和没有两个第二个参数，看到 https://stackoverflow.com/a/13241410/1090474

With and without the two second parameters, after seeing https://stackoverflow.com/a/13241410/1090474

JPA 2.1类型转换器不应该必要的，但不是一个选项，因为我现在在JPA 2.0上。

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.

答

HQL

正确地使用混搭并使用限定的属性名称是解决方案的第一部分。

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映射

@Enumerated（EnumType.STRING）仍然不起作用，因此需要使用自定义 UserType 。关键是正确地覆盖 nullSafeSet ，就像这个答案一样 https：// stackoverflow。 / / / / / / / / / 实施从网络。

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

绕行

implements ParameterizedType 没有合作：

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

相反，我宣布这样做：

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

与构造函数：

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;

其他注释

EnhancedUserType 及其想要实现的三种方法

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)
```
  没有任何区别，我可以看到，所以我坚持使用实现UserType 。
  
  didn't make any difference I could see, so I stuck with implements UserType.