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?

Assiniboine

possible duplicate of Postgresql UUID supported by Hibernate? – Sabu

As I mentioned in my post, there are many questions on SO with the same problem but they are all old and none seem to have a good, working solution. The post you mentioned is no exception (which, btw, I saw before I posted my question). – Assiniboine

Answer 1

The PostgreSQL JDBC driver has chosen an unfortunate 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

Aldercy

Hi, i tried this but i got a org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111. Anyone can help me? – Midas

@Midas I had the same issue but forgot to select org.hibernate.dialect.PostgreSQLDialect in my Grails project. Maybe you're missing that or some other settings? I didn't need the following, but it may help you: andrew-arch.blogspot.com/2008/02/… – Boysenberry

To make it work with JPA 2.0 and EclipseLink (Glassfish 3.1.2) have a look at github.com/ancoron/pg-inet-maven/wiki/… – Seppuku

Found event simpler solution for JPA 2.0 with EclipseLink - just annotate your UUID field with @Convert("uuidConverter") @TypeConverter(name = "uuidConverter", dataType = Object.class, objectType = UUID.class) private UUID id; (both annotations from 'org.eclipse.persistence.annotations' package) – Seppuku

Does this work for SQLQuery queries as well or only for HQL queries? – Maniemanifest

Answer 2

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

Actinon

I would voice twice for this answer if I could. columnDefinition specification solved me an issue when UUID was replaced by geometry type while tables creation. It happened because of PostgisDialect were used. – Infanticide

Answer 3

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

Thadthaddaus

Not having any luck with this. The @Converter annotation doesn't apply to a class. – Fusible

I am not sure what do you mean. Both javax.persistence.Converter and org.eclipse.persistence.annotations.Converter can be applied to a class. I edited the answer to make it explicit that it uses javax.persistence.Converter. – Thadthaddaus

It worked (PG9.4) - Also @Column annotation is not required for me. – Shea

You can't use it for @Id fields. – Criminal

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://mcmap.net/q/425998/-postgres-hibernate-java-uuid:

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

Bumgardner

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

Sufi

Answer 6

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

like that

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

Swept

While this may answer the question, it is better to explain the essential parts of the answer and possibly what was the problem with OPs code. – Piliform