Mysql的JSON字段的读取和转换

一. 背景

在平常的开发中,我们可能会有这样的需求:

业务数据在存储的时候,并不是以 mysql 中的 varchar 丶 int 等格式来存储的,而是使用 JSON 格式.

这样做的好处是当数据结构有变化或者需求变化时,我们不用再添加字段,方便扩展.

那么mysql中 json 格式的数据我们使用 mybatis 或者mybatisplus读取以后,肯定是希望转换成对象或者对象集合的,本篇博文就记录一下使用 mybatis 读取 json 格式的数据转换为对象的方法

二. 数据准备

1. json字段的添加

首先我们如果要使用sql语句来在表中添加一个 json 格式的字段,可以使用以下脚本:

 ALTER TABLE a ADD COLUMN alert_up_config json DEFAULT NULL COMMENT '注释' AFTER id

以上sql执行以后,会在 a 表的 id 字段之后,添加一个字段 alert_up_config ,它的格式就是 json

添加完成以后,我们可以在这个字段中保存json对象或者json数组格式的字符串.

当前的业务场景是: alert_up_config 字段中,是个jsonArr,保存的是告警的升级规则,比如持续时间,次数等.

模拟数据如下:

[{“alertNum”: 100, “alertLevel”: “P1”, “durationTime”: 100, “resourceInfoId”: “123456”}]

注意,使用json类型有2点需要注意:

存储数据必须是json格式,否则会报错
json数据类型没有默认值

三. 数据处理

1. 使用sql语句获取json数据

首先我们可以使用sql语句来提取我们需要的json字段,语法如下:

 #json对象类型处理
JSON_EXTRACT(json列, '$.键')
#jsonArray类型处理, index从开始计算
JSON_EXTRACT(json列, '$[index].键')

比如对于我们上面的示例数据,我们要查询出alert_up_config 字段中的 alertNum 的值,可以使用如下语句:

 select JSON_EXTRACT(alert_up_config, '$[].alertNum') from a;

查询结果如下:

2. 使用mybatis&mybatisPlus来读取数据库中的json数据

首先我们需要知道的是,mybatis提供了一个接口 org.apache.ibatis.type.TypeHandler 来对数据库中各种类型数据的处理,它主要有四个方法:

 public interface TypeHandler<T> {

  //保存数据到数据库之前的处理  
  void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException;
  //下面三个时候拿到数据以后的处理
  T getResult(ResultSet rs, String columnName) throws SQLException;

  T getResult(ResultSet rs, int columnIndex) throws SQLException;

  T getResult(CallableStatement cs, int columnIndex) throws SQLException;

}

也就是说,如果我们要处理从 mysql 中取出的数据,就可以实现这个接口,来自定义我们的数据处理逻辑,得到我们想要的数据.

现在对于我们来说,我们的源数据是这样的:

 [{"alertNum":, "alertLevel": "P1", "durationTime": 100, "resourceInfoId": "123456"}]

也有可能是这样的:

 {"alertNum":, "alertLevel": "P1", "durationTime": 100, "resourceInfoId": "123456"}

前者是 JSONArray ,后者是 JSONObject

对于前面的数据,我们可能会用 List<AlertUpConfig> 这种格式来接收,如果是后者,我们可以使用 AlertUpConfig 这种对象来直接接收数据.

现在,我们的需要mybatis或者mybatisplus帮助我们将数据库中的 json 数据自动转换为相应的格式,一起来看下怎么实现吧!

3. 使用mybatisplus实现json转java对象/集合

在mybatisplus中,已经为我们提供了一系列的 TypeHandler

有一个抽象类为: com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler

它抽象了json处理的方法:

 public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }

    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        return StringUtils.isBlank(json) ? null : this.parse(json);
    }

    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        return StringUtils.isBlank(json) ? null : this.parse(json);
    }

    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        return StringUtils.isBlank(json) ? null : this.parse(json);
    }

同时,它提供两个抽象方法,用于提供给不同的json包:

 protected abstract T parse(String json);

    protected abstract String toJson(T obj);

我们看一下 AbstractJsonTypeHandler 的实现类,可以发现有我们想要的东西:

很明显,mybatisplus已经提供了 FastJson 、 Gson 和 JackSon 的typehandler给我们使用.

下面是使用方法:

json转java对象如果不用 xml 文件(xml的方式在mybatis部分说明),在实体对象相应的字段上,用 typeHandler 参数指定我们要使用的 typeHandler 同时,在实体类上使用注解: @TableName(value = “a”, autoResultMap = true)autoResultMap 参数表明字段在查询处理的时候自动转换为对象 @TableField(value = “alert_up_config” , typeHandler = FastjsonTypeHandler . class ) private AlertUpConfig alertUpConfig; 配置好以后,使用mapper查询,可以发现 alert_up_config 字段直接被转换成了 java对象
json转List

4. 使用mybatis在xml中实现json转java对象/集合

在xml中实现json和对象的转换也比较简单

 <result column="alert_up_config" property="alertUpConfig" jdbcType="VARCHAR"
                javaType="com.xxxx.AlertUpConfig"
                 typeHandler="com.xxxx.AlertUpConfigHandler"/>

同样的handler,只是配置方式从注解改成xml中的属性了.

需要注意的是, FastjsonTypeHandler 是mybatisplus提供的处理器,如果你的项目中没有引入mp,那么可以自己参照它写一个.