背景
- 使用Spring 3.x、JPA 2.0、Hibernate 4.x和Postgresql 9.x。
- 正在处理一个具有枚举属性的Hibernate映射类,我想将其映射到Postgresql枚举。
问题
在枚举列上使用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>
故障排除
- 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
instead of having the query in XML, similar to thefindByRating
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
解决方案尝试
Custom
UserType
as suggested by this accepted answer https://dev59.com/knI-5IYBdhLWcg3w9thw#1594020 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;
Mapping with Hibernate's
EnumType
as suggested by a higher rated but not accepted answer https://dev59.com/knI-5IYBdhLWcg3w9thw#1604286 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") })
With and without the two second parameters, after seeing https://dev59.com/eWrWa4cB1Zd3GeqP8C88#13241410
- Tried annotating the getter and setter like in this answer https://dev59.com/SXnZa4cB1Zd3GeqPsqRH#20252215.
- Haven't tried
EnumType.ORDINAL
because I want to stick withEnumType.STRING
, which is less brittle and more flexible.
其他注意事项
JPA 2.1类型转换器不是必需的,但由于我现在使用的是JPA 2.0,因此无法选择。
package-info.java
文件。 - jmizv