mybatis中操作json类型数据

mysql使用json类型字段保存数据，使用mybatis进行新增、查询操作，实现字段映射转换

1. 自定义TypeHandler

package com.xxx.xxx.handler;
  
import java.io.IOException;  
import java.sql.CallableStatement;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
import com.fasterxml.jackson.core.JsonProcessingException;  
import com.fasterxml.jackson.databind.DeserializationFeature;  
import com.fasterxml.jackson.databind.ObjectMapper;  
import lombok.extern.slf4j.Slf4j;  
import org.apache.ibatis.exceptions.PersistenceException;  
import org.apache.ibatis.type.BaseTypeHandler;  
import org.apache.ibatis.type.JdbcType;  
import org.apache.ibatis.type.MappedJdbcTypes;  

/**
 1. @ModifyTime 2021/11/25
 2. JSON 字段类型处理器
 **/
@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JacksonTypeHandler<T extends Object> extends BaseTypeHandler<T> {
    private static ObjectMapper objectMapper;
    private Class<T> type;

    static {
        objectMapper = new ObjectMapper();
        objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
    }

    public JacksonTypeHandler(Class<T> type) {
        if (log.isTraceEnabled()) {
            log.trace("JacksonTypeHandler(" + type + ")");
        }
        if (null == type) {
            throw new PersistenceException("Type argument cannot be null");
        }
        this.type = type;
    }

    private T parse(String json) {
        try {
            if (json == null || json.length() == 0) {
                return null;
            }
            return objectMapper.readValue(json, type);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private String toJsonString(T obj) {
        try {
            return objectMapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getString(columnName));
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getString(columnIndex));
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parse(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int columnIndex, T parameter, JdbcType jdbcType)
            throws SQLException {
        ps.setString(columnIndex, toJsonString(parameter));
    }
}

2. yml配置文件中添加配置

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-handlers-package: com.xx.xx.handler

3. 在mapper文件中编写对应的字段映射规则

<result column="parameter" property="parameter" jdbcType="VARCHAR"
            typeHandler="com.xx.xx.handler.JacksonTypeHandler"/> 
            //指定返回信息使用该Typehandler

`parameter` = #{parameter,javaType=com.xx.xx.entity.ParameterEntity, typeHandler=com.xx.xx.handler.JacksonTypeHandler},
//新增或修改时指定该字段对应的对象类型、以及Typehandler

4. 结果

//新增结果
{
    "code": 200,
    "msg": "操作成功",
    "data": {
        "id": "9c43257c-90ff-464d-aa84-43784b728c06",
        "name": "string",
        "parameter": [
            {
                "name": "新增的参数",
                "type": 1
            },
            {
                "name": "新增的参数",
                "type": 1
            }
        ],
        "createTime": "2021-11-25T11:54:24.577+08:00",
        "updateTime": "2021-11-25T11:54:24.577+08:00"
    }
}

//查询结果
{
  "code": 200,
  "msg": "操作成功",
  "data": [
    {
      "id": "2ecb88f6-c99c-4877-ade5-72c3081b6009",
      "name": "string",
      "parameter": [
        {
          "name": "新增的参数",
          "type": 1
        },
        {
          "name": "新增的参数",
          "type": 1
        }
      ],
      "createTime": "2021-11-25T00:00:00.000+08:00",
      "updateTime": "2021-11-25T00:00:00.000+08:00"
    },
    {
      "id": "65e0a272-4267-4ea8-a89b-79e811ca2e17",
      "name": "string2",
      "parameter": [
        {
          "name": "新增的参数",
          "type": 1
        },
        {
          "name": "新增的参数",
          "type": 1
        }
      ],
      "createTime": "2021-11-25T00:00:00.000+08:00",
      "updateTime": "2021-11-25T00:00:00.000+08:00"
    }
  ]
}