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

Answer 3

To have it working with Hibernate 5.1.x, you can follow Steve Ebersole comment here

@Id
@GeneratedValue
@Column( columnDefinition = "uuid", updatable = false )
public UUID getId() {
    return id;
}

Answer 4

Newer version >= 8.4-701 of Postgresql JDBC driver correctly handle java.util.UUID mapping. And so do Hibernate >= 4.3.x.

See details on https://stackoverflow.com/a/780922/173149:

Map the database uuid type to java.util.UUID. This only works for relatively new server (8.3) and JDK (1.5) versions.

Answer 5

The solution suggested by Oleg did not work perfectly for me (it failed if you tried to persist a null value). Here is a tweaked solution that also works for null values.

In my case i am using EclipseLink though so if you are using Hibernate you might not need this.

public class UuidConverter implements AttributeConverter<UUID, Object> {
    @Override
    public Object convertToDatabaseColumn(UUID uuid) {
        PGobject object = new PGobject();
        object.setType("uuid");
        try {
            if (uuid == null) {
                object.setValue(null);
            } else {
                object.setValue(uuid.toString());
            }
        } catch (SQLException e) {
            throw new IllegalArgumentException("Error when creating Postgres uuid", e);
        }
        return object;
    }

    @Override
    public UUID convertToEntityAttribute(Object dbData) {
        return (UUID) dbData;
    }
}

Answer 6

Adds this flag on postgresql url connection: ?stringtype=unspecified

like that

jdbc:postgresql://localhost:5432/yourdatabase?stringtype=unspecified