mybatis中操作json类型数据

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));
        }
    }

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

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

1. 在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

1. 结果

    
    //新增结果
    {
        "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"
     }
   ]
 }

文章知识点与官方知识档案匹配，可进一步学习相关知识

Java技能树
使用JDBC操作数据库
JDBC概述