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

Question

I use in application MySQL 5.7 and I have JSON columns. When I try running my integration tests don't work because the H2 database can't create the table. This is the error:

2016-09-21 16:35:29.729 ERROR 10981 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table payment_transaction (id bigint generated by default as identity, creation_date timestamp not null, payload json, period integer, public_id varchar(255) not null, state varchar(255) not null, subscription_id_zuora varchar(255), type varchar(255) not null, user_id bigint not null, primary key (id))
2016-09-21 16:35:29.730 ERROR 10981 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Unknown data type: "JSON"; SQL statement:

This is the entity class.

@Table(name = "payment_transaction")
public class PaymentTransaction extends DomainObject implements Serializable {

    @Convert(converter = JpaPayloadConverter.class)
    @Column(name = "payload", insertable = true, updatable = true, nullable = true, columnDefinition = "json")
    private Payload payload;

    public Payload getPayload() {
        return payload;
    }

    public void setPayload(Payload payload) {
        this.payload = payload;
    }
}

And the subclass:

public class Payload implements Serializable {

    private Long userId;
    private SubscriptionType type;
    private String paymentId;
    private List<String> ratePlanId;
    private Integer period;

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public SubscriptionType getType() {
        return type;
    }

    public void setType(SubscriptionType type) {
        this.type = type;
    }

    public String getPaymentId() {
        return paymentId;
    }

    public void setPaymentId(String paymentId) {
        this.paymentId = paymentId;
    }

    public List<String> getRatePlanId() {
        return ratePlanId;
    }

    public void setRatePlanId(List<String> ratePlanId) {
        this.ratePlanId = ratePlanId;
    }

    public Integer getPeriod() {
        return period;
    }

    public void setPeriod(Integer period) {
        this.period = period;
    }

}

And this converter for insert in database:

public class JpaPayloadConverter implements AttributeConverter<Payload, String> {

    // ObjectMapper is thread safe
    private final static ObjectMapper objectMapper = new ObjectMapper();

    private Logger log = LoggerFactory.getLogger(getClass());

    @Override
    public String convertToDatabaseColumn(Payload attribute) {
        String jsonString = "";
        try {
            log.debug("Start convertToDatabaseColumn");

            // convert list of POJO to json
            jsonString = objectMapper.writeValueAsString(attribute);
            log.debug("convertToDatabaseColumn" + jsonString);

        } catch (JsonProcessingException ex) {
            log.error(ex.getMessage());
        }
        return jsonString;
    }

    @Override
    public Payload convertToEntityAttribute(String dbData) {

        Payload payload = new Payload();
        try {
            log.debug("Start convertToEntityAttribute");

            // convert json to list of POJO
            payload = objectMapper.readValue(dbData, Payload.class);
            log.debug("JsonDocumentsConverter.convertToDatabaseColumn" + payload);

        } catch (IOException ex) {
            log.error(ex.getMessage());
        }
        return payload;

    }
}

Answer 1

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/

Answer 2

Champagne time!

Starting with the version 2.11, the Hypersistence Utils 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.

Answer 3

A workaround is actually to create a custom column data type in H2 for the jsonb type, and put the query in the datasource url like this:

spring.datasource.url=jdbc:h2:mem:testdb;INIT=create domain if not exists jsonb as text;MODE=PostgreSQL"

Now for tests and integration tests in particular, it would be preferable to use the same DB than your application, via TestContainers

Answer 4

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.

Answer 5

My problem was with JSONB since H2 does not support it as was already mentioned.

One more problem is that when you insert a json, H2 transforms it into a json object string which makes jackson serialization fail. ex: "{\"key\": 3}" instead of {"key": 3} . One solution is to use FORMAT JSON when inserting the json, but then you need to have duplicate insert files if you are using flyway, for example.

Inspired by the @madz answer I came across with this solution:

Create a custom JsonbType (on production - e.g. main/java/com/app/types/JsonbType.java)

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

public class JsonbType extends JsonBinaryType {
  private static final long serialVersionUID = 1L;
}

Create a custom JsonbType (on tests - e.g. test/java/com/app/types/JsonbType.java)

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

public class JsonbType extends JsonStringType {
  private static final long serialVersionUID = 1L;
  @Override
  public String getName() {
      return "jsonb";
  }
}

Create an alias type from JSONB to JSON only on tests (h2):

-- only on H2 database
CREATE TYPE "JSONB" AS TEXT;

note: I'm using flyway which make it easy to do but you can follow @jchrbrt suggestion

Finally you declare the type on your entity model, as follows:

import com.app.types.JsonbType;

@TypeDef(name = "jsonb", typeClass = JsonbType.class)
@Entity(name = "Translation")
@Table(name = "Translation")
@Data
public class Translation {
  @Type(type = "jsonb")
  @Column(name="translations")
     private MySerializableCustomType translations; 
  }
}

That's it. I hope it helps someone.

Answer 6

This is how I solved it in Spring context:

1. Create /src/test/resources/init.sql

CREATE TYPE "JSONB" AS json;

2. Configure H2 datasource as follows /src/test/resources/application-test.yml

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:init.sql'
    username: sa
    password: sa

Source article

Answer 7

In my case we were dealing with PostgreSQL jsonb type in production and H2 for our tests.

I could not test @n00dle 's solution because apparently spring does not support executing a SQL script before Hibernate's ddl-auto=update for our tests so I used another way to solve this.

Here is a gist for it.

The overall idea is to create two package-info files. One for production and the other for tests and register different types (JsonBinaryType.class for production and TextType.class for tests) to handle them differently for PostgreSQL and H2

Answer 8

I have solved the problem using TEXT type in H2. One must create a separate database script to create schema in H2 for tests and replace the JSON type by TEXT.

It is still a problem since if you use Json function in queries, you will not be able to test those while with H2.

Answer 9

Example with Kotlin + Spring + Hibernate + Postgres + jsonb column

Create the entity:

@Entity
@TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
class MyEntity(
    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    val myConfig: String,

    @Id
    @GeneratedValue
    val id: Long = 0,
)

JsonBinaryType.class comes from https://github.com/vladmihalcea/hibernate-types

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.9.13</version>
</dependency>

Configure your H2 database in spring profile. The key line is this: INIT=create domain if not exists jsonb as other

spring:
    profiles: h2

    datasource:
        driver-class-name: org.h2.Driver
        url: jdbc:h2:mem:testdb;INIT=create domain if not exists jsonb as other;MODE=PostgreSQL;DB_CLOSE_DELAY=-1
        username: sa
        password: sa

spring.jpa.hibernate.ddl-auto: create

Write the test:

// Postgres test
@SpringBootTest
class ExampleJsonbPostgres(@Autowired private val myEntityRepository: MyEntityRepository) {
    @Test
    fun `verify we can write and read jsonb`() {
        val r = myEntityRepository.save(MyEntity("""{"hello": "world"}"""))
        assertThat(myEntityRepository.findById(r.id).get().config).isEqualTo("""{"hello": "world"}""")
    }
}

// H2 test
@ActiveProfiles("h2")
@SpringBootTest
class ExampleJsonbH2(@Autowired private val myEntityRepository: MyEntityRepository) {
    @Test
    fun `verify we can write and read jsonb`() {
        val r = myEntityRepository.save(MyEntity("""{"hello": "world"}"""))
        assertThat(myEntityRepository.findById(r.id).get().config).isEqualTo("""{"hello": "world"}""")
    }
}

Alternatively you can try to define custom type per database in hibernate XML as described here: https://stackoverflow.com/a/59753980/10714479

Answer 10

I am in the same situation as @madz, where we use Postgres in production and H2 for unit tests. In my case i found a bit more simple solution, i think. We use Liquibase for database migrations, so here i made a conditional migration only to be run on H2, where i change the column type to H2's "other" type.

With the other type, H2 just stores it in the database and doesn't think twice about how the data is formatted etc. This does require however that you are not doing anything with the JSON directly in the database, and only in your application.

My migration looks like this:

  # Use other type in H2, as jsonb is not supported
  - changeSet:
      id: 42
      author: Elias Jørgensen
      dbms: h2
      changes:
        - modifyDataType:
            tableName: myTableName
            columnName: config
            newDataType: other

Along with this, i added the following to my test datasource:

INIT=create domain if not exists jsonb as text;

Answer 11

The correct way of avoiding such things is using liquibase or flywaydb to evolve your schema and never ever allow Hibernate to create it.

Answer 12

H2 does not have the JSON data type.

In MySQL the JSON type is just an alias for the LONGTEXT data type so the actual data type for the column will be LONGTEXT.