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 ：

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中的查询工作。
  
  

  • .setParameter（direction，direction.name（） code>不与 .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>
    

    
    
    

    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.

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