将持久化的 JSONB 字段通过 Hibernate 存储到 H2 数据库中。

huangapple
go评论108阅读模式
英文:

persistence jsonb field to h2 using hibernate

问题

I understand your concern. The issue you're facing seems to be related to H2's handling of the jsonb column type when compared to PostgreSQL. The error is occurring due to a data conversion error in the ValueJson.convertToJson() method, specifically when the value type is JAVA_OBJECT. Unfortunately, H2's handling of jsonb is not as seamless as PostgreSQL's, and this has caused difficulties for your application.

You've already identified the part of the H2 source code that is causing the problem. In the ValueJson.convertToJson() method, the switch statement handles various value types, but when the value type is JAVA_OBJECT, it throws a data conversion error.

It seems that H2 does not have native support for JSON or JSONB types, and therefore, handling them seamlessly like PostgreSQL is challenging. As a result, you might need to take a different approach to work around this issue.

Here are a few possible solutions you could consider:

  1. Change Data Type Handling: You could change your approach to how JSON is stored in H2. Instead of trying to map it to a custom Hibernate type, you could store JSON as a string in H2 and manually serialize/deserialize it in your application.

  2. Use PostgreSQL for Testing: Since you mentioned that your code works fine with PostgreSQL, you could consider using PostgreSQL as the database for your tests, even if you're primarily using H2 for development. This could help ensure that your code functions correctly with your desired JSONB column handling.

  3. Custom Type Handling in H2: If you're comfortable with contributing to open-source projects, you could consider submitting a pull request to the H2 project to enhance their support for JSONB types.

  4. Explore Other Embedded Databases: If you find that H2's limitations are hindering your development, you might explore other embedded databases that provide better support for JSON data types.

Remember that your choice of approach will depend on your specific project requirements and constraints. It's also worth keeping an eye on updates to the H2 database project to see if they address these issues in the future.

英文:

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

  1. ERROR: Data conversion error converting "X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: ""attributes"" ""JSONB"")"; SQL statement:
  2. insert into json_entities (attributes, id) values (?, ?) [22018-200]
  3. javax.persistence.RollbackException: Error while committing the transaction
  4. at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:81)
  5. at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:104)
  6. at H2Test.jsonFieldTest(H2Test.java:39)
  7. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  8. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  9. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  10. at java.lang.reflect.Method.invoke(Method.java:498)
  11. at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
  12. at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
  13. at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
  14. at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
  15. at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
  16. at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
  17. at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
  18. at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
  19. at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
  20. at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
  21. at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
  22. at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
  23. at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
  24. at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
  25. at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
  26. at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
  27. at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
  28. at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
  29. at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
  30. at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
  31. at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
  32. at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
  33. at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
  34. Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute statement
  35. at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
  36. at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
  37. at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:65)
  38. ... 29 more
  39. Caused by: org.hibernate.exception.DataException: could not execute statement
  40. at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
  41. at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
  42. at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
  43. at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
  44. at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
  45. at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3254)
  46. at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3779)
  47. at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
  48. at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
  49. at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
  50. at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
  51. at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
  52. at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
  53. at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
  54. at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
  55. at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1360)
  56. at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:451)
  57. at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3210)
  58. at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2378)
  59. at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
  60. at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
  61. at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
  62. at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
  63. at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
  64. ... 28 more
  65. Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "X'aced000574000f7b226b6579223a2276616c7565227d' (json_entities: ""attributes"" ""JSONB"")"; SQL statement:
  66. insert into json_entities (attributes, id) values (?, ?) [22018-200]
  67. at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
  68. at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
  69. at org.h2.message.DbException.get(DbException.java:194)
  70. at org.h2.table.Column.getDataConversionError(Column.java:409)
  71. at org.h2.table.Column.validateConvertUpdateSequence(Column.java:381)
  72. at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845)
  73. at org.h2.command.dml.Insert.insertRows(Insert.java:187)
  74. at org.h2.command.dml.Insert.update(Insert.java:151)
  75. at org.h2.command.CommandContainer.update(CommandContainer.java:198)
  76. at org.h2.command.Command.executeUpdate(Command.java:251)
  77. at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:191)
  78. at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:152)
  79. at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
  80. at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
  81. at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
  82. ... 47 more
  83. Caused by: org.h2.message.DbException: Data conversion error converting "OTHER to JSON" [22018-200]
  84. at org.h2.message.DbException.get(DbException.java:205)
  85. at org.h2.message.DbException.get(DbException.java:181)
  86. at org.h2.value.Value.getDataConversionError(Value.java:1504)
  87. at org.h2.value.Value.convertToJson(Value.java:1439)
  88. at org.h2.value.Value.convertTo(Value.java:861)
  89. at org.h2.value.Value.convertTo(Value.java:772)
  90. at org.h2.value.TypeInfo.cast(TypeInfo.java:515)
  91. at org.h2.table.Column.validateConvertUpdateSequence(Column.java:378)
  92. ... 57 more
  93. Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "OTHER to JSON" [22018-200]
  94. at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
  95. at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
  96. ... 65 more

Here is my simple project.

Custom hibernate type for jsonb

  1. import com.fasterxml.jackson.databind.JsonNode;
  2. import com.fasterxml.jackson.databind.ObjectMapper;
  3. import org.hibernate.HibernateException;
  4. import org.hibernate.engine.spi.SharedSessionContractImplementor;
  5. import org.hibernate.usertype.UserType;
  6. import java.io.Serializable;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  9. import java.sql.SQLException;
  10. import java.sql.Types;
  11. /**
  12. * Created by sanco on 29/09/2020.
  13. * h2jsontest
  14. */
  15. public class PGJsonType implements UserType {
  16. private final int CUSTOM_TYPE = Types.OTHER;
  17. private final static ObjectMapper jsonMapper = new ObjectMapper();
  18. @Override
  19. public int[] sqlTypes() {
  20. return new int[]{CUSTOM_TYPE};
  21. }
  22. @Override
  23. public Class returnedClass() {
  24. return JsonNode.class;
  25. }
  26. @Override
  27. public boolean equals(Object x, Object y) throws HibernateException {
  28. return x==null? y==null : ((JsonNode)x).equals((JsonNode)y);
  29. }
  30. @Override
  31. public int hashCode(Object x) throws HibernateException {
  32. return ((JsonNode)x).hashCode();
  33. }
  34. @Override
  35. public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
  36. final String cellContent = rs.getString(names[0]);
  37. if (cellContent == null) {
  38. return null;
  39. }
  40. try {
  41. return jsonMapper.readTree(cellContent);
  42. } catch (final Exception ex) {
  43. throw new RuntimeException("Failed to convert jsonb to JsonNode: " + ex.getMessage(), ex);
  44. }
  45. }
  46. @Override
  47. public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
  48. if (value == null) {
  49. st.setNull(index, CUSTOM_TYPE);
  50. return;
  51. }
  52. try {
  53. st.setObject(index, jsonMapper.writeValueAsString(value), CUSTOM_TYPE);
  54. } catch (final Exception ex) {
  55. throw new RuntimeException("Failed to convert JsonNode to jsonb: " + ex.getMessage(), ex);
  56. }
  57. }
  58. @Override
  59. public Object deepCopy(Object value) throws HibernateException {
  60. return value==null? null : ((JsonNode)value).deepCopy();
  61. }
  62. @Override
  63. public boolean isMutable() {
  64. return true;
  65. }
  66. @Override
  67. public Serializable disassemble(Object value) throws HibernateException {
  68. return (Serializable) value;
  69. }
  70. @Override
  71. public Object assemble(Serializable cached, Object owner) throws HibernateException {
  72. return cached;
  73. }
  74. @Override
  75. public Object replace(Object original, Object target, Object owner) throws HibernateException {
  76. return original;
  77. }
  78. }

my entity class

  1. import com.fasterxml.jackson.databind.JsonNode;
  2. import org.hibernate.annotations.Type;
  3. import javax.persistence.Column;
  4. import javax.persistence.Entity;
  5. import javax.persistence.Id;
  6. import javax.persistence.Table;
  7. /**
  8. * Created by sanco on 29/09/2020.
  9. * h2jsontest
  10. */
  11. @Entity
  12. @Table(name="json_entities")
  13. public class JsonEntity {
  14. @Id
  15. private Long id;
  16. @Type(type = "PGJsonType")
  17. @Column(columnDefinition = "jsonb")
  18. private JsonNode attributes;
  19. public void setId(Long id) {
  20. this.id = id;
  21. }
  22. public Long getId() {
  23. return id;
  24. }
  25. public JsonNode getAttributes() {
  26. return attributes;
  27. }
  28. public void setAttributes(JsonNode attributes) {
  29. this.attributes = attributes;
  30. }
  31. }

persistence unit

  1. <persistence xmlns="http://java.sun.com/xml/ns/persistence"
  2. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
  4. version="2.0">
  5. <persistence-unit name="postgre">
  6. <description>
  7. Hibernate using JPA
  8. </description>
  9. <class>JsonEntity</class>
  10. <properties>
  11. <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/postgres"/>
  12. <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
  13. <property name="hibernate.connection.username" value="postgres"/>
  14. <property name="hibernate.connection.password" value="s2351910"/>
  15. <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL10Dialect"/>
  16. <property name="hibernate.temp.use_jdbc_metadata_defaults"
  17. value="false"/>
  18. <property name="hibernate.show_sql" value="true"/>
  19. <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
  20. <property name="hibernate.enable_lazy_load_no_trans" value="true"/>
  21. <property name="hibernate.connection.provider_class"
  22. value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>
  23. <property name="hibernate.hikari.minimumIdle" value="5"/>
  24. <property name="hibernate.hikari.maximumPoolSize" value="20"/>
  25. <property name="hibernate.hikari.idleTimeout" value="45000"/>
  26. </properties>
  27. </persistence-unit>
  28. <persistence-unit name="h2">
  29. <description>
  30. Hibernate using JPA
  31. </description>
  32. <class>JsonEntity</class>
  33. <properties>
  34. <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;"/>
  35. <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
  36. <property name="hibernate.connection.username" value="sa"/>
  37. <property name="hibernate.connection.password" value=""/>
  38. <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
  39. <property name="hibernate.temp.use_jdbc_metadata_defaults"
  40. value="false"/>
  41. <property name="hibernate.show_sql" value="true"/>
  42. <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
  43. <property name="hibernate.enable_lazy_load_no_trans" value="true"/>
  44. <property name="hibernate.connection.provider_class"
  45. value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>
  46. <property name="hibernate.hikari.minimumIdle" value="5"/>
  47. <property name="hibernate.hikari.maximumPoolSize" value="20"/>
  48. <property name="hibernate.hikari.idleTimeout" value="60000"/>
  49. </properties>
  50. </persistence-unit>
  51. </persistence>

test classes
H2

  1. import com.fasterxml.jackson.databind.ObjectMapper;
  2. import com.fasterxml.jackson.databind.node.ObjectNode;
  3. import org.junit.AfterClass;
  4. import org.junit.BeforeClass;
  5. import org.junit.Test;
  6. import javax.persistence.EntityManager;
  7. import javax.persistence.EntityManagerFactory;
  8. import javax.persistence.Persistence;
  9. /**
  10. * Created by sanco on 29/09/2020.
  11. * h2jsontest
  12. */
  13. public class H2Test {
  14. private static EntityManagerFactory factory;
  15. private static ObjectMapper mapper;
  16. @BeforeClass
  17. public static void init(){
  18. factory = Persistence.createEntityManagerFactory("h2");
  19. mapper = new ObjectMapper();
  20. }
  21. @Test
  22. public void jsonFieldTest(){
  23. EntityManager em = factory.createEntityManager();
  24. JsonEntity je = new JsonEntity();
  25. je.setId(1L);
  26. ObjectNode on = mapper.createObjectNode();
  27. on.put("key", "value");
  28. je.setAttributes(on);
  29. try {
  30. em.getTransaction().begin();
  31. em.persist(je);
  32. em.getTransaction().commit();
  33. }catch (Exception e){
  34. e.printStackTrace();
  35. em.getTransaction().rollback();
  36. }

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

  1.     @AfterClass
  2. public static void cleanResource(){
  3. if(factory!=null)
  4. factory.close();
  5. }
  6. }

Postgresql

  1. import com.fasterxml.jackson.databind.ObjectMapper;
  2. import com.fasterxml.jackson.databind.node.ObjectNode;
  3. import org.junit.AfterClass;
  4. import org.junit.BeforeClass;
  5. import org.junit.Test;
  6. import javax.persistence.EntityManager;
  7. import javax.persistence.EntityManagerFactory;
  8. import javax.persistence.Persistence;
  9. /**
  10. * Created by sanco on 29/09/2020.
  11. * h2jsontest
  12. */
  13. public class PostgreSQLTest {
  14. private static EntityManagerFactory factory;
  15. private static ObjectMapper mapper;
  16. @BeforeClass
  17. public static void init(){
  18. factory = Persistence.createEntityManagerFactory("postgre");
  19. mapper = new ObjectMapper();
  20. }
  21. @Test
  22. public void jsonFieldTest(){
  23. EntityManager em = factory.createEntityManager();
  24. JsonEntity je = new JsonEntity();
  25. je.setId(1L);
  26. ObjectNode on = mapper.createObjectNode();
  27. on.put("key", "value");
  28. je.setAttributes(on);
  29. try {
  30. em.getTransaction().begin();
  31. em.persist(je);
  32. em.getTransaction().commit();
  33. }catch (Exception e){
  34. e.printStackTrace();
  35. em.getTransaction().rollback();
  36. }

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

  1.     @AfterClass
  2. public static void cleanResource(){
  3. if(factory!=null)
  4. factory.close();
  5. }
  6. }

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

  1. private ValueJson convertToJson() {
  2. switch (getValueType()) {
  3. case BOOLEAN:
  4. return ValueJson.get(getBoolean());
  5. case BYTE:
  6. case SHORT:
  7. case INT:
  8. return ValueJson.get(getInt());
  9. case LONG:
  10. return ValueJson.get(getLong());
  11. case FLOAT:
  12. case DOUBLE:
  13. case DECIMAL:
  14. return ValueJson.get(getBigDecimal());
  15. case BYTES:
  16. case BLOB:
  17. return ValueJson.fromJson(getBytesNoCopy());
  18. case STRING:
  19. case STRING_IGNORECASE:
  20. case STRING_FIXED:
  21. case CLOB:
  22. return ValueJson.get(getString());
  23. case GEOMETRY: {
  24. ValueGeometry vg = (ValueGeometry) this;
  25. return ValueJson.getInternal(GeoJsonUtils.ewkbToGeoJson(vg.getBytesNoCopy(), vg.getDimensionSystem()));
  26. }
  27. default:
  28. throw getDataConversionError(Value.JSON);
  29. }
  30. }

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

答案1

得分: 1

我使用Spring Boot与Liquibase，以及这个库（https://github.com/vladmihalcea/hibernate-types）来实现此功能。并且使用这篇文章（https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/）来完成此操作。

第一步是创建一个Liquibase变更集，仅在测试模式下运行，将JSONB列类型视为JSON。H2支持后者但不支持前者。

这是第一个变更集，仅在上下文为test且目标数据库为H2时运行。

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

application-test.properties的内容

  1. spring.liquibase.contexts=test

我的测试实体。请查看How to map json文章以更好地理解所使用的注解。

  1. import com.vladmihalcea.hibernate.type.json.JsonType;
  2. import lombok.*;
  3. import net.energyhub.testcontainers.dto.CustomerData;
  4. import org.hibernate.annotations.Type;
  5. import org.hibernate.annotations.TypeDef;
  6. import org.hibernate.annotations.TypeDefs;
  8. import javax.persistence.*;
  10. @Entity
  11. @Table(name = "customers")
  12. @Getter
  13. @Setter
  14. @NoArgsConstructor
  15. @AllArgsConstructor
  16. @Builder
  17. @TypeDefs({
  18.         @TypeDef(name = "json", typeClass = JsonType.class)
  19. })
  20. public class Customer {
  21.     @Id
  22.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  23.     private Integer id;
  24.     private String name;
  25.     @Type(type = "json")
  26.     @Column(columnDefinition = "jsonb")
  27.     private CustomerData customerDetails;
  28. }

演示一个测试，测试JSON列。我在这里使用了Spring Boot的DataJPATest测试切片功能。

  1. @DataJpaTest
  2. @ActiveProfiles("test")
  3. public class CustomerServiceH2Test {
  4.     @Autowired
  5.     private CustomerRepository customerRepository;
  7.     @Test
  8.     public void testFindAll() {
  9.         Customer customer = Customer.builder()
  10.                 .name("Phil Calouche")
  11.                 .customerDetails(CustomerData.builder().x("x-value").build())
  12.                 .build();
  13.         customerRepository.save(customer);
  15.         assertThat(customerRepository.findAll()).hasSize(4);
  16.     }
  17. }
英文:

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.

  1. &lt;changeSet id=&quot;0&quot; author=&quot;psc&quot; context=&quot;test&quot; dbms=&quot;h2&quot;&gt;
  2.   &lt;sql&gt;
  3.     CREATE TYPE IF NOT EXISTS &quot;JSONB&quot; as json;
  4.   &lt;/sql&gt;
  5. &lt;/changeSet&gt;

Contents of application-test.properties

  1. spring.liquibase.contexts=test

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

  1. import com.vladmihalcea.hibernate.type.json.JsonType;
  2. import lombok.*;
  3. import net.energyhub.testcontainers.dto.CustomerData;
  4. import org.hibernate.annotations.Type;
  5. import org.hibernate.annotations.TypeDef;
  6. import org.hibernate.annotations.TypeDefs;
  8. import javax.persistence.*;
  10. @Entity
  11. @Table(name = &quot;customers&quot;)
  12. @Getter
  13. @Setter
  14. @NoArgsConstructor
  15. @AllArgsConstructor
  16. @Builder
  17. @TypeDefs({
  18.         @TypeDef(name = &quot;json&quot;, typeClass = JsonType.class)
  19. })
  20. public class Customer {
  21.     @Id
  22.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  23.     private Integer id;
  24.     private String name;
  25.     @Type(type = &quot;json&quot;)
  26.     @Column(columnDefinition = &quot;jsonb&quot;)
  27.     private CustomerData customerDetails;
  28. }

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

  1. @DataJpaTest
  2. @ActiveProfiles(&quot;test&quot;)
  3. public class CustomerServiceH2Test {
  4.     @Autowired
  5.     private CustomerRepository customerRepository;
  7.     @Test
  8.     public void testFindAll() {
  9.         Customer customer = Customer.builder()
  10.                 .name(&quot;Phil Calouche&quot;)
  11.                 .customerDetails(CustomerData.builder().x(&quot;x-value&quot;).build())
  12.                 .build();
  13.         customerRepository.save(customer);
  15.         assertThat(customerRepository.findAll()).hasSize(4);
  16.     }
  17. }

huangapple
  • 本文由 发表于 2020年9月29日 20:19:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/64119491.html
  • h2
  • hibernate
  • java
  • jsonb
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定