postgresql - UUID support for different DB with Spring boot

Question

I am trying to use UUID as the primary key of my table. I am using postgresql for production and h2 for test. I am creating the table using liquibase and set this column type as UUID, which liquibase support the type for both h2 and postgres

<column name="uuid" type="UUID">

 @Id
 private UUID uuid;

I got to two questions/issues when I try to save a record using JPA with spring-boot. For Postgres, I will get the following error

org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea

I understand this was because JPA doesn't know which type should be converted to from Java UUID type. Some people suggest using @Type(type="org.hibernate.type.PostgresUUIDType") on the UUID filed in the entity class. However, this doesn't change anything for me and I still got the same error. Not sure what I have missed.

A further question is how to make it also working with h2 when I switch to run the test config, as the annotation is specific to the Postgres. Any suggestion please?

question from:https://stackoverflow.com/questions/66051135/uuid-support-for-different-db-with-spring-boot

Answer 1

Hibernate is not able to map the java UUID to postgres UUID. Make sure you use the correct dialect in appication.yaml

spring:
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQL10Dialect

for local profile you can use the following

spring:
  datasource:
    driverClassName: org.h2.Driver
    username: sa
    password: password
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE