如何使用 H2、JPA 和 Hibernate 映射 JSON 列 [英] How to map a JSON column with H2, JPA, and Hibernate

问题描述

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

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:

这是实体类.

@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;
    }
}

和子类:

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 中添加了 JSON 支持，版本为 1.4.200 (2019-10-14).

JSON support was added to H2 after the question was asked, with version 1.4.200 (2019-10-14).

但是，您很少需要数据库中的 JSON 数据类型.JSON 本质上只是一个可能很长的字符串，因此您可以使用在大多数数据库中都可用的 CLOB.

However, you rarely need a JSON data type in a database. JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.

如果您需要对它们进行操作的 SQL 函数，那么您确实需要 JSON 数据类型，并且仅当数据库坚持其 JSON 函数对 JSON 类型而不是 CLOB 进行操作时才需要.不过，这些函数往往依赖于数据库.

You do need the JSON data type 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. Such functions tend to be database-dependent though.

这篇关于如何使用 H2、JPA 和 Hibernate 映射 JSON 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！