PostgreSQL 枚举和 Java 枚举之间的 Hibernate 映射 [英] Hibernate mapping between PostgreSQL enum and Java enum

问题描述

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

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 而不是 enum 查询按预期工作.

• 没有数据库交互的 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 中进行查询，类似于 Apache 的 JPA 和 Enums 通过 @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")
            }
    )
    

    相反，我像这样声明了类:

    Instead, I declared the class like so:

    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)
        

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

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

        这篇关于PostgreSQL 枚举和 Java 枚举之间的 Hibernate 映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！