22

I can't get Hibernate working with java.util.UUID for PostgreSQL.

Here is the mapping using javax.persistence.* annotations:

private UUID itemUuid;

@Column(name="item_uuid",columnDefinition="uuid NOT NULL")
public UUID getItemUuid() {
    return itemUuid;
}

public void setItemUuid(UUID itemUuid) {
    this.itemUuid = itemUuid;
}

When persisting a transient object I get a SQLGrammarException:

column "item_uuid" is of type uuid but expression is of type bytea at character 149

PostgreSQL version is 8.4.4
JDBC driver - 8.4.4-702 (also tried 9.0 - same thing)
Hibernate version is 3.6, main configuration properties:

<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://192.168.1.1/db_test</property>
approxiblue
  • 6,982
  • 16
  • 51
  • 59
forker
  • 2,609
  • 4
  • 23
  • 24

7 Answers7

43

This can be solved by adding the following annotation to the UUID:

import org.hibernate.annotations.Type;
...
@Type(type="pg-uuid")
private java.util.UUID itemUuid;

As to why Hibernate doesn't just make this the default setting, I couldn't tell you...

UPDATE: There still seem to be issues using the createNativeQuery method to open objects that have UUID fields. Fortunately, the createQuery method so far has worked fine for me.

Robyn P
  • 644
  • 9
  • 8
  • 1
    The issue with the createNativeQuery only happens if you do not tell Hibernate the types to expect in return. The problem is that Postgres decided to map multiple custom datatypes to the JDBC type code OTHER in their JDBC driver. So when reading these values out of a result set without direction from the user Hibernate has to decide which to use. It does that based on the type mappings registered in the Dialect. – Steve Ebersole Jun 06 '12 at 14:13
  • 4
    (continued) If your want it to work without specifying type information for the query the create a custom Dialect (extending the Postgres one you use noww) that does the following call in its constructor: `registerHibernateType( Types.OTHER, "pg-uuid" );` – Steve Ebersole Jun 06 '12 at 14:15
  • 3
    Fails to `H2` database. `Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111` – daydreamer Nov 05 '14 at 04:13
  • 11
    `@Type(type="pg-uuid")` is not recognized in the latest version of hibernate (5.2.12). Instead i used `@Type(type="org.hibernate.type.PostgresUUIDType")` to solve the problem. – Sri Feb 06 '18 at 20:05
10

Now you can also use the UUID class provided by java.util.UUID which gets mapped to uuid datatype of Postgres by Hibernate without any conversions required while reading/writing from the database.

  @Id
  @GeneratedValue
  private UUID id;

The generated value is auto by default this lets your JVM define the UUID. This also allows hibernate to use the batch insert optimisation.

You can configure the database to set the UUID value. More information can be found here https://vladmihalcea.com/uuid-identifier-jpa-hibernate/

wrathtoliar
  • 261
  • 3
  • 4
6

You try to persist object of type UUID, which is not hibernate-annotated entity. So the hibernate wants to serialize it to byte array (blob type). This is why you get this message 'expression of type bytea'.

You can either store UUID as blobs in database (not elegant), or provide your custom serializer (much work) or manually convert that object. UUID class has methods fromString and toString, so I would store it as String.

Danubian Sailor
  • 1
  • 38
  • 145
  • 223
  • 2
    thank you for the answer, lech, but according to http://opensource.atlassian.com/projects/hibernate/browse/HHH-3579 Hibernate should work with UUID type just like with any other.. and having uuids in char type is hurtful from performance standpoint. there is also a tutorial project http://opensource.atlassian.com/projects/hibernate/browse/HHH-4093 that shows use of UUID without and redundant workarounds. – forker Dec 21 '10 at 21:35
  • damn, I've just noticed that the patches from the ticket I mentioned are not applied to hibernate 3.6.0 distribution! Solved. – forker Dec 21 '10 at 22:46
  • And moreover those patches are not applicable to hibernate 3.6.0 sources which apparently possessed some refactoring. – forker Dec 21 '10 at 22:57
  • In general it's not a good idea to store IDs as strings in your database though. Because it consumes more bytes than numeric types, is influenced by encodings and they are sorted as text. – bvdb Jul 01 '20 at 12:58
  • Apart from my criticism towards the use of strings for keys: Thank you for explaining what actually goes wrong. (upvote) – bvdb Jul 01 '20 at 13:00
3

As others mentioned, the solution to this issue is to add a @Type(type = "pg-uuid") annotation. However, this type is not compatible with UUID types of other vendors, so this ties your Hibernate classes to Postgres. To work around this, it is possible to insert this annotation at runtime. The below works for Hibernate 4.3.7.

First, you need to insert a custom metadata provider to insert the annotations. Do this as the first step after creating an instance of the Configuration class:

// Perform some test to verify that the current database is Postgres.
if (connectionString.startsWith("jdbc:postgresql:")) {
    // Replace the metadata provider with our custom metadata provider.
    MetadataProviderInjector reflectionManager = MetadataProviderInjector)cfg.getReflectionManager();
    reflectionManager.setMetadataProvider(new UUIDTypeInsertingMetadataProvider(reflectionManager.getMetadataProvider()));
}

This custom metadata provider finds fields and methods of type UUID. If it finds one, it inserts an instance of the org.hibernate.annotations.Type annotation stating that the type should be "pg-uuid":

package nl.gmt.data;

import org.hibernate.annotations.Parameter;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.common.reflection.AnnotationReader;
import org.hibernate.annotations.common.reflection.MetadataProvider;

import java.lang.annotation.Annotation;
import java.lang.reflect.AnnotatedElement;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

class UUIDTypeInsertingMetadataProvider implements MetadataProvider {
    private final Map<AnnotatedElement, AnnotationReader> cache = new HashMap<>();
    private final MetadataProvider delegate;

    public UUIDTypeInsertingMetadataProvider(MetadataProvider delegate) {
        this.delegate = delegate;
    }

    @Override
    public Map<Object, Object> getDefaults() {
        return delegate.getDefaults();
    }

    @Override
    public AnnotationReader getAnnotationReader(AnnotatedElement annotatedElement) {
        // This method is called a lot of times on the same element, so annotation
        // readers are cached. We only cache our readers because the provider
        // we delegate to also caches them.

        AnnotationReader reader = cache.get(annotatedElement);
        if (reader != null) {
            return reader;
        }

        reader = delegate.getAnnotationReader(annotatedElement);

        // If this element is a method that returns a UUID, or a field of type UUID,
        // wrap the returned reader in a new reader that inserts the "pg-uuid" Type
        // annotation.

        boolean isUuid = false;
        if (annotatedElement instanceof Method) {
            isUuid = ((Method)annotatedElement).getReturnType() == UUID.class;
        } else if (annotatedElement instanceof Field) {
            isUuid = ((Field)annotatedElement).getType() == UUID.class;
        }

        if (isUuid) {
            reader = new UUIDTypeInserter(reader);
            cache.put(annotatedElement, reader);
        }

        return reader;
    }

    private static class UUIDTypeInserter implements AnnotationReader {
        private static final Type INSTANCE = new Type() {
            @Override
            public Class<? extends Annotation> annotationType() {
                return Type.class;
            }

            @Override
            public String type() {
                return "pg-uuid";
            }

            @Override
            public Parameter[] parameters() {
                return new Parameter[0];
            }
        };

        private final AnnotationReader delegate;

        public UUIDTypeInserter(AnnotationReader delegate) {
            this.delegate = delegate;
        }

        @Override
        @SuppressWarnings("unchecked")
        public <T extends Annotation> T getAnnotation(Class<T> annotationType) {
            if (annotationType == Type.class) {
                return (T)INSTANCE;
            }

            return delegate.getAnnotation(annotationType);
        }

        @Override
        public <T extends Annotation> boolean isAnnotationPresent(Class<T> annotationType) {
            return annotationType == Type.class || delegate.isAnnotationPresent(annotationType);
        }

        @Override
        public Annotation[] getAnnotations() {
            Annotation[] annotations = delegate.getAnnotations();
            Annotation[] result = Arrays.copyOf(annotations, annotations.length + 1);
            result[result.length - 1] = INSTANCE;
            return result;
        }
    }
}
Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
0

Solution for someone who don't use JPA.

Before:

<property name="testId" >
        <column name="test_id"  sql-type="uuid"  not-null="true"/>
</property>

After:

<property name="testId" column="test_id" type="org.hibernate.type.PostgresUUIDType">
</property>
Tomasz
  • 1
0

I had a smililar issue while having Sprint Data and jsonb in postgres. Thank you Sri for the solution!

In the Model, replacing

@Type(type="pg-uuid")

with

@Type(type="org.hibernate.type.PostgresUUIDType")

solved the issue running JUnit Tests with @SpringBootTest.

Example in Entity (Kotlin):

@Type(type="org.hibernate.type.PostgresUUIDType")
@Column(
    nullable = false,
    unique = true,
    updatable = false,
    columnDefinition = "CHAR(36)"
)
var uuid: UUID = UUID.randomUUID()
DeWitt
  • 3
  • 3
0

For new hibernate I used

@JdbcType(UUIDJdbcType.class)