如何将数据库中json格式的列值映射到java对象的属性中

前言

mysql5.7版本之后,列值的类型支持json格式,那么如何将json格式的字段类型的值映射到java对象当中呢?以下记录一下转换方法.
数据库展示:

specs列为json格式,现将此列的值映射到java的对象的属性当中
分析json串,需要创建一个VO对象,用于保存json串中的对象

public class Spec implements Serializable {
    private Integer key_id;
    private String key;
    private Integer value_id;
    private String value;
}

创建Pojo对象

@Entity
@Table(name = "sku")
@Getter
@Setter
public class SkuEntity extends BaseEntity {
    @Id
    private int id;
    private BigDecimal price;
    private BigDecimal discountPrice;
    private byte online;
    private String img;
    private String title;
    private int spuId;
    //private List<Spec>specs;
    private String specs;
    private String code;
    private int stock;
    private Integer categoryId;
    private Integer rootCategoryId;

GenericAndJson工具类代码:

package com.my.sevencell.api.utils;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.my.sevencell.api.exception.http.ServerErrorException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.IOException;

/**
 * @Description: 此工具类进行json的序列化和反序列化
 * @Author: my
 * @CreateDate: 2020/3/5 22:08
 * @UpdateUser:
 * @UpdateDate: 2020/3/5 22:08
 * @UpdateRemark: 修改内容
 * @Version: 1.0
 */
@Component
@Slf4j
public class GenericAndJson {
    private static ObjectMapper mapper;

    /**
     * 采用set方法自动注入
     * @param mapper
     */
    @Autowired
    public void setMapper(ObjectMapper mapper) {
        GenericAndJson.mapper = mapper;
    }

    public static<T> String objectToJson(T o){
        try {
            String jsonString = mapper.writeValueAsString(o);
            return jsonString;
        } catch (JsonProcessingException e) {
            log.error(e.getMessage());
            throw new ServerErrorException(9999);
        }
    }
    public static<T> T jsonToObject(String json, TypeReference<T>typeReference){
        try {
            if (json.isEmpty()){
                return null;
            }
            T t = mapper.readValue(json, typeReference);
            return t;
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new ServerErrorException(9999);
        }
    }
}

方法一

1.我们将specs列的值当做String类型进行接收

   private String specs;
  

2.编写specs的get/set方法,通过GenericAndJson工具类进行序列化和烦序列化

 public List<SpecVO> getSpecs() {
        if(this.specs.isEmpty()){
            return Collections.emptyList();
        }
        List<SpecVO> specs = GenericAndJson.jsonToObject(this.specs, new TypeReference<List<SpecVO>>() {
        });
        return specs;
    }

    public void setSpecs(List<SpecVO> specs) {
        String json = GenericAndJson.objectToJson(specs);
        this.specs = json;
    }

测试结果:

{
    "id": 1,
    "price": 3999.00,
    "discount_price": null,
    "online": 1,
    "img": "http://xxxxx",
    "title": "复古双色沙发（藏青色）",
    "spu_id": 1,
    "specs": [
        {
            "key_id": 1,
            "key": "颜色",
            "value_id": 2,
            "value": "藏青色"
        },
        {
            "key_id": 7,
            "key": "双色沙发尺寸（非标）",
            "value_id": 32,
            "value": "1.5米 x 1米"
        }
    ],
    "code": "1$1-2#7-32",
    "stock": 89,
    "category_id": 35,
    "root_category_id": null
}

方法二

1.新建工具类ConveterObjectAndJson,实现AttributeConverter接口

package com.my.sevencell.api.utils;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.my.sevencell.api.exception.http.ServerErrorException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;

import javax.persistence.AttributeConverter;
import java.io.IOException;

/**
 * @Description: 实现json的序列化和反序列化,只需要对需要转换的
 * 属性打上@convet主机即可
 * @Author: my
 * @CreateDate: 2020/3/6 9:32
 * @UpdateUser:
 * @UpdateDate: 2020/3/6 9:32
 * @UpdateRemark: 修改内容
 * @Version: 1.0
 */
@Slf4j
public class ConveterObjectAndJson<T> implements AttributeConverter<T,String> {
    @Autowired
    private ObjectMapper objectMapper;
    @Override
    public String convertToDatabaseColumn(T t) {
        try {
            String jsonString = objectMapper.writeValueAsString(t);
            return jsonString;
        } catch (JsonProcessingException e) {
            log.error(e.getMessage());
            throw new ServerErrorException(9999);
        }
    }

    @Override
    public T convertToEntityAttribute(String s) {
        try {
            if(s.isEmpty()){
                return null;
            }
            T t = objectMapper.readValue(s, new TypeReference<T>() {
            });
            return t;
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new ServerErrorException(9999);
        }
    }
}

2.我们将specs列的值当用List集合进行接收,并打上@Convert(converter = ConveterObjectAndJson.class)的注解

注意:
1.specs属性的get/set方法可自动生成,无需进行任何改动.
2.用List<specVo>接收时,idea会报错,不过不影响正常运行,可忽略
3.@Conver是avax.persistence包下的;

@Convert(converter = ConveterObjectAndJson.class)
private List<SpecVO> specs;

测试结果:

{
    "id": 1,
    "price": 3999.00,
    "discount_price": null,
    "online": 1,
    "img": "http://xxxx",
    "title": "复古双色沙发（藏青色）",
    "spu_id": 1,
    "specs": [
        {
            "key": "颜色",
            "value": "藏青色",
            "key_id": 1,
            "value_id": 2
        },
        {
            "key": "双色沙发尺寸（非标）",
            "value": "1.5米 x 1米",
            "key_id": 7,
            "value_id": 32
        }
    ],
    "code": "1$1-2#7-32",
    "stock": 89,
    "category_id": 35,
    "root_category_id": null
}