SpringBoot项目中对数据库中json格式数据的操作（开发小记）

首先我们需要在pom.xml引入依赖：

<!--JSONObject依赖 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>fastjson</artifactId>
  <version>1.2.68</version>
</dependency>

创建一个处理类JsonTypeHandler

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JsonTypeHandler<T extends Object> extends BaseTypeHandler<T> {
    private static final ObjectMapper mapper = new ObjectMapper();
    private Class<T> clazz;

    public JsonTypeHandler(Class<T> clazz) {
        if (clazz == null) {
            throw new IllegalArgumentException("类型参数不能为空");
        }
        this.clazz = clazz;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps,
                                    int i,
                                    T parameter,
                                    JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }


    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.toObject(rs.getString(columnName), clazz);
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.toObject(rs.getString(columnIndex), clazz);
    }



    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex), clazz);
    }

    private String toJson(T object) {
        try {
            return mapper.writeValueAsString(object);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private T toObject(String content, Class<?> clazz) {
        if (content != null && !content.isEmpty()) {
            try {
                return (T) mapper.readValue(content, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }


}

在实体类中，我们将数据库中的json格式定义为List类型数据

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.List;

/**
 * 
 * @author Master_Tang
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class OkrItem implements Serializable {
    private static final long serialVersionUID = 1L;

    //    项目id
    private String itemId;

    //    考核id
    private String assessId;

    //    价值观
    private String itemValues;

    //    诠释
    private String itemNote;

    //    行为描述 
    private List<BehaviorPrin> itemBehavior;//这里的BehaviorPrin可以定义成你想要的Json数据格式

    //    评分原则
    private String itemPrinciple;

}


/
对应的BehaviorPrin类
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class BehaviorPrin implements Serializable {
    private static final long serialVersionUID = 1L;
    //    行为描述
    private String behavior;
    //    评分原则
    private String principle;
}

接下来，我们就可以在Dao层Mapper中使用了

增：

@Insert("insert into okr_item " +
            "(assess_id, item_values, item_note, item_behavior, item_principle) " +
            "values(#{itemId}, #{assessId}, #{itemValues}, #{itemNote}, " +
            "#{itemBehavior,typeHandler=com.路径.JsonTypeHandler}," +
            " #{itemPrinciple})")
int templateSelectInsert(OkrItem item);

改：

@Update("update okr_item set " +
            "item_values=#{itemValues}," +
            "item_note=#{itemNote}," +
            "item_behavior=#{itemBehavior,typeHandler=com.fcwr.typehandler.JsonTypeHandler}," +
            "item_principle=#{itemPrinciple} where item_id=#{itemId}")
int templateSelectUpadte(OkrItem item);

查：

@Select("select * from okr_item where assess_id=#{assessId}")
    @Results(id = "itemMap", value = {
            @Result(column = "item_behavior", property = "itemBehavior", typeHandler = com.fcwr.typehandler.JsonTypeHandler.class),
    })
List<OkrItem> itemListFindByAssessId(String assessId);

数据表结构：