問題描述

• Spring 3.x、JPA 2.0、Hibernate 4.x、Postgresql 9.x.
• 使用我想映射到 Postgresql 枚舉的枚舉屬性處理 Hibernate 映射類.

在枚舉列上使用 where 子句進(jìn)行查詢會(huì)引發(fā)異常.

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
}

調(diào)用查詢的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 而不是枚舉查詢按預(yù)期工作.

• 沒有數(shù)據(jù)庫交互的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 中進(jìn)行查詢，類似于 findByRating 示例/jpa-enumerated/README.html" rel="noreferrer">Apache 的 JPA 和 Enums via @Enumerated 文檔給出了同樣的例外.
  • 使用 select * from move where direction = 'LEFT'; 在 psql 中查詢按預(yù)期工作.
  • 硬編碼 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 根據(jù)這個(gè)接受的答案 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 進(jìn)行映射，由評分較高但未接受的答案 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 之后，有沒有第二個(gè)參數(shù)

    With and without the two second parameters, after seeing https://stackoverflow.com/a/13241410/1090474

    JPA 2.1 類型轉(zhuǎn)換器應(yīng)該不是必需的，但無論如何都不是一個(gè)選項(xiàng)，因?yàn)槲椰F(xiàn)在使用的是 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.關(guān)鍵是正確覆蓋 nullSafeSet 就像在這個(gè)答案 https://stackoverflow.com/a/7614642/1090474 和 類似 實(shí)現(xiàn)來自網(wǎng)絡(luò).

    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
    

    使用構(gòu)造函數(shù):

    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及其要實(shí)現(xiàn)的三個(gè)方法

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

      我看不出有什么不同，所以我堅(jiān)持使用 implements UserType.

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

      這篇關(guān)于PostgreSQL 枚舉和 Java 枚舉之間的休眠映射的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

      【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！