jsonb work around for H2 database

h2-jsonb.md

Creat a package-info file in the models package and put the following contents in there:

@org.hibernate.annotations.TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
package com.ourproject.model;

import com.vladmihalcea.hibernate.type.json.JsonBinaryType;

I used JsonBinaryType in hibernate-types-52 library.

Then in the entity class, I removed columnDefinition="jsonb" from the @Column and only used @Type(type = "jsonb")

This way, for the product, hibernate would map column to jsonb type. Then in the test folder inside the same package I added package-info with this contents:

@org.hibernate.annotations.TypeDef(name = "jsonb", typeClass = TextType.class)
package com.ourproject.model;

import org.hibernate.type.TextType;

Now when we run maven test hibernate generates varchar(2147483647) column type for the jsonb type and it solved.

Hi,

But without columnDefinition="jsonb" you can't create column in table if it doesn't exists. How do you solve it?

I am not using JPA/hibernate to create my schema, I am using liquibase or flyway to abstract away the DB schema creation.

Thanks,

In my case, I am using flyway only for my test classes, with a script to create an alias and without remove @column(columnDefinition="jsonb)

V1.0__create_alias.sql
CREATE TYPE "JSONB" AS json;

json type is supported in H2 1.4.200

<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-core</artifactId>
	<version>5.0.7</version>
	<scope>test</scope>
</dependency>
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<version>1.4.200</version>
	<scope>test</scope>
</dependency>

Wonderfull! Don't forget to remove the eventual TypeDef declaration at class level (like me) or else you will get the error "No Dialect mapping for JDBC type: 1111"

This recent enhancement in hibernate-types library fixes all issues and makes it possible to have a single code snippet for all databases including h2.

This recent enhancement in hibernate-types library fixes all issues and makes it possible to have a single code snippet for all databases including h2.

I was already using JsonType and still was getting No Dialect mapping for JDBC type: 1111.

The workarounds from Vlad to create your own extended Dialect https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/ only partially solved the problem for me. Plus I'm using different Dialects for different Production DBs (Oracle and Postgres) and it wasn't easy to configure this for my tests.

So in the end following all the advice contained above was the cleanest (and only solution) I got to work so far.

I'm using Spring Boot and created 2 package-info.json Files (one for test against H2 and one for production against Postgres) and removed the @TypeDef from the Class. I'm not using Flyway.