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:

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.