Postgresql Uuid Supported by Hibernate

Postgresql UUID supported by Hibernate?

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.

Postgres + Hibernate + Java UUID

Try use the latest development version of the JDBC driver (Currently 8.4dev-700), or wait for the next release version. (Edited to add: 8.4-701 has been released)

The release notes mention this change:

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

Postgres UUID and Hibernate → no column found

Thanks to @JBNizet kind comment I found out the problem was not in weird UUID behaviours, but that Hibernate does not escape identifiers by default.

There are actually three easy solutions to the question:

1. Do not use reserved keywords, change table name to something else.

2. Manually escape table name (like @Table(name = "\"user\"") in HUser.java).

3. Add line hibernate.globally_quoted_identifiers=true to your config. I wonder why is it not true by default... See this for more details.

Hibernate/JPA - Collection of UUID can't determine data type when using IS NULL comparison

Hibernate has three basic types mapped to the java.util.UUID:

1. org.hibernate.type.UUIDBinaryType is mapped to binary data types (bytea)

2. org.hibernate.type.UUIDCharType is mapped to CHAR, can also read VARCHAR

3. org.hibernate.type.PostgresUUIDType is mapped to the PostgreSQL UUID, through Types#OTHER, which complies to the PostgreSQL JDBC driver definition.

A lot of PostgreSQL types mapped to the java.sql.Types.OTHER, for example org.postgresql.util.Pgobject (bit varying), org.postgresql.geometric.Pgpoint (point), org.postgresql.geometric.Pgbox (box), java.util.UUID (uuid), ... (see this). So, PostgreSQL JDBC driver could not determine data type of parameter when you pass null.

Solutions

1. You can correct your query in the following way:

@Query("SELECT e FROM Element e WHERE (:uuids) IS NULL OR CAST(e.uuid as org.hibernate.type.UUIDCharType) IN (:uuids)")
List<Element> findByUuidIn(@Param("uuids") Collection<UUID> uuids);

It will allow you to use Collection<UUID> as passed parameter type. But, actually this is similar to your workaround, because it will generate the following sql:

select
   element0_.id as id1_2_ 
from TEST_SCHEMA.ELEMENT element0_ 
where  ? is null or cast(element0_.id as varchar(255)) in (?)

2. You can change your table's column definition:

create table ELEMENT
(
   id bytea,
   ...
);

Then map this column in the following way:

import org.hibernate.annotations.Type;

@Entity
public class Element
{
   @Id
   @GeneratedValue
   @Type(type = "uuid-binary") // This is pg-uuid by default for PostgreSQL82Dialect and higher
   private UUID id;

   // ...
}

and then you will be able to query it without any casting:

Collection<UUID> uuids = null;

List<Element> elements = em.createQuery(
   "select e from Element e where (:uuids) is null or e.id in (:uuids)",
   Element.class)
.setParameter("uuids", uuids)
.getResultList();

UUID[] in hibernate not mapping

I found out the solution. I am using hibernate 4.3.11.Final due to some issues and couldn't use the typedef class UUIDArrayType.class so I imported the dependency in pom.xml

    <dependency>
        <groupId>com.vladmihalcea</groupId>
        <artifactId>hibernate-types-5</artifactId>
        <version>2.9.13</version>
    </dependency>

Now my code looks like this

...

@TypeDefs({
        @TypeDef(name = "uuid-array", typeClass = UUIDArrayType.class) })
...

    @Type(type = "uuid-array")
    @Column(name = "system_ids", columnDefinition = "uuid[]")
    private UUID[] system_ids;

...

---

Related Topics