mybatis中操作json類型數(shù)據(jù)

mysql使用json類型字段保存數(shù)據(jù)，使用mybatis進(jìn)行新增、查詢操作，實(shí)現(xiàn)字段映射轉(zhuǎn)換

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文件中編寫對應(yīng)的字段映射規(guī)則

<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},
    //新增或修改時指定該字段對應(yīng)的對象類型、以及Typehandler

1. 結(jié)果

    
    //新增結(jié)果
    {
        "code": 200,
        "msg": "操作成功",
        "data": {
            "id": "9c43257c-90ff-464d-aa84-43784b728c06",
            "name": "string",
            "parameter": [
                {
                    "name": "新增的參數(shù)",
                    "type": 1
                },
                {
                    "name": "新增的參數(shù)",
                    "type": 1
                }
            ],
            "createTime": "2021-11-25T11:54:24.577+08:00",
            "updateTime": "2021-11-25T11:54:24.577+08:00"
        }
  }
//查詢結(jié)果
{
  "code": 200,
   "msg": "操作成功",
   "data": [
     {
       "id": "2ecb88f6-c99c-4877-ade5-72c3081b6009",
       "name": "string",
       "parameter": [
         {
           "name": "新增的參數(shù)",
           "type": 1
         },
         {
           "name": "新增的參數(shù)",
           "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": "新增的參數(shù)",
           "type": 1
         },
         {
           "name": "新增的參數(shù)",
           "type": 1
         }
       ],
       "createTime": "2021-11-25T00:00:00.000+08:00",
       "updateTime": "2021-11-25T00:00:00.000+08:00"
     }
   ]
 }

文章知識點(diǎn)與官方知識檔案匹配，可進(jìn)一步學(xué)習(xí)相關(guān)知識

Java技能樹
使用JDBC操作數(shù)據(jù)庫
JDBC概述