H2中如何解决JSON列 [英] How can solve JSON column in H2

问题描述

我在应用程序MySQL 5.7中使用，我有JSON列。当我尝试运行集成测试时不起作用，因为H2数据库无法创建表。这是错误：

  2016-09-21 16：35：29.729错误10981 --- [main] org.hibernate .tool.hbm2ddl.SchemaExport：HHH000389：Unsuccessful：create table payment_transaction（id bigint默认生成为identity，creation_date timestamp not null，载荷json，周期整数，public_id varchar（255）非null，状态varchar（255）非null， $ _ 
 2016-09-21 16：35：29.730错误10981 --- [main] org.hibernate（）varchar（255），type varchar（255）not null，user_id bigint不为null，主键（id） .tool.hbm2ddl.SchemaExport：未知数据类型：JSON; SQL语句：
  

这是实体类。

  @Table（name =payment_transaction）
 public class PaymentTransaction extends DomainObject实现Serializable {
 
 @Convert（converter = JpaPayloadConverter.class） 
 @Column（name =payload，insertable = true，updatable = true，nullable = true，columnDefinition =json）
私有有效载荷; 
 
 public Payload getPayload（）{
 return payload; 
} 
 
 public void setPayload（Payload payload）{
 this.payload = payload; 
 
 
 $ / code $ / pre 
 
 和子类：
 
 
  public class Payload实现Serializable {
 
 private Long userId; 
私人SubscriptionType类型; 
 private String paymentId; 
私人列表< String> ratePlanId; 
私人整数周期; 
 
 public Long getUserId（）{
 return userId; 
} 
 
 public void setUserId（Long userId）{
 this.userId = userId; 
} 
 
 public SubscriptionType getType（）{
返回类型; 
} 
 
 public void setType（SubscriptionType类型）{
 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; 
} 
 
} 
  
此转换器用于插入数据库：
  public class JpaPayloadConverter实现了AttributeConverter< Payload，String> {
 
 // ObjectMapper是线程安全的
 private final ObjectMapper ObjectMapper = new ObjectMapper（）; 
 
 private Logger log = LoggerFactory.getLogger（getClass（））; 
 
 @Override 
 public String convertToDatabaseColumn（Payload属性）{
 String jsonString =; 
尝试{
 log.debug（Start convertToDatabaseColumn）; 
 
 //将POJO列表转换为json 
 jsonString = objectMapper.writeValueAsString（attribute）; 
 log.debug（convertToDatabaseColumn+ jsonString）; 
 $ b $ catch（JsonProcessingException ex）{
 log.error（ex.getMessage（））; 
} 
返回jsonString; 
} 
 
 @Override 
 Public Payload convertToEntityAttribute（String dbData）{
 
 Payload payload = new Payload（）; 
尝试{
 log.debug（Start convertToEntityAttribute）; 
 
 //将json转换为POJO列表
 payload = objectMapper.readValue（dbData，Payload.class）; 
 log.debug（JsonDocumentsConverter.convertToDatabaseColumn+ payload）; 
 
} catch（IOException ex）{
 log.error（ex.getMessage（））; 
} 
返回有效载荷; 
 
 
 $ b  
 
 
解决方案
 H2没有JSON数据类型。
 
 
 
 JSON本质上只是一个可能很长的字符串，所以您可以使用大多数数据库上可用的CLOB。 / p> 
 
 
只有在需要使用SQL函数对其进行操作时，才需要行级JSON类型，并且只有当数据库坚持其JSON函数以JSON类型运行时而不是在CLOB上。
 
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;

    }
}

解决方案
H2 does not have a JSON data type.

JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.

You need a JSON type at row level only 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.

                        
这篇关于H2中如何解决JSON列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！