PostgreSQL 枚举和 Java 枚举之间的 Hibernate 映射
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27804069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate mapping between PostgreSQL enum and Java enum
提问by Kenny Linsky
Background
背景
- Spring 3.x, JPA 2.0, Hibernate 4.x, Postgresql 9.x.
- Working on a Hibernate mapped class with an enum property that I want to map to a Postgresql enum.
- Spring 3.x、JPA 2.0、Hibernate 4.x、Postgresql 9.x。
- 使用我想映射到 Postgresql 枚举的枚举属性处理 Hibernate 映射类。
Problem
问题
Querying with a where clause on the enum column throws an exception.
使用枚举列上的 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.
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 mapped class:
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 that calls the query:
调用查询的Java：
public List<Move> getMoves(Direction directionToMove) {
return (List<Direction>) sessionFactory.getCurrentSession()
.getNamedQuery("getAllMoves")
.setParameter("directionToMove", directionToMove)
.list();
}
Hibernate xml query:
休眠xml查询：
<query name="getAllMoves">
<![CDATA[
select move from Move move
where directiontomove = :directionToMove
]]>
</query>
Troubleshooting
故障排除
- Querying by
idinstead 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; }createQueryinstead of having the query in XML, similar to thefindByRatingexample in Apache's JPA and Enums via @Enumerated documentationgave 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
- 查询按
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 的 JPA 和 Enums 中的findByRating示例，通过 @Enumerated 文档给出了相同的例外。- 在 psql 中查询
select * from move where direction = 'LEFT';按预期工作。 where direction = 'FORWARD'XML 查询中的硬编码有效。.setParameter("direction", direction.name())与.setString()and 不同.setText()，异常更改为：Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: movedirection = character varying
Attempts at resolution
尝试解决
Custom
UserTypeas suggested by this accepted answer https://stackoverflow.com/a/1594020/1090474along 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
EnumTypeas suggested by a higher rated but not accepted answer https://stackoverflow.com/a/1604286/1090474from 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://stackoverflow.com/a/13241410/1090474
- Tried annotating the getter and setter like in this answer https://stackoverflow.com/a/20252215/1090474.
- Haven't tried
EnumType.ORDINALbecause I want to stick withEnumType.STRING, which is less brittle and more flexible.
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;与上述相同问题
EnumType的更高评级但未接受的答案https://stackoverflow.com/a/1604286/1090474建议的使用 Hibernate 映射，以及：@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/20252215/1090474 中注释 getter 和 setter 。
- 还没有尝试过，
EnumType.ORDINAL因为我想坚持使用EnumType.STRING，它不那么脆弱，更灵活。
Other notes
其他注意事项
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.
JPA 2.1 类型转换器不是必需的，但无论如何都不是一个选项，因为我现在使用的是 JPA 2.0。
采纳答案by Kenny Linsky
HQL
高品质
Aliasing correctly and using the qualified property namewas the first part of the solution.
正确别名和使用限定的属性名称是解决方案的第一部分。
<query name="getAllMoves">
<![CDATA[
from Move as move
where move.directionToMove = :direction
]]>
</query>
Hibernate mapping
休眠映射
@Enumerated(EnumType.STRING)still didn't work, so a custom UserTypewas necessary. The key was to correctly override nullSafeSetlike in this answer https://stackoverflow.com/a/7614642/1090474and similarimplementationsfrom the web.
@Enumerated(EnumType.STRING)仍然不起作用，因此需要自定义UserType。关键是正确覆盖nullSafeSet像这个答案https://stackoverflow.com/a/7614642/1090474和来自网络的类似实现。
@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);
}
}
Detour
车辆改道
implements ParameterizedTypewasn't cooperating:
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:
所以我无法像这样注释 enum 属性：
@Type(type = "full.path.to.PGEnumUserType",
parameters = {
@Parameter(name = "enumClass", value = "full.path.to.Move$Direction")
}
)
Instead, I declared the class like so:
相反，我像这样声明了这个类：
public class PGEnumUserType<E extends Enum<E>> implements UserType
with a constructor:
使用构造函数：
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;
Other notes
其他注意事项
EnhancedUserTypeand the three methods it wants implementedpublic String objectToSQLString(Object value) public String toXMLString(Object value) public String objectToSQLString(Object value)didn't make any difference I could see, so I stuck with
implements UserType.- Depending on how you're using the class, it might not be strictly necessary to make it postgres-specific by overriding
nullSafeGetin the way the two linked solutions did. - If you're willing to give up the postgres enum, you can make the column
textand the original code will work without extra work.
EnhancedUserType以及它想要实现的三种方法public String objectToSQLString(Object value) public String toXMLString(Object value) public String objectToSQLString(Object value)我看不出有什么区别，所以我坚持使用
implements UserType.- 根据您使用该类
nullSafeGet的方式，通过覆盖两个链接解决方案的方式来使其特定于postgres 可能不是绝对必要的。 - 如果您愿意放弃 postgres 枚举，您可以制作该列
text，原始代码无需额外工作即可运行。
回答by Vlad Mihalcea
You don't have to create all the following Hibernate Types manually. You can simply get them via Maven Central using the following dependency:
您不必手动创建以下所有休眠类型。您可以使用以下依赖项通过 Maven Central 简单地获取它们：
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
For more info, check out the hibernate-types open-source project.
有关更多信息，请查看hibernate-types 开源项目。
As I explained in this article, if you easily map Java Enum to a PostgreSQL Enum column type using the following custom Type:
正如我在本文中所解释的，如果您使用以下自定义类型轻松地将 Java Enum 映射到 PostgreSQL Enum 列类型：
public class PostgreSQLEnumType extends org.hibernate.type.EnumType {
public void nullSafeSet(
PreparedStatement st,
Object value,
int index,
SharedSessionContractImplementor session)
throws HibernateException, SQLException {
if(value == null) {
st.setNull( index, Types.OTHER );
}
else {
st.setObject(
index,
value.toString(),
Types.OTHER
);
}
}
}
To use it, you need to annotate the field with the Hibernate @Typeannotation as illustrated in the following example:
要使用它，您需要使用 Hibernate@Type注释来注释该字段，如下例所示：
@Entity(name = "Post")
@Table(name = "post")
@TypeDef(
name = "pgsql_enum",
typeClass = PostgreSQLEnumType.class
)
public static class Post {
@Id
private Long id;
private String title;
@Enumerated(EnumType.STRING)
@Column(columnDefinition = "post_status_info")
@Type( type = "pgsql_enum" )
private PostStatus status;
//Getters and setters omitted for brevity
}
This mapping assumes you have the post_status_infoenum type in PostgreSQL:
此映射假设您post_status_info在 PostgreSQL 中有枚举类型：
CREATE TYPE post_status_info AS ENUM (
'PENDING',
'APPROVED',
'SPAM'
)
That's it, it works like a charm. Here's a test on GitHub that proves it.
回答by bdshadow
As said in 8.7.3. Type Safety of Postgres Docs:
If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:
如果您真的需要做类似的事情，您可以编写自定义运算符或向查询添加显式强制转换：
so if you want a quick and simple workaround, do like this:
所以如果你想要一个快速简单的解决方法，请这样做：
<query name="getAllMoves">
<![CDATA[
select move from Move move
where cast(directiontomove as text) = cast(:directionToMove as text)
]]>
</query>
Unfortunately, you can't do it simply with two colons:
不幸的是，你不能简单地用两个冒号来做到这一点：
回答by lew
Let me start off saying I was able to do this using Hibernate 4.3.x and Postgres 9.x.
首先让我说我能够使用 Hibernate 4.3.x 和 Postgres 9.x 来做到这一点。
I based my solution off something similar to what you did. I believe if you combine
我的解决方案基于与您所做的类似的事情。我相信如果你结合
@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")
})
and this
还有这个
@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);
}
}
You should be able to get something along the lines of this, without having to make either above change.
您应该能够获得与此类似的东西，而无需进行上述任何更改。
@Type(type = "org.hibernate.type.EnumType",
parameters = {
@Parameter(name = "enumClass", value = "full.path.to.Move$Direction"),
@Parameter(name = "type", value = "1111"),
@Parameter(name = "useNamed", value = "true")
})
I believe that this works since you're essentially telling Hibernate to map the enum to a type of other (Types.OTHER == 1111). It may be a slightly brittle solution since the value of Types.OTHERcould change. However, this would provide significantly less code overall.
我相信这是有效的，因为您实际上是在告诉 Hibernate 将枚举映射到其他类型 ( Types.OTHER == 1111)。这可能是一个稍微脆弱的解决方案，因为 的值Types.OTHER可能会改变。但是，这将提供明显更少的代码。
回答by Marko Novakovic
I have another approach with a persistence converter:
我有另一种使用持久性转换器的方法：
import javax.persistence.Convert;
@Column(name = "direction", nullable = false)
@Converter(converter = DirectionConverter.class)
private Direction directionToMove;
This is a converter definition:
这是一个转换器定义：
import javax.persistence.Converter;
@Converter
public class DirectionConverter implements AttributeConverter<Direction, String> {
@Override
public String convertToDatabaseColumn(Direction direction) {
return direction.name();
}
@Override
public Direction convertToEntityAttribute(String string) {
return Diretion.valueOf(string);
}
}
It does not resolve mapping to psql enum type, but it can simulate @Enumerated(EnumType.STRING) or @Enumerated(EnumType.ORDINAL) in a good way.
它不会解析到 psql 枚举类型的映射，但它可以很好地模拟 @Enumerated(EnumType.STRING) 或 @Enumerated(EnumType.ORDINAL)。
For ordinal use direction.ordinal() and Direction.values()[number].
对于序数使用 direction.ordinal() 和 Direction.values()[number]。
