PostgreSQL 枚举和 Java 枚举之间的休眠映射

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:

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
}

调用查询的Java:

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

休眠 xml 查询:

<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 示例/jpa-enumerated/README.html" rel="noreferrer">Apache 的 JPA 和 Enums via @Enumerated 文档给出了同样的例外.
  • 使用 select * from move where direction = 'LEFT'; 在 psql 中查询按预期工作.
  • 硬编码 where direction = 'FORWARD' 在 XML 的查询中有效.

  • .setParameter("direction", direction.name()) 没有，与 .setString() 和 .setText() 相同，异常更改为:

  • 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
    

  • 自定义 UserType 根据这个接受的答案 https://stackoverflow.com/a/1594020/1090474 以及:

  • 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;
  

  • 使用 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>
    

    休眠映射

    @Enumerated(EnumType.STRING) 仍然不起作用，因此需要自定义 UserType.关键是正确覆盖 nullSafeSet 就像在这个答案 https://stackoverflow.com/a/7614642/1090474 和 类似 实现来自网络.

    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及其要实现的三个方法

      public String objectToSQLString(Object value)
      public String toXMLString(Object value)
      public String objectToSQLString(Object value)
      

      我看不出有什么不同，所以我坚持使用 implements UserType.

      didn't make any difference I could see, so I stuck with implements UserType.