mysql5.7支持json，mybatis不支持json

mybatis提供了解决方案，就是自定义 typeHandler,然后再 sql 那里指定 你要使用 的typeHandler
下面是我代码的配置：

<?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.much.shopmanager.dao.TSkuDao">

    <resultMap type="com.much.shopmanager.entity.TSku" id="TSkuMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="spuId" column="spu_id" jdbcType="INTEGER"/>
        <result property="title" column="title" jdbcType="VARCHAR"/>
        <result property="image" column="image" jdbcType="VARCHAR"/>
        <result property="price" column="price" jdbcType="NUMERIC"/>
        <result property="param" column="param" jdbcType="OTHER" typeHandler="com.much.shopmanager.config.JsonConverter"/>
        <result property="saleable" column="saleable" jdbcType="VARCHAR"/>
        <result property="valid" column="valid" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
        <result property="isDeleted" column="is_deleted" jdbcType="OTHER"/>
    </resultMap>

    <!--查询单个-->
    <select id="queryById" resultMap="TSkuMap">
        select
          id, spu_id, title, image, price, param, saleable, valid, create_time, last_update_time, is_deleted
        from lyr_shop_manager.t_sku
        where id = #{id}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="TSkuMap">
        select
          id, spu_id, title, image, price, param, saleable, valid, create_time, last_update_time, is_deleted
        from lyr_shop_manager.t_sku
        limit #{offset}, #{limit}
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="TSkuMap">
        select
          id, spu_id, title, image, price, param, saleable, valid, create_time, last_update_time, is_deleted
        from lyr_shop_manager.t_sku
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="spuId != null">
                and spu_id = #{spuId}
            </if>
            <if test="title != null and title != ''">
                and title = #{title}
            </if>
            <if test="image != null and image != ''">
                and image = #{image}
            </if>
            <if test="price != null">
                and price = #{price}
            </if>
            <if test="param != null">
                and param = #{param,jdbcType=java.util.Map}
            </if>
            <if test="saleable != null and saleable != ''">
                and saleable = #{saleable}
            </if>
            <if test="valid != null">
                and valid = #{valid}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
            <if test="lastUpdateTime != null">
                and last_update_time = #{lastUpdateTime}
            </if>
            <if test="isDeleted != null">
                and is_deleted = #{isDeleted}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into lyr_shop_manager.t_sku(spu_id, title, image, price, param, saleable, valid, create_time, last_update_time, is_deleted)
        values (#{spuId}, #{title}, #{image}, #{price}, #{param,typeHandler=com.much.shopmanager.config.JsonConverter}, #{saleable}, #{valid}, #{createTime}, #{lastUpdateTime}, #{isDeleted})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update lyr_shop_manager.t_sku
        <set>
            <if test="spuId != null">
                spu_id = #{spuId},
            </if>
            <if test="title != null and title != ''">
                title = #{title},
            </if>
            <if test="image != null and image != ''">
                image = #{image},
            </if>
            <if test="price != null">
                price = #{price},
            </if>
            <if test="param != null">
                param = #{param,jdbcType=OTHER,typeHandler=com.much.shopmanager.config.JsonConverter},
            </if>
            <if test="saleable != null and saleable != ''">
                saleable = #{saleable},
            </if>
            <if test="valid != null">
                valid = #{valid},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="lastUpdateTime != null">
                last_update_time = #{lastUpdateTime},
            </if>
            <if test="isDeleted != null">
                is_deleted = #{isDeleted},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete from lyr_shop_manager.t_sku where id = #{id}
    </delete>

</mapper>

自定义的 typeHandler

package com.much.shopmanager.config;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

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

/**
 * @Author lyr
 * @create 2020/6/7 1:17
 */
@MappedTypes(Map.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JsonConverter extends BaseTypeHandler<JSONObject> {
    /**
     * 设置非空参数
     * @param ps
     * @param i
     * @param parameter
     * @param jdbcType
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, String.valueOf(parameter.toJSONString()));
    }

    /**
     * 根据列名，获取可以为空的结果
     * @param rs
     * @param columnName
     * @return
     * @throws SQLException
     */
    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String sqlJson = rs.getString(columnName);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }

    /**
     * 根据列索引，获取可以为空的结果
     * @param rs
     * @param columnIndex
     * @return
     * @throws SQLException
     */
    @Override
    public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String sqlJson = rs.getString(columnIndex);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }

    @Override
    public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String sqlJson = cs.getString(columnIndex);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }
}

pojo的实体类：

package com.much.shopmanager.entity;

import com.alibaba.fastjson.JSONObject;

import java.util.Date;
import java.io.Serializable;

/**
 * (TSku)实体类
 *
 * @author makejava
 * @since 2020-06-07 00:53:57
 */
public class TSku implements Serializable {
    private static final long serialVersionUID = 312574170576826955L;
    
    private Integer id;
    
    private Integer spuId;
    
    private String title;
    
    private String image;
    
    private Double price;
    
    private JSONObject param;
    
    private String saleable;
    
    private Integer valid;
    
    private Date createTime;
    
    private Date lastUpdateTime;
    
    private Integer isDeleted;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getSpuId() {
        return spuId;
    }

    public void setSpuId(Integer spuId) {
        this.spuId = spuId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public Object getParam() {
        return param;
    }

    public void setParam(JSONObject param) {
        this.param = param;
    }

    public String getSaleable() {
        return saleable;
    }

    public void setSaleable(String saleable) {
        this.saleable = saleable;
    }

    public Integer getValid() {
        return valid;
    }

    public void setValid(Integer valid) {
        this.valid = valid;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getLastUpdateTime() {
        return lastUpdateTime;
    }

    public void setLastUpdateTime(Date lastUpdateTime) {
        this.lastUpdateTime = lastUpdateTime;
    }

    public Object getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Object isDeleted) {
        this.isDeleted = isDeleted;
    }

}