MySQL通过json格式存放Long类型List以及mybatis存取

背景

由于业务需要，存储一个Long类型的list在mysql里，使用的MySQL版本为8.0。查阅发现5.7以上已经支持json格式，想测试一下以json类型存储list。

准备

准备一个实体类映射到mysql表，实体类包括一个主键id以及一个Long类型的list。

public class MyEntity {

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private List<Long> jsonList;

    // 省略其他属性和方法
}

在数据库中新建表

CREATE TABLE my_entity (
  id BIGINT PRIMARY KEY,
  json_list JSON
);

新建typeHandler

要将 mysql中取出的JSON 类型数组转化为 Long 类型列表，需要自定义typeHandler。首先是json转list，假设命名为ListTypeHandler(json解析采用的是jackson)：

package xxx.handler;

import xxx.JacksonUtil;
import cn.hutool.core.collection.CollectionUtil;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.*;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes({List.class})
public abstract class ListTypeHandler<T> extends BaseTypeHandler<List<T>> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, List<T> ts, JdbcType jdbcType) throws SQLException {
        String content = CollectionUtil.isEmpty(ts) ? null : this.toJSONString(ts);
        preparedStatement.setString(i, content);
    }

    @Override
    public List<T> getNullableResult(ResultSet resultSet, String s) throws SQLException {
        try {
            return this.getListByJsonArrayString(resultSet.getString(s));
        } catch (JsonProcessingException e) {
            throw new BizException(e);
        }
    }

    @Override
    public List<T> getNullableResult(ResultSet resultSet, int i) throws SQLException {
        try {
            return this.getListByJsonArrayString(resultSet.getString(i));
        } catch (JsonProcessingException e) {
            throw new BizException(e);
        }
    }

    @Override
    public List<T> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        try {
            return this.getListByJsonArrayString(callableStatement.getString(i));
        } catch (JsonProcessingException e) {
            throw new BizException(e);
        }
    }

    private List<T> getListByJsonArrayString(String content) throws JsonProcessingException {
        return StringUtils.isEmpty(content) ? new ArrayList<>() : new ObjectMapper().readValue(content, this.specificType());
    }

    protected abstract TypeReference<List<T>> specificType();
}

public String toJSONString(Object obj) {
        try {
            return null == obj ? null : mapper.writeValueAsString(obj);
        } catch (JsonProcessingException var2) {
            throw new BizException(var2);
        }
    }

再新建个LongTypeHandler类，将ListTypeHandler的元素设置为Long类型

package xxx.handler;

import com.fasterxml.jackson.core.type.TypeReference;

import java.util.List;

public class LongTypeHandler extends ListTypeHandler<Long>{
    @Override
    protected TypeReference<List<Long>> specificType() {
        return new TypeReference<List<Long>>() {};
    }
}

在mapper.xml中添加resultMap，对于json_list指定typeHandler，用于映射实体类时进行类型转换。指定将 JSON 格式的 Long 类型列表在数据库中的类型为 VARCHAR。

    <resultMap id="MyEntity" type="xxx.MyEntity">
        <id column="id" property="id"/>
        <result column="json_list" property="jsonList"
                jdbcType="VARCHAR"
                typeHandler="xxx.LongTypeHandler"/>
    </resultMap>

查阅的时候，resultMap加上这个id即可，比如：

<select id="xxx" resultMap="SqlInfoTabDO">
        select
            *
        from
            my_entity

如果是增删改的时候，需要加上typeHandler和jdbcType，这里用的是mybatis-plus的JacksonTypeHandler,比如

 <insert id="insert" parameterType="xxx">
        insert into my_entity
            (json_list)
        values
            (
             #{jsonList, jdbcType=VARCHAR,typeHandler=com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler}
            )
    </insert>

 <update id="update" parameterType="xxx">
        update
            my_entity
        set
            
            json_list = #{json_list, jdbcType=VARCHAR, typeHandler=com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler}
        where id = #{id}
    </update>

错误示范

刚开始的时候，本人是使用了JacksonTypeHandler对jsonList在取值的时候进行映射，如下：

<!-- 错误示范 -->
    <resultMap id="MyEntity" type="xxx.MyEntity">
        <id column="id" property="id"/>
        <result column="json_list" property="jsonList"
                jdbcType="VARCHAR"
               typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
    </resultMap>

会出现什么情况呢？如果jsonList中long类型元素较小的时候，比如[1,2,3]等，从mysql中取出的时候，JacksonTypeHandler会将这些元素自动转换为“更适合”的Integer类型，如果像下面表达式一样取出：

List<Long> jsonList = mapper.getJsonList(id);

整个List都是Integer类型元素，转型为Long类型时会报错。

所以还是学会写typeHandler，将类型转换掌握在自己手中比较好。。。