【Mybatis整合mysql之Json类型属性适配&&手把手】

【Mybatis整合mysql之Json类型属性适配&&手把手】

场景

JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 、TEXT或LONGTEXT ）来保存 JSON 文档。在开发中发现，Mybatis查询Json字段时，发现不能映射，如下：

java实体：

@Data
public class ApiGatherProject extends Base {

    /**
     * id
     */
    private Long id;

    /**
     * 项目id
     */
    private Long projectId;

    /**
     * sap接口url
     */
    private String url;

    /**
     * 请求类型 0:GET 1:POST 2:PUT 3:DELETE
     */
    private Integer httpType;

    /**
     * 请求参数列表
     */
    private JSONArray requestParamList;

    /**
     * 检验条件 0:默认响应码200 1:自定义响应码 2:内容包含 3:内容不包含
     */
    private Integer checkCondition;

    /**
     * 检验内容
     */
    private String checkContent;

    /**
     * 路由
     */
    private String route;

    /**
     * api类型：0 自定义
     */
    private Integer apiType;

    /**
     * json类型:0  application/json
     */
    private Integer jsonType;
}

mybatis config.xml如下：

<mapper namespace="com.mapper.ApiGatherProjectMapper">
    <resultMap id="resultList" type="com.test.entity.ApiGatherProject">
        <id column="id" property="id"></id>
        <result column="project_id" property="projectId"></result>
        <result column="url" property="url"></result>
        <result column="http_type" property="httpType"></result>
        <result column="request_param_list" property="requestParamList"></result>
        <result column="check_condition" property="checkCondition"></result>
        <result column="check_content" property="checkContent"></result>
        <result column="api_type" property="apiType"></result>
        <result column="json_type" property="jsonType"></result>
        <result column="route" property="route"></result>
        <result column="creator_id" property="creatorId"></result>
        <result column="create_time" property="createTime"></result>
        <result column="modify_time" property="modifyTime"></result>
    </resultMap>
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO t_gather_api (project_id, url, http_type, request_param_list, check_condition, check_content, api_type, json_type, route)
        VALUES (#{projectId}, #{url}, #{httpType}, #{requestParamList}, #{checkCondition}, #{checkContent}, #{apiType}, #{jsonType}, #{route})
    </insert>
    <delete id="batchDeleteByProjectIds">
        DELETE FROM t_gather_api WHERE project_id IN
        <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
            #{projectId}
        </foreach>
    </delete>
    <delete id="batchDelete">
        DELETE FROM t_gather_api WHERE id IN
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
    <select id="getById" resultMap="resultList">
        SELECT * FROM t_gather_api WHERE id = #{id}
    </select>
    <select id="getByProjectId" resultMap="resultList">
        SELECT * FROM t_gather_api WHERE project_id = #{id}
    </select>

</mapper>

mysql数据库中，requestParamList字段有数据，如下：

对于JSONArray 类型 requestParamList字段，服务启动不了，报如下错误：

Caused by: java.lang.IllegalStateException: No typehandler found for property requestParamList
	at org.apache.ibatis.mapping.ResultMapping$Builder.validate(ResultMapping.java:151)
	at org.apache.ibatis.mapping.ResultMapping$Builder.build(ResultMapping.java:140)
	at org.apache.ibatis.builder.MapperBuilderAssistant.buildResultMapping(MapperBuilderAssistant.java:446)
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.buildResultMappingFromContext(XMLMapperBuilder.java:393)
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:280)
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:254)
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElements(XMLMapperBuilder.java:246)
	at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:119)

原因分析

Caused by: java.lang.IllegalStateException: No typehandler found for property requestParamList`

出现该异常，大致原因是是因为requestParamList属性的类型无法和mysql映射！
从该问题入手后，发现mybatis目前不支持Json字段的的处理，如下包：

org.apache.ibatis.type.*;

找不到Json的Handler，哦 my gay，好像找到问题了，我决定仿照org.apache.ibatis.type.ArrayTypeHandler自定义一个JsonTypeHandler，看到这里，是不是觉得我很牛逼，no no no，去掉牛！！！！

解决方案

自定义JsonTypeHandler：

package com.test.handler;

import org.apache.ibatis.type.*;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.springframework.context.annotation.Configuration;

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

@MappedTypes(JSONArray.class) // 指定映射的目标类型为 JSONArray
@Configuration
public class JsonTypeHandler extends BaseTypeHandler<JSONArray> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONArray parameter, JdbcType jdbcType) throws SQLException {
        String jsonString = JSON.toJSONString(parameter);
        ps.setString(i, jsonString);
    }

    @Override
    public JSONArray getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parseJson(rs.getString(columnName));
    }

    @Override
    public JSONArray getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parseJson(rs.getString(columnIndex));
    }

    @Override
    public JSONArray getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parseJson(cs.getString(columnIndex));
    }

    private JSONArray parseJson(String jsonString) {
        if (jsonString == null || "".equals(jsonString)) {
            return new JSONArray();
        } else {
            return JSON.parseArray(jsonString);
        }
    }
}

适配mbatis 的Xml文件中的，如下，主要关注request_param_list

<resultMap id="resultList" type="com.entity.ApiGatherProject">
        <id column="id" property="id"></id>
        <result column="project_id" property="projectId"></result>
        <result column="url" property="url"></result>
        <result column="http_type" property="httpType"></result>
        <result column="request_param_list" property="requestParamList"
                javaType="com.alibaba.fastjson.JSONArray" jdbcType="OTHER"
                typeHandler="com.cctegitc.warehouse.handler.JsonTypeHandler"></result>
        <result column="check_condition" property="checkCondition"></result>
        <result column="check_content" property="checkContent"></result>
        <result column="api_type" property="apiType"></result>
        <result column="json_type" property="jsonType"></result>
        <result column="route" property="route"></result>
        <result column="creator_id" property="creatorId"></result>
        <result column="create_time" property="createTime"></result>
        <result column="modify_time" property="modifyTime"></result>
    </resultMap>

重启服务，测试：

成功了！！！！！看着很简单吧，看到这里了，求个小关小赞吧，家人们 /笑。

写到最后

作为一个程序员，公认的都是很忙的，但是，我就很“闲”了，开发工作游刃有余，但就是太闲了，哈哈哈，因为我的领导也很闲呀，上班8小时，谝传4小时，上厕所2小时，接水1小时，剩余1小时给我安排任务，阿达。。。
作为小小程序员，最近也开始研究“伏羲一画开天下”，八卦图，哈哈【乾三连，坤六断，震仰盂，艮覆碗，离中空，坎中满，兑上缺，巽下断】，当我慢慢深入发现，没有比程序员更适合学习《易经》的人了，因为我们的逻辑思维很强呀，，哈哈，我去上个厕所，下期见