Persisting UUID in PostgreSQL using JPA

问题:

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?

回答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

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

回答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;
}

回答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.

回答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;
    }
}

回答6:

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

like that

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

回答7:

I am sure, that once you generated UUID for a record, it will be static. Thus there is no point in mapping UUID as an object to database and then converting it back to String on retrieval.

Use UUID as a String:

@Id
@Column(name = "customer_id")
private String id;

//getter and setter

public void setId(UUID id) {
    this.id = id.toString();
}

Also there are some things to consider:

1. Whether to use ID, UUID or both. There is quick discussion over this.
2. Using 'uuid' for names rather than 'id', it is misleading.
3. Removing '-' signs from UUID, save 4 bytes.
4. Generating UUID in @PrePersist section if you use Spring.