如何將資料庫中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": "",
    "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": "",
    "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
}