Persisting UUID in PostgreSQL using JPA

Question

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?

Answer 1

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

Answer 2

JPA 2.1 provides a very easy way to use the PostgreSQL uuid column type and java.util.UUID as the type of the corresponding entity field:

@javax.persistence.Converter(autoApply = true) public class PostgresUuidConverter implements AttributeConverter<UUID, UUID> {      @Override     public UUID convertToDatabaseColumn(UUID attribute) {         return attribute;     }      @Override     public UUID convertToEntityAttribute(UUID dbData) {         return dbData;     }  } 

Just add this class to your persistence configuration and annotate UUID fields with @Column(columnDefinition="uuid").