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.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.