Skip to content

Instantly share code, notes, and snippets.

@agrawald

agrawald/h2-jsonb.md

Forked from madz0/h2-jsonb.md
Created November 22, 2019 05:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save agrawald/adad25d28bf6c56a7e4618fe95ee5a39 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save agrawald/adad25d28bf6c56a7e4618fe95ee5a39 to your computer and use it in GitHub Desktop.
Download ZIP
jsonb work around for H2 database
Raw
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.

@titobundy
Copy link

titobundy commented Apr 20, 2020

Hi,

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

@agrawald
Copy link
Author

agrawald commented Apr 21, 2020

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

@titobundy
Copy link

titobundy commented Apr 21, 2020

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>

@pdemanget
Copy link

pdemanget commented Jun 4, 2021

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"

@shahamit
Copy link

shahamit commented Jun 23, 2021

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.

@swiss-chris
Copy link

swiss-chris commented Apr 28, 2022 •
edited
Loading

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment