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

背景

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

Hibernate映射类：

@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查询：

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

> createQuery而不是在XML中查询,类似于Apache’s JPA and Enums via @Enumerated documentation中的findByRating示例给出了同样的异常.
>在psql中查询select * from move where direction =’LEFT’;按预期工作.
>在XML中查询中的direction =’FORWARD’的Hardcoding工作.
> .setParameter(“direction”,direction.name())不同于.setString()和.setText(),异常更改为：

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

尝试解决

>自定义UserType,由本接受的答案https://stackoverflow.com/a/1594020/1090474以及

@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,以及：

@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
>尝试在这个答案https://stackoverflow.com/a/20252215/1090474中注释getter和setter.
>没有尝试过EnumType.ORDINAL,因为我想坚持使用EnumType.STRING,这是不那么脆弱和更灵活.

其他笔记

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

HQL

混淆正确并使用合格属性名称是解决方案的第一部分.

<query name="getAllMoves">
    <![CDATA[
        from Move as move
        where move.directionToMove = :direction
    ]]>
</query>

Hibernate映射

@Enumerated(EnumType.STRING)仍然不起作用,因此需要自定义UserType.关键是正确地覆盖nullSafeSet,就像在这个答案https://stackoverflow.com/a/7614642/1090474和similar implementations中一样.

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

車輛改道

实现ParameterizedType没有合作：

org.hibernate.MappingException: type is not parameterized: full.path.to.PGEnumUserType

所以我不能像这样注释枚举属性：

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

不幸的是,这意味着类似映射的任何其他枚举属性将需要类似这样的类：

public class HibernateDirectionUserType extends PGEnumUserType<Direction> {
    public HibernateDirectionUserType() {
        super(Direction.class);
    }
}

注解

注释财产,你完成了.

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

没有任何区别我可以看到,所以我坚持实现UserType.>根据您使用该类的方式,通过以两个链接解决方案的方式覆盖nullSafeGet,可能并不一定要使其具有postgres特性.>如果您愿意放弃postgres枚举,您可以使列文本和原始代码无需额外的工作.

原文链接：https://www.f2er.com/postgresql/192531.html

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

猜你在找的Postgre SQL相关文章