使用 JPA 在 PostgreSQL 中持久化 UUID [英] Persisting UUID in PostgreSQL using JPA
问题描述
我正在尝试在 PostgreSQL 中保留一个使用 UUID 作为主键的实体.我试过将它作为一个普通的 UUID 持久化:
I'm trying to persist an entity in PostgreSQL that uses UUID as primary key. I've tried persisting it as a plain UUID:
@Id
@Column(name = "customer_id")
private UUID id;
有了上面的，我得到这个错误:
With the above, I get this error:
ERROR: column "customer_id" is of type uuid but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 137
我也尝试将 UUID 持久化为 byte[] 无济于事:
I also tried persisting the UUID as byte[] to no avail:
@Transient
private UUID id;
@Id
@Column(name = "customer_id")
@Access(AccessType.PROPERTY)
@Lob
protected byte[] getRowId() {
return id.toString().getBytes();
}
protected void setRowId(byte[] rowId) {
id = UUID.fromString(new String(rowId));
}
如果我删除@Lob，我得到的错误与上面发布的错误相同.但是应用@Lob 后，错误会略微更改为:
If I remove @Lob, the error I get is the same as the one posted above. But with @Lob applied, the error changes slightly to:
ERROR: column "customer_id" is of type uuid but expression is of type bigint
Hint: You will need to rewrite or cast the expression.
Position: 137
不能做这么简单的事情，我感觉非常糟糕！
I'm feeling extremely bad being unable to do something as simple as this!
我在 PostgreSQL 9.1 中使用 Hibernate 4.1.3.Final.
I'm using Hibernate 4.1.3.Final with PostgreSQL 9.1.
我已经或多或少地看到许多关于相同问题的问题，但它们都是旧的，似乎没有一个直接的答案.
I've seen numerous questions on SO more or less with the same issue but they are all old and none seems to have a straight forward answer.
我想以标准的方式实现这一点，而不是诉诸丑陋的黑客.但是，如果这只能通过(丑陋的)黑客来实现，那么这可能就是我要做的.但是，我不想将 UUID 作为 varchar 存储在数据库中，因为这对性能不利.另外，如果可能的话，我不想在我的代码中引入 Hibernate 依赖项.
I'd like to achieve this in a standard way without resorting to ugly hacks. But if this can be achieved only though (ugly) hacks, then may be that's what I'll do. However, I don't want to store the UUID as a varchar in the database as that's not good for performance. Also, I'd want to not introduce a Hibernate dependency in my code if possible.
任何帮助将不胜感激.
更新 1 (2012-07-03 12:15 pm)
UPDATE 1 (2012-07-03 12:15 pm)
好吧，好吧……我用 JTDS 驱动程序(v1.1 版本)在 SQL Server 2008 R2 上测试了完全相同的代码(纯 UUID，没有转换——上面发布的代码的第一个版本)，这有点有趣.2.5)而且，猜猜看，它很有魅力(当然，我必须在persistence.xml 中更改与连接相关的信息).
Well, well, well... It's kinda interesting that I tested the exact same code (plain UUID, no conversion -- the first version of the code posted above) with SQL server 2008 R2 using the JTDS driver (v1.2.5) and, guess what, it worked as a charm (of course I had to change connection-related info in persistence.xml).
现在，它是 PostgreSQL 特定的问题还是什么?
Now, is it a PostgreSQL-specific issue or what?
推荐答案
不幸的是，PostgreSQL JDBC 驱动程序选择了一种表示非 JDBC 标准类型代码的方式.他们只是将所有这些映射到 Types.OTHER.长话短说，您需要启用特殊的 Hibernate 类型映射来处理 UUID 映射(到 postgres 特定的 uuid 数据类型的列):
The PostgreSQL JDBC driver has chosen an unfortunately way to represent non-JDBC-standard type codes. They simply map all of them to Types.OTHER. Long story short, you need to enable a special Hibernate type mapping for handling UUID mappings (to columns of the postgres-specific uuid datatype):
@Id
@Column(name = "customer_id")
@org.hibernate.annotations.Type(type="org.hibernate.type.PostgresUUIDType")
private UUID id;
或更简洁:
@Id
@Column(name = "customer_id")
@org.hibernate.annotations.Type(type="pg-uuid")
private UUID id;
另一个(更好)的选择是将 org.hibernate.type.PostgresUUIDType 注册为默认的 Hibernate 类型映射，用于公开为 java.util.UUID 的所有属性.这在文档@http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch06.html#types-registry
Another (better) option is to register org.hibernate.type.PostgresUUIDType as the default Hibernate type mapping for all attributes exposed as java.util.UUID. That is covered in the documentation @ http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch06.html#types-registry
这篇关于使用 JPA 在 PostgreSQL 中持久化 UUID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
