使用JPA在PostgreSQL中持久化UUID [英] Persisting UUID in PostgreSQL using JPA

问题描述

我试图在PostgreSQL中坚持使用UUID作为主键的实体。我尝试将它作为普通的UUID持久化：

  @Id 
 @Column（name =customer_id） 
私人UUID ID; 
  

有了上述，我得到这个错误：

 错误：列customer_id的类型为uuid，但表达式的类型为bytea 
提示：您需要重写或转换表达式。 
位置：137 
  

我也尝试将UUID保存为byte []无效：

  @Transient 
私人UUID ID; 
 
 @Id 
 @Column（name =customer_id）
 @Access（AccessType.PROPERTY）
 @Lob 
保护字节[] getRowId（ ）{
 return id.toString（）。getBytes（）; 
 
 
 protected void setRowId（byte [] rowId）{
 id = UUID.fromString（new String（rowId））; 
} 
  

如果我删除@Lob，我得到的错误与张贴在上面。但是应用了@Lob，这个错误稍微改变了：

 错误：列customer_id的类型是uuid，但表达式是键入bigint 
提示：您将需要重写或转换表达式。 
职位：137 
  

我感觉非常糟糕，无法做到像这个！

我使用Hibernate 4.1.3.Final和PostgreSQL 9.1。

我在同一个问题上或多或少地看到了很多问题，但他们都很陈旧，似乎没有一个简单的答案。

我想以一种标准的方式实现这一点，而不诉诸于丑陋的黑客。但是，如果只有（丑陋的）黑客才能实现，那么可能就是我要做的。但是，我不想将UUID作为varchar存储在数据库中，因为这对性能不利。另外，如果可能，我不想在代码中引入Hibernate依赖项。

任何帮助都将不胜感激。

UPDATE 1 （2012-07-03 12:15 pm）

好吧，好吧...有趣的是，我使用JTDS驱动程序（v1.2.5）测试了与SQL Server 2008 R2完全相同的代码（普通的UUID，没有转换 - 上面发布的代码的第一个版本），猜测它是什么，当然我必须在persistence.xml中更改连接相关的信息）。

现在，它是PostgreSQL特定的问题还是什么？

解决方案

PostgreSQL JDBC驱动程序选择了一种不幸的方式来表示非JDBC标准类型代码。他们只是将它们全部映射到Types.OTHER。长话短说，你需要启用一个特殊的Hibernate类型映射来处理UUID映射（到特定于postgres的uuid数据类型的列）：

pre $ code $ @ $
@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

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

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));
}

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!

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.

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.

Any help would be greatly appreciated.

UPDATE 1 (2012-07-03 12:15 pm)

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).

Now, is it a PostgreSQL-specific issue or what?

解决方案

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;

or more succinctly:

@Id
@Column(name = "customer_id")
@org.hibernate.annotations.Type(type="pg-uuid")
private UUID id;

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屋！