persistence jsonb field to h2 using hibernate

source code

I want to use jsonb column type. When I used postgresql there is no problem. But when I use H2 I can not persist my entity. Native sql works but when saving EntityManager.persist i got below error

ERROR: Data conversion error converting "X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: ""attributes"" ""JSONB"")"; SQL statement:
insert into json_entities (attributes, id) values (?, ?) [22018-200]
javax.persistence.RollbackException: Error while committing the transaction
at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:81)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:104)
at H2Test.jsonFieldTest(H2Test.java:39)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:65)
... 29 more
Caused by: org.hibernate.exception.DataException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3254)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3779)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1360)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:451)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3210)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2378)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
... 28 more
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: ""attributes"" ""JSONB"")"; SQL statement:
insert into json_entities (attributes, id) values (?, ?) [22018-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:194)
at org.h2.table.Column.getDataConversionError(Column.java:409)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:381)
at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845)
at org.h2.command.dml.Insert.insertRows(Insert.java:187)
at org.h2.command.dml.Insert.update(Insert.java:151)
at org.h2.command.CommandContainer.update(CommandContainer.java:198)
at org.h2.command.Command.executeUpdate(Command.java:251)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:191)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:152)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
... 47 more
Caused by: org.h2.message.DbException: Data conversion error converting "OTHER to JSON" [22018-200]
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.value.Value.getDataConversionError(Value.java:1504)
at org.h2.value.Value.convertToJson(Value.java:1439)
at org.h2.value.Value.convertTo(Value.java:861)
at org.h2.value.Value.convertTo(Value.java:772)
at org.h2.value.TypeInfo.cast(TypeInfo.java:515)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:378)
... 57 more
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "OTHER to JSON" [22018-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
... 65 more

Here is my simple project.

Custom hibernate type for jsonb

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
/**
* Created by sanco on 29/09/2020.
* h2jsontest
*/
public class PGJsonType implements UserType {
private final int CUSTOM_TYPE = Types.OTHER;
private final static ObjectMapper jsonMapper = new ObjectMapper();
@Override
public int[] sqlTypes() {
return new int[]{CUSTOM_TYPE};
}
@Override
public Class returnedClass() {
return JsonNode.class;
}
@Override
public boolean equals(Object x, Object y) throws HibernateException {
return x==null? y==null : ((JsonNode)x).equals((JsonNode)y);
}
@Override
public int hashCode(Object x) throws HibernateException {
return ((JsonNode)x).hashCode();
}
@Override
public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
final String cellContent = rs.getString(names[0]);
if (cellContent == null) {
return null;
}
try {
return jsonMapper.readTree(cellContent);
} catch (final Exception ex) {
throw new RuntimeException("Failed to convert jsonb to JsonNode: " + ex.getMessage(), ex);
}
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
if (value == null) {
st.setNull(index, CUSTOM_TYPE);
return;
}
try {
st.setObject(index, jsonMapper.writeValueAsString(value), CUSTOM_TYPE);
} catch (final Exception ex) {
throw new RuntimeException("Failed to convert JsonNode to jsonb: " + ex.getMessage(), ex);
}
}
@Override
public Object deepCopy(Object value) throws HibernateException {
return value==null? null : ((JsonNode)value).deepCopy();
}
@Override
public boolean isMutable() {
return true;
}
@Override
public Serializable disassemble(Object value) throws HibernateException {
return (Serializable) value;
}
@Override
public Object assemble(Serializable cached, Object owner) throws HibernateException {
return cached;
}
@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return original;
}
}

my entity class

import com.fasterxml.jackson.databind.JsonNode;
import org.hibernate.annotations.Type;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* Created by sanco on 29/09/2020.
* h2jsontest
*/
@Entity
@Table(name="json_entities")
public class JsonEntity {
@Id
private Long id;
@Type(type = "PGJsonType")
@Column(columnDefinition = "jsonb")
private JsonNode attributes;
public void setId(Long id) {
this.id = id;
}
public Long getId() {
return id;
}
public JsonNode getAttributes() {
return attributes;
}
public void setAttributes(JsonNode attributes) {
this.attributes = attributes;
}
}

persistence unit

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="postgre">
<description>
Hibernate using JPA
</description>
<class>JsonEntity</class>
<properties>
<property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/postgres"/>
<property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
<property name="hibernate.connection.username" value="postgres"/>
<property name="hibernate.connection.password" value="s2351910"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL10Dialect"/>
<property name="hibernate.temp.use_jdbc_metadata_defaults"
value="false"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>
<property name="hibernate.enable_lazy_load_no_trans" value="true"/>
<property name="hibernate.connection.provider_class"
value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>
<property name="hibernate.hikari.minimumIdle" value="5"/>
<property name="hibernate.hikari.maximumPoolSize" value="20"/>
<property name="hibernate.hikari.idleTimeout" value="45000"/>
</properties>
</persistence-unit>
<persistence-unit name="h2">
<description>
Hibernate using JPA
</description>
<class>JsonEntity</class>
<properties>
<property name="hibernate.connection.url" value="jdbc:h2:mem:organization;MODE=PostgreSQL;IGNORECASE=TRUE;DATABASE_TO_LOWER=TRUE;AUTO_RECONNECT=TRUE;INIT=CREATE DOMAIN IF NOT EXISTS jsonb AS other\;CREATE TYPE if not exists "JSONB" AS json;"/>
<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
<property name="hibernate.temp.use_jdbc_metadata_defaults"
value="false"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>
<property name="hibernate.enable_lazy_load_no_trans" value="true"/>
<property name="hibernate.connection.provider_class"
value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>
<property name="hibernate.hikari.minimumIdle" value="5"/>
<property name="hibernate.hikari.maximumPoolSize" value="20"/>
<property name="hibernate.hikari.idleTimeout" value="60000"/>
</properties>
</persistence-unit>
</persistence>

test classes
H2

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
/**
* Created by sanco on 29/09/2020.
* h2jsontest
*/
public class H2Test {
private static EntityManagerFactory factory;
private static ObjectMapper mapper;
@BeforeClass
public static void init(){
factory = Persistence.createEntityManagerFactory("h2");
mapper = new ObjectMapper();
}
@Test
public void jsonFieldTest(){
EntityManager em = factory.createEntityManager();
JsonEntity je = new JsonEntity();
je.setId(1L);
ObjectNode on = mapper.createObjectNode();
on.put("key", "value");
je.setAttributes(on);
try {
em.getTransaction().begin();
em.persist(je);
em.getTransaction().commit();
}catch (Exception e){
e.printStackTrace();
em.getTransaction().rollback();
}

assert (em.createQuery("select j.id from JsonEntity j", Long.class).getSingleResult())==1L;
}

    @AfterClass
public static void cleanResource(){
if(factory!=null)
factory.close();
}
}

Postgresql

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
/**
* Created by sanco on 29/09/2020.
* h2jsontest
*/
public class PostgreSQLTest {
private static EntityManagerFactory factory;
private static ObjectMapper mapper;
@BeforeClass
public static void init(){
factory = Persistence.createEntityManagerFactory("postgre");
mapper = new ObjectMapper();
}
@Test
public void jsonFieldTest(){
EntityManager em = factory.createEntityManager();
JsonEntity je = new JsonEntity();
je.setId(1L);
ObjectNode on = mapper.createObjectNode();
on.put("key", "value");
je.setAttributes(on);
try {
em.getTransaction().begin();
em.persist(je);
em.getTransaction().commit();
}catch (Exception e){
e.printStackTrace();
em.getTransaction().rollback();
}

assert (em.createQuery("select j.id from JsonEntity j", Long.class).getSingleResult())==1L;
}

    @AfterClass
public static void cleanResource(){
if(factory!=null)
factory.close();
}
}

I read lots of forms but the suggested solution does not work for me. I also debug the H2 source code and see that ValueJson.convertToJson throw exception because of value type not handle on switch case. Look at the source code of org.h2.value.Value.java

private ValueJson convertToJson() {
switch (getValueType()) {
case BOOLEAN:
return ValueJson.get(getBoolean());
case BYTE:
case SHORT:
case INT:
return ValueJson.get(getInt());
case LONG:
return ValueJson.get(getLong());
case FLOAT:
case DOUBLE:
case DECIMAL:
return ValueJson.get(getBigDecimal());
case BYTES:
case BLOB:
return ValueJson.fromJson(getBytesNoCopy());
case STRING:
case STRING_IGNORECASE:
case STRING_FIXED:
case CLOB:
return ValueJson.get(getString());
case GEOMETRY: {
ValueGeometry vg = (ValueGeometry) this;
return ValueJson.getInternal(GeoJsonUtils.ewkbToGeoJson(vg.getBytesNoCopy(), vg.getDimensionSystem()));
}
default:
throw getDataConversionError(Value.JSON);
}
}

getValueType return 19 which means JAVA_OBJECT. How can I handle this problem?

I'm using Spring Boot with Liquibase and this library (https://github.com/vladmihalcea/hibernate-types) to accomplish this. And this article to accomplish this https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/.

The first step is have a Liquibase change set that only runs in test mode to treat JSONB column types as JSON. H2 supports the latter but not the former.

This is the first change set that runs, and it is only targeted if the context is test and if the target DB is H2.

<changeSet id="0" author="psc" context="test" dbms="h2">
  <sql>
    CREATE TYPE IF NOT EXISTS "JSONB" as json;
  </sql>
</changeSet>

Contents of application-test.properties

spring.liquibase.contexts=test

My test entity. See How to map json article to better understand the annotations that were used.

import com.vladmihalcea.hibernate.type.json.JsonType;
import lombok.*;
import net.energyhub.testcontainers.dto.CustomerData;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

import javax.persistence.*;

@Entity
@Table(name = "customers")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@TypeDefs({
        @TypeDef(name = "json", typeClass = JsonType.class)
})
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    @Type(type = "json")
    @Column(columnDefinition = "jsonb")
    private CustomerData customerDetails;
}

Example of a test that exercises the JSON column. I'm making use of Spring Boot's DataJPATest test slice feature.

@DataJpaTest
@ActiveProfiles("test")
public class CustomerServiceH2Test {
    @Autowired
    private CustomerRepository customerRepository;

    @Test
    public void testFindAll() {
        Customer customer = Customer.builder()
                .name("Phil Calouche")
                .customerDetails(CustomerData.builder().x("x-value").build())
                .build();
        customerRepository.save(customer);

        assertThat(customerRepository.findAll()).hasSize(4);
    }
}