Postgresql UUID supported by Hibernate?
Asked Answered
S

8

28

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>
Southland answered 21/12, 2010 at 0:17 Comment(0)
V
45

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.

Velar answered 20/9, 2011 at 1:4 Comment(4)
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.Izmir
(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" );Izmir
Fails to H2 database. Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111Kortneykoruna
@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.Romanfleuve
K
11

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/

Kosak answered 18/11, 2019 at 11:12 Comment(0)
S
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.

Stanzel answered 21/12, 2010 at 13:23 Comment(5)
thank you for the answer, lech, but according to 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 opensource.atlassian.com/projects/hibernate/browse/HHH-4093 that shows use of UUID without and redundant workarounds.Southland
damn, I've just noticed that the patches from the ticket I mentioned are not applied to hibernate 3.6.0 distribution! Solved.Southland
And moreover those patches are not applicable to hibernate 3.6.0 sources which apparently possessed some refactoring.Southland
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.Almanza
Apart from my criticism towards the use of strings for keys: Thank you for explaining what actually goes wrong. (upvote)Almanza
U
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;
        }
    }
}
Unfetter answered 25/6, 2015 at 9:35 Comment(0)
P
1

The new Jakarta Persistence 3.1(aka JPA 3.1, part of Jakarta EE 10) adds UUID primary key generation strategy and supports UUID as primary key and basic types. The JPA providers including EclipseLink4.x and Hibernate 6.2/6.3.x have already support these.

Simply use java UUID type in the codes and uuid data type in Postgres database.

@Entity
public class Person {
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @Column(...)
    private UUID apiKey;

}

Check my examples of demonstrating JPA 3.1 using EclipseLink in a Glassfish Container and Hibernate with Java SE.

Perambulator answered 26/9, 2023 at 1:30 Comment(0)
N
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>
Nevlin answered 9/10, 2014 at 8:9 Comment(0)
J
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()
Jacklyn answered 10/9, 2021 at 11:15 Comment(0)
H
0

For new hibernate I used

@JdbcType(UUIDJdbcType.class)
Heth answered 20/6, 2023 at 8:37 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.