How to map a JSON column with H2, JPA, and Hibernate

I just came across this problem working with the JSONB column type - the binary version of the JSON type, which doesn't map to TEXT.

For future reference, you can define a custom type in H2 using CREATE DOMAIN, as follows:

CREATE domain IF NOT EXISTS jsonb AS other;

This seemed to work for me, and allowed me to successfully test my code against the entity.

Source: https://objectpartners.com/2015/05/26/grails-postgresql-9-4-and-jsonb/

Champagne time! 🍾

Starting with the version 2.11.0, the Hibernate Types project now provides a generic JsonType that works auto-magically with:

• Oracle,
• SQL Server,
• PostgreSQL,
• MySQL, and
• H2.

Oracle

@Entity(name = "Book")@Table(name = "book")@TypeDef(name = "json", typeClass = JsonType.class)public class Book {    @Id    @GeneratedValue    private Long id;    @NaturalId    @Column(length = 15)    private String isbn;    @Type(type = "json")    @Column(columnDefinition = "VARCHAR2(1000) CONSTRAINT IS_VALID_JSON CHECK (properties IS JSON)")    private Map<String, String> properties = new HashMap<>();}

SQL Server

@Entity(name = "Book")@Table(name = "book")@TypeDef(name = "json", typeClass = JsonType.class)public class Book {    @Id    @GeneratedValue    private Long id;    @NaturalId    @Column(length = 15)    private String isbn;    @Type(type = "json")    @Column(columnDefinition = "NVARCHAR(1000) CHECK(ISJSON(properties) = 1)")    private Map<String, String> properties = new HashMap<>();}

PostgreSQL

@Entity(name = "Book")@Table(name = "book")@TypeDef(name = "json", typeClass = JsonType.class)public class Book {    @Id    @GeneratedValue    private Long id;    @NaturalId    @Column(length = 15)    private String isbn;    @Type(type = "json")    @Column(columnDefinition = "jsonb")    private Map<String, String> properties = new HashMap<>();}

MySQL

@Entity(name = "Book")@Table(name = "book")@TypeDef(name = "json", typeClass = JsonType.class)public class Book {    @Id    @GeneratedValue    private Long id;    @NaturalId    @Column(length = 15)    private String isbn;    @Type(type = "json")    @Column(columnDefinition = "json")    private Map<String, String> properties = new HashMap<>();}

H2

@Entity(name = "Book")@Table(name = "book")@TypeDef(name = "json", typeClass = JsonType.class)public class Book {    @Id    @GeneratedValue    private Long id;    @NaturalId    @Column(length = 15)    private String isbn;    @Type(type = "json")    @Column(columnDefinition = "json")    private Map<String, String> properties = new HashMap<>();}

Works like a charm!

So, no more hacks and workarounds, the JsonType will work no matter what DB you are using.

If you want to see it in action, check out this test folder on GitHub.

JSON support was added to H2 after the question was asked, with version 1.4.200 (2019-10-14).

However, you rarely need a JSON data type in a database. JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.

You do need the JSON data type if you need an SQL function that operates on them, and then only if the database insists that its JSON functions operate on a JSON type instead of on a CLOB. Such functions tend to be database-dependent though.