title: persistence jsonb field to h2 using hibernate
body: <p><a href="https://github.com/sakitA/h2jsonb" rel="nofollow noreferrer">source code</a></p>
  <p>I want to use <code>jsonb</code> column type. When I used <code>postgresql</code> there is no problem. But when I use <code>H2</code> I can not persist my entity. Native sql works but when saving <code>EntityManager.persist</code>  i got below error</p>
  <pre><code>ERROR: Data conversion error converting &quot;X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: &quot;&quot;attributes&quot;&quot; &quot;&quot;JSONB&quot;&quot;)&quot;; 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 &quot;X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: &quot;&quot;attributes&quot;&quot; &quot;&quot;JSONB&quot;&quot;)&quot;; 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 &quot;OTHER to JSON&quot; [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 &quot;OTHER to JSON&quot; [22018-200]
      at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
      at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
      ... 65 more
  </code></pre>
  <p>Here is my simple project.</p>
  <p><strong>Custom hibernate type for jsonb</strong></p>
  <pre><code>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(&quot;Failed to convert jsonb to JsonNode: &quot; + 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(&quot;Failed to convert JsonNode to jsonb: &quot; + 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;
      }
  }
  </code></pre>
  <p><strong>my entity class</strong></p>
  <pre><code>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=&quot;json_entities&quot;)
  public class JsonEntity {
      @Id
      private Long id;
  
      @Type(type = &quot;PGJsonType&quot;)
      @Column(columnDefinition = &quot;jsonb&quot;)
      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;
      }
  }
  </code></pre>
  <p><strong>persistence unit</strong></p>
  <pre><code>&lt;persistence xmlns=&quot;http://java.sun.com/xml/ns/persistence&quot;
               xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
               xsi:schemaLocation=&quot;http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd&quot;
               version=&quot;2.0&quot;&gt;
  
      &lt;persistence-unit name=&quot;postgre&quot;&gt;
          &lt;description&gt;
              Hibernate using JPA
          &lt;/description&gt;
  
          &lt;class&gt;JsonEntity&lt;/class&gt;
          &lt;properties&gt;
              &lt;property name=&quot;hibernate.connection.url&quot; value=&quot;jdbc:postgresql://localhost:5432/postgres&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.driver_class&quot; value=&quot;org.postgresql.Driver&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.username&quot; value=&quot;postgres&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.password&quot; value=&quot;s2351910&quot;/&gt;
              &lt;property name=&quot;hibernate.dialect&quot; value=&quot;org.hibernate.dialect.PostgreSQL10Dialect&quot;/&gt;
              &lt;property name=&quot;hibernate.temp.use_jdbc_metadata_defaults&quot;
                        value=&quot;false&quot;/&gt;
              &lt;property name=&quot;hibernate.show_sql&quot; value=&quot;true&quot;/&gt;
              &lt;property name=&quot;hibernate.hbm2ddl.auto&quot; value=&quot;create-drop&quot;/&gt;
              &lt;property name=&quot;hibernate.enable_lazy_load_no_trans&quot; value=&quot;true&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.provider_class&quot;
                        value=&quot;org.hibernate.hikaricp.internal.HikariCPConnectionProvider&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.minimumIdle&quot; value=&quot;5&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.maximumPoolSize&quot; value=&quot;20&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.idleTimeout&quot; value=&quot;45000&quot;/&gt;
  
          &lt;/properties&gt;
  
      &lt;/persistence-unit&gt;
  
      &lt;persistence-unit name=&quot;h2&quot;&gt;
          &lt;description&gt;
              Hibernate using JPA
          &lt;/description&gt;
  
          &lt;class&gt;JsonEntity&lt;/class&gt;
          &lt;properties&gt;
              &lt;property name=&quot;hibernate.connection.url&quot; value=&quot;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 &amp;quot;JSONB&amp;quot; AS json;&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.driver_class&quot; value=&quot;org.h2.Driver&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.username&quot; value=&quot;sa&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.password&quot; value=&quot;&quot;/&gt;
              &lt;property name=&quot;hibernate.dialect&quot; value=&quot;org.hibernate.dialect.H2Dialect&quot;/&gt;
              &lt;property name=&quot;hibernate.temp.use_jdbc_metadata_defaults&quot;
                        value=&quot;false&quot;/&gt;
              &lt;property name=&quot;hibernate.show_sql&quot; value=&quot;true&quot;/&gt;
              &lt;property name=&quot;hibernate.hbm2ddl.auto&quot; value=&quot;create-drop&quot;/&gt;
              &lt;property name=&quot;hibernate.enable_lazy_load_no_trans&quot; value=&quot;true&quot;/&gt;
              &lt;property name=&quot;hibernate.connection.provider_class&quot;
                        value=&quot;org.hibernate.hikaricp.internal.HikariCPConnectionProvider&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.minimumIdle&quot; value=&quot;5&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.maximumPoolSize&quot; value=&quot;20&quot;/&gt;
              &lt;property name=&quot;hibernate.hikari.idleTimeout&quot; value=&quot;60000&quot;/&gt;
  
          &lt;/properties&gt;
  
      &lt;/persistence-unit&gt;
  
  &lt;/persistence&gt;
  </code></pre>
  <p><strong>test classes</strong>
  <strong>H2</strong></p>
  <pre><code>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(&quot;h2&quot;);
          mapper = new ObjectMapper();
      }
  
      @Test
      public void jsonFieldTest(){
          EntityManager em = factory.createEntityManager();
  
          JsonEntity je = new JsonEntity();
          je.setId(1L);
  
          ObjectNode on = mapper.createObjectNode();
          on.put(&quot;key&quot;, &quot;value&quot;);
          je.setAttributes(on);
  
          try {
              em.getTransaction().begin();
              em.persist(je);
              em.getTransaction().commit();
  
              
          }catch (Exception e){
              e.printStackTrace();
              em.getTransaction().rollback();
          }
  </code></pre>
  <p>assert (em.createQuery(&quot;select j.id from JsonEntity j&quot;, Long.class).getSingleResult())==1L;
  }</p>
  <pre><code>    @AfterClass
      public static void cleanResource(){
          if(factory!=null)
              factory.close();
      }
  }
  </code></pre>
  <p><strong>Postgresql</strong></p>
  <pre><code>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(&quot;postgre&quot;);
          mapper = new ObjectMapper();
      }
  
      @Test
      public void jsonFieldTest(){
          EntityManager em = factory.createEntityManager();
  
          JsonEntity je = new JsonEntity();
          je.setId(1L);
  
          ObjectNode on = mapper.createObjectNode();
          on.put(&quot;key&quot;, &quot;value&quot;);
          je.setAttributes(on);
  
          try {
              em.getTransaction().begin();
              em.persist(je);
              em.getTransaction().commit();
  
              
          }catch (Exception e){
              e.printStackTrace();
              em.getTransaction().rollback();
          }
  </code></pre>
  <p>assert (em.createQuery(&quot;select j.id from JsonEntity j&quot;, Long.class).getSingleResult())==1L;
  }</p>
  <pre><code>    @AfterClass
      public static void cleanResource(){
          if(factory!=null)
              factory.close();
      }
  }
  </code></pre>
  <p>I read lots of forms but the suggested solution does not work for me. I also debug the <code>H2</code> source code and see that <code>ValueJson.convertToJson</code> throw exception because of value type not handle on switch case. Look at the source code of <a href="https://github.com/h2database/h2database/blob/e1bcf3b6ba8927ff640299c879011f2df0750ef9/h2/src/main/org/h2/value/Value.java#L1412" rel="nofollow noreferrer">org.h2.value.Value.java</a></p>
  <pre><code>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);
          }
      }
  </code></pre>
  <p><code>getValueType</code> return <code>19</code> which means <code>JAVA_OBJECT</code>. How can I handle this problem?</p>