mysql5.7使用json类型crud

使用json类型踩坑记要

1. 前言

需求存在需要类似mongodb一样动态存储字段进行扩展,如果直接存json字符串需要整存整取，查改起来比较麻烦。所以准备使用mysql5.7新特性json类型进行存储。

2.本文内容安排

• 建数据库表
• 创建java映射类
• 编写自定义typehandler
• 编写crud xml

3. 正文

3.1 建表

在处理JSON时，MySQL使用的utf8mb4字符集，utf8mb4是utf8和ascii的超集。  
由于历史原因，这里utf8并非是我们常说的UTF-8 Unicode变长编码方案，而是
MySQL自身定义的utf8编码方案，最长为三个字节。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `extra` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 建立类

我这里使用的是阿里的fastjson，直接使用jsonobject格式就好了

import com.alibaba.fastjson.JSONObject;
public class Test {
    private int id;
    private JSONObject extra;
    //setter ,getter 略
}

3.3 创建自定义typehandler

	先建立基类，然后建立jsonObject和jsonArray分别继承，我这里只使用了
	jsonObject

下面为待继承基类

package com.gzjp.commonservice.typehandler;

import com.alibaba.fastjson.JSON;
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;

/**
 * mybatis 转换基类
 * @since 2018-07-05 11:26:22
 */
public abstract class BaseJSONTypeHandler<E extends JSON> extends BaseTypeHandler<E> {

    public BaseJSONTypeHandler(Class<E> type) {
        if (type == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        }
    }

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, E parameter, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i, parameter.toString());
    }

    @Override
    public E getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        String jsonStr = resultSet.getString(columnName);
        if (resultSet.wasNull()) {
            return null;
        } else {
            return parseJSONString(jsonStr);
        }
    }

    @Override
    public E getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
        String jsonStr = resultSet.getString(columnIndex);
        if (resultSet.wasNull()) {
            return null;
        } else {
            return parseJSONString(jsonStr);
        }
    }

    @Override
    public E getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        String jsonStr = callableStatement.getString(columnIndex);
        if (callableStatement.wasNull()) {
            return null;
        } else {
            return parseJSONString(jsonStr);
        }
    }

    /**
     * 默认子类去实现
     *
     * @param jsonString
     * @return
     */
    protected abstract E parseJSONString(String jsonString);
}

下面为处理jsonObject类

package com.gzjp.commonservice.typehandler;

import com.alibaba.fastjson.JSONObject;


/**
 * mybatis json转换
 * @since 2018-07-05 11:26:22
 */
public class JSONTypeHandler extends BaseJSONTypeHandler<JSONObject> {

    public JSONTypeHandler(Class<JSONObject> type) {
        super(type);
    }

    /**
     * 默认子类去实现
     *
     * @param jsonString
     * @return
     */
    @Override
    protected JSONObject parseJSONString(String jsonString) {
        return JSONObject.parseObject(jsonString);
    }
}

下面为处理jsonArray类

package com.gzjp.commonservice.typehandler;

import com.alibaba.fastjson.JSONArray;

/**
 * mybatis 数组转换
 * @since 2018-07-05 11:26:22
 */
public class JSONArrayTypeHandler extends BaseJSONTypeHandler<JSONArray> {

    public JSONArrayTypeHandler(Class<JSONArray> type) {
        super(type);
    }

    @Override
    protected JSONArray parseJSONString(String jsonString) {
        return JSONArray.parseArray(jsonString);
    }
}

3.4 编写interface和xml

我这里使用的mybatis-plus 2.1.9

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TestMapper extends BaseMapper<Test> {

    List<Test> selectList(@Param("ew") Wrapper<Test> wrapper);

    Test selectByPrimaryKey(Integer id);

    Integer insert(Test record);

    Integer insertSelective(Test record);
	//注意updateBatch 需要在数据库连接字符串添加
	//allowMultiQueries=true
    int updateByPrimaryKeySelectiveBatch(@Param("list") List<Test> records);

    int updateByPrimaryKeySelective(Test record);
}

<?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.gzjp.commonservice.dao.TestMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.gzjp.core.base.model.Test">
        <id column="id" property="id"/>
        <result column="extra" property="extra" typeHandler="com.gzjp.commonservice.typehandler.JSONTypeHandler"/>
    </resultMap>

    <!-- 通用查询结果列 -->

    <sql id="Base_Column_List">
    id,  extra
  </sql>

    <sql id="list_sql">
        select
        <include refid="Base_Column_List"/>
        from base_person_info
        ${ew.sqlSegment}
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from base_person_info
        where id = #{id,jdbcType=INTEGER}
    </select>

    <select id="selectPage" resultMap="BaseResultMap">
        <include refid="list_sql"/>
    </select>

    <select id="selectList" resultMap="BaseResultMap">
        <include refid="list_sql"/>
    </select>

    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.gzjp.core.base.model.Test"
            useGeneratedKeys="true">
    insert into test(
      photo_old_name, extra)
    values ( #{extra,typeHandler=com.gzjp.commonservice.typehandler.JSONTypeHandler})
  </insert>
    <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.gzjp.core.base.model.Test"
            useGeneratedKeys="true">
        insert into base_person_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="extra != null">
              #{extra,typeHandler=com.gzjp.commonservice.typehandler.JSONTypeHandler},
            </if>
        </trim>
    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.gzjp.core.base.model.Test">
        update test
        <set>
      <if test="extra != null">
                <foreach item="value" index="key" collection="extra" open="extra = JSON_SET(extra," separator=","
                         close=")">
                    '$.${key}', #{value}
                </foreach>
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>


    <update id="updateByPrimaryKeySelectiveBatch">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update test
		 <if test="item.extra != null">
                    <foreach item="value" index="key" collection="item.extra" open="extra = JSON_SET(extra," separator=","
                             close=")">
                        '$.${key}', #{value}
                    </foreach>

                </if>
            </set>
            where id = #{item.id,jdbcType=INTEGER}
        </foreach>
    </update>

    <update id="updateByPrimaryKey" parameterType="com.gzjp.core.base.model.Test">
    update test
    set extra = #{extra, typeHandler=com.gzjp.commonservice.typehandler.JSONTypeHandler}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

注意有的地方会使用下面两个foreach中上面一个foreach，这个使用方式和mybatis版本有关，应该是3.2版本以前可以使用这个foreach，3.2版本之后需要使用下面一个
下面是mybatis的官方文档给出的提示
NOTE You can pass any Iterable object (for example List, Set, etc.), as well as any Map or Array object to foreach as collection parameter. When using an Iterable or Array, index will be the number of current iteration and value item will be the element retrieved in this iteration. When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.

1. stackoverflow引用
2. mybatis文档引用

	<foreach collection="extra .entrySet()"  item="item"  separator="," open="extra = JSON_SET(extra," close=")">	      
		        ${item.key}, #{item.value]}  		     
	</foreach>
	
	 <foreach item="value" index="key" collection="extra" open="extra = JSON_SET(extra," separator=","
                         close=")">
                    '$.${key}', #{value}
                </foreach>

4. 结论

主要的想法是使用JSON_SET实现对extra json对象实现动态的局部替换，而不是每次都会替换整个json字段，那么就失去了使用json的便捷性，不如直接使用json字符串来的快