How to use a jsonb column with H2, JPA, and Hibernate

Question

Using Spring, when I try running my integration tests don't work because the H2 database can't create the table. This is the error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table foo (id binary not null, bar jsonb, primary key (id))" via JDBC Statement

My entity being defined as follow :

@Entity(name = "foo")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Foo {
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    private UUID id;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private Object bar;
}

My type being defined through com.vladmihalcea:hibernate-types-52:2.14.0.

And my h2 version being com.h2database:h2:2.0.202.

If I remove the bar property, it works fine.

Any clue please ?

Answer 1

You can create a JSONB domain in H2 and use it.

If you want to use H2 as the testing database in your Spring application that runs on Postgresql, adding the following to the connection string provides JSONB type.

jdbc:h2:mem:public;MODE=PostgreSQL;INIT=CREATE DOMAIN IF NOT EXISTS JSONB AS JSON;

For JPA you will need to configure custom read and write converters separately for runtime and for testing.

Use PGObject in Postgresql converters and byte[] in H2 converters. There should be enough answers on Stackoverflow about creating custom converters for JPA.