Question: error using postgres UUID primary key with Hibernate

Question

error using postgres UUID primary key with Hibernate

Answers 0
Added at 2016-12-14 19:12
Tags
Question

I am developing a greenfield web app that uses Spring boot 1.4.1 - which uses spring 4.3.3 & Hibernate 5.2.3.Final under the hood.

We are using Postgres 9.4 for our database, so I have the postgres-9.4.1212.jar in my class path as well.

All my tables use primary (& foreign) keys of type UUID. In my entity class itself, I have the following annotations over the id property:

@Id
@Type(type = "pg-uuid")
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
@Column(unique = true, nullable = false, columnDefinition = "uuid")
private UUID id;

When we connect to the database using this url and do a query

spring.datasource.url: jdbc:postgresql://localhost:5432/mydb

We get the following error

Caused by: org.postgresql.util.PSQLException: 
    ERROR: operator does not exist: uuid = bytea
  Hint: No operator matches the given name and argument type(s). 
    You might need to add explicit type casts.
  Position: 139
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)

The error goes away and the query works fine when we change the connection url to the following:

spring.datasource.url: jdbc:postgresql://localhost:5432/mydb?stringtype=unspecified

Note the added suffix: ?stringtype=unspecified

Question:

Is this the right thing to do? Is there a better way to fix the error?

Saw a few stack-overflow posts related to using postgres UUID in hibernate, so it looks like other folks are having the same issue as well. None of the answers seem satisfactory. It is odd that this would not work out of the box.

After perusing Hibernate 5.2.3 documentation and R-ing TFM, I see this snippet which seems like it may be related:

Hibernate User Guide

Quoting from the above link

The default UUID mapping is as binary because it represents more efficient storage. However many applications prefer the readability of character storage. To switch the default mapping, simply call

MetadataBuilder.applyBasicType( UUIDCharType.INSTANCE, UUID.class.getName() )

Also elsewhwere in section 2.3.13 in the above document, it also says this

Maps the UUID using PostgreSQL’s specific UUID data type. The PostgreSQL JDBC driver chooses to map its UUID type to the OTHER code. Note that this can cause difficulty as the driver chooses to map many different data types to OTHER.

I suspect I need to create an @Bean to tell Spring-boot to configure the MetadataBuilder, but am not quite sure how to do this.

Looking for some insights on how to configure hibernate's MetadataBuilder to process UUID as char instead of binary.

-sgb

Answers
Source Show
◀ Wstecz