MyBaits大字段类型写入MySql数据库

使用场景：

前端存储信息小而多，前端传到后台的是json格式，读取的时候也是json格式。
而且通常这个json作为一个整体使用，后台基本上不会单独去读取使用这个json中的具体值
那么这个时候就可以考虑将整个json存入表字段中。要用的时候直接取出即可。
省去，拆解，组装的过程。减小建表的数量

1.数据字段类型

`RULE_JSON_CLOB` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '规则字段2',

 

2.实体对象属性

private Object ruleJsonClob;

public Object getResultJsonClob() {
        return resultJsonClob;
    }

    public void setResultJsonClob(Object resultJsonClob) {
        this.resultJsonClob = resultJsonClob;
    }

3.  ..Mapper.xml内容

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ovit.kjxx.jcyjserver.mapper.CheckupResultMapper">
  <resultMap id="BaseResultMap" type="com.xxx.kjxx.jcyjserver.pojo.CheckupResult">
    <result column="ID" jdbcType="VARCHAR" property="id" />
    <result column="PLAN_ID" jdbcType="VARCHAR" property="planId" />
    <result column="MODULE_ID" jdbcType="VARCHAR" property="moduleId" />
    <result column="REMARK" jdbcType="VARCHAR" property="remark" />
    <result column="RESULT_JSON_CLOB" jdbcType="OTHER" property="resultJsonClob" typeHandler="com.xxx.kjxx.jcyjserver.config.JsonTypeHandler"/>
  </resultMap>
  <insert id="insert" parameterType="com.xxx.kjxx.jcyjserver.pojo.CheckupResult">
    insert into CHECKUP_RESULT (ID, PLAN_ID, MODULE_ID, 
      REMARK, RESULT_JSON_CLOB)
    values (#{id,jdbcType=VARCHAR}, #{planId,jdbcType=VARCHAR}, #{moduleId,jdbcType=VARCHAR},
      #{remark,jdbcType=VARCHAR}, #{resultJsonClob,jdbcType=CLOB,typeHandler=com.xxx.kjxx.jcyjserver.config.JsonTypeHandler})
  </insert>
  <select id="selectAll" resultMap="BaseResultMap">
    select ID, PLAN_ID, MODULE_ID, REMARK, RESULT_JSON_CLOB
    from CHECKUP_RESULT
  </select>
  <select id="findOne" resultType="com.xxx.kjxx.jcyjserver.pojo.CheckupResult">
    select ID, PLAN_ID, MODULE_ID, REMARK, RESULT_JSON_CLOB
    from CHECKUP_RESULT where id = #{id,jdbcType=VARCHAR}
  </select>
</mapper>

前端传参，ruleJsonClob 内容直接就是json对象,注意不是String

 

4.BaseTypeHandler类

import com.alibaba.fastjson.JSON;
import com.ovit.kjxx.jcyjserver.dto.PGobject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;

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

/**
 * describe:
 *
 * @author liuli
 * @date 2019/02/13
 */
@MappedTypes(Object.class)
public class JsonTypeHandler extends BaseTypeHandler<Object> {  


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i,  JSON.toJSONString(parameter));
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        // 返回String
        return JSON.parse(rs.getString(columnName));
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return JSON.parse(rs.getString(columnIndex));
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return JSON.parse(cs.getString(columnIndex));
    }
}

5.controller类

@PostMapping
public RespInfo addOne(@RequestBody CheckupResult checkupResult){
    checkupResult.setId("2");
    checkupResultService.addOne( checkupResult );
    return RespInfo.successResult(checkupResult, Consts.STATUS_SUCCESS );
}

@GetMapping("/{id}")
public RespInfo findOne(@PathVariable String id){
    CheckupResult checkupResult = checkupResultService.findOne(id);
    //这里要转一个下,否则当成字符串了
    JSONObject obj= JSON.parseObject(checkupResult.getResultJsonClob().toString());
    checkupResult.setResultJsonClob( obj );
    return RespInfo.successResult(checkupResult, Consts.STATUS_SUCCESS );
}

返回类说明:

RespInfo 包含三个参数  status 状态码, content 返回的具体类容, message 操作的说明,提示信息.

这个大家可以自行创建

 

6.PostMan测试

1. 添加接口测试

http://127.0.0.1:8089/jcyj-server/api/checkup

 {
    "id": "2",
    "planId": "2",
    "moduleId": "1",
    "remark": "检查结果第一模块内容",
    "resultJsonClob": {
      "totalScore": 80,
      "upToStandard": 5,
      "notToStandard": 2,
      "statistics": [
        {
          "indexName": "创新",
          "socre": 1
        },
        {
          "indexName": "安全",
          "socre": 2
        },
        {
          "indexName": "共享",
          "socre": 3
        },
        {
          "indexName": "开发",
          "socre": 4
        },
        {
          "indexName": "绿色",
          "socre": 5
        }
      ]
    }
  }

1. 查询接口测试

{

    "status": 1,

    "content": {

        "id": "2",

        "planId": "2",

        "moduleId": "1",

        "remark": "检查结果第一模块内容",

        "resultJsonClob": {

            "upToStandard": 5,

            "notToStandard": 2,

            "totalScore": 80,

            "statistics": [

                {

                    "socre": 1,

                    "indexName": "创新"

                },

                {

                    "socre": 2,

                    "indexName": "安全"

                },

                {

                    "socre": 3,

                    "indexName": "共享"

                },

                {

                    "socre": 4,

                    "indexName": "开发"

                },

                {

                    "socre": 5,

                    "indexName": "绿色"

                }

            ]

        }

    },

    "message": "操作成功"

}