首页 前端知识 【Mybatis整合mysql之Json类型属性适配&&手把手】

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

2024-05-10 22:05:40 前端知识 前端哥 525 96 我要收藏

【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小时给我安排任务,阿达。。。
作为小小程序员,最近也开始研究“伏羲一画开天下”,八卦图,哈哈【乾三连,坤六断,震仰盂,艮覆碗,离中空,坎中满,兑上缺,巽下断】,当我慢慢深入发现,没有比程序员更适合学习《易经》的人了,因为我们的逻辑思维很强呀,,哈哈,我去上个厕所,下期见

转载请注明出处或者链接地址:https://www.qianduange.cn//article/8122.html
标签
mybatis
评论
发布的文章
大家推荐的文章
会员中心 联系我 留言建议 回顶部
复制成功!