MySQL存储JSON

1.需要将数据转换为Json存储的场景

1. 电子商务平台的商品信息

在电商平台上，每个商品可能有多种属性，如标题、描述、价格、规格、图片URL等，而且这些属性可能会随着业务需求变化而增加或减少。使用JSON可以灵活地存储这些复杂且不断变化的商品信息结构，便于快速检索和展示。

1. 社交网络的用户动态

社交网络中，用户发布的动态可能包含文字、图片、地理位置、标签等多种元素，且结构可能随产品迭代而调整。存储动态信息时，将这些多样化的内容打包成JSON可以简化数据库设计并提高扩展性。

2.自定义JsonTypeHandler继承Mybatis的BaseTypeHandler处理器

import cn.hutool.json.JSONUtil;import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;import org.springframework.context.annotation.Configuration;
import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
public class JsonTypeHandler1<T> extends BaseTypeHandler<T> {@Override    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {        ps.setString(i, JSONUtil.toJsonStr(parameter));    }@Override    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {if (rs.getString(columnName).charAt(0) == '{') {            return (T) JSONUtil.parseObj(rs.getString(columnName));        }        return (T) JSONUtil.parseArray(rs.getString(columnName));}@Override    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {        return null;    }@Override    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {        return null;    }}

Java

3.自定义序列化方式

import cn.hutool.core.lang.Assert;import com.fasterxml.jackson.databind.JavaType;import com.fasterxml.jackson.databind.ObjectMapper;import com.fasterxml.jackson.databind.type.TypeFactory;import org.springframework.lang.Nullable;
import java.nio.charset.Charset;import java.nio.charset.StandardCharsets;
public class MybatisToJsonConfig<T> {    public static final Charset DEFAULT_CHARSET;    private final JavaType javaType;private ObjectMapper objectMapper = new ObjectMapper();public MybatisToJsonConfig(Class<T> type) {        this.javaType = this.getJavaType(type);    }public MybatisToJsonConfig(JavaType javaType) {        this.javaType = javaType;    }public T deserialize(@Nullable byte[] bytes) throws Exception {try {                return this.objectMapper.readValue(bytes, 0, bytes.length, this.javaType);            } catch (Exception var3) {                throw new Exception("Could not read JSON: " + var3.getMessage(), var3);            }    }public byte[] serialize(@Nullable Object t) throws Exception {try {                return this.objectMapper.writeValueAsBytes(t);            } catch (Exception var3) {                throw new Exception("Could not write JSON: " + var3.getMessage(), var3);            }    }public void setObjectMapper(ObjectMapper objectMapper) {        Assert.notNull(objectMapper, "'objectMapper' must not be null");        this.objectMapper = objectMapper;    }protected JavaType getJavaType(Class<?> clazz) {        return TypeFactory.defaultInstance().constructType(clazz);    }static {        DEFAULT_CHARSET = StandardCharsets.UTF_8;    }
}

Java

import com.fasterxml.jackson.annotation.JsonAutoDetect;import com.fasterxml.jackson.annotation.PropertyAccessor;import com.fasterxml.jackson.databind.ObjectMapper;import lombok.SneakyThrows;import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;import java.nio.charset.StandardCharsets;import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
//@MappedTypes(JSONObject.class)//@MappedJdbcTypes(JdbcType.VARCHAR)public class JsonTypeHandler<T> extends BaseTypeHandler<T> {    private static MybatisToJsonConfig<Object> serializer = null;static {        serializer = new MybatisToJsonConfig<>(Object.class);ObjectMapper objectMapper = new ObjectMapper();        //JsonAutoDetect.Visibility.ANY 代表所有属性或字段都可以序列化        objectMapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);        //新版用法        //以数组的方式存放到Redis，Class Type 全类名作为为第一个元素，Json字符串为第二个元素。        objectMapper.activateDefaultTyping(objectMapper.getPolymorphicTypeValidator(), ObjectMapper.DefaultTyping.NON_FINAL);        //老版用法，已弃用        //objectMapper.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);        serializer.setObjectMapper(objectMapper);}public JsonTypeHandler(Class<T> clazz) {}@Override    @SneakyThrows    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {ps.setString(i, new String(serializer.serialize(parameter)));}@Override    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {        try {            return (T) serializer.deserialize(rs.getString(columnName).getBytes(StandardCharsets.UTF_8));        } catch (Exception e) {            throw new RuntimeException(e);        }    }@Override    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return null;    }@Override    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {return null;}}

Java

4.MyBatis-Plus

了解一下

5.映射处理

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dunan.dao.UserMapper"><resultMap id="jsonMapper" type="com.dunan.entity.User"><result column="id" property="id"/><result column="name" property="name"/><result column="age" property="age"/><result column="address" property="address"typeHandler="com.dunan.config.JsonTypeHandler"/><result column="friend" property="friend"typeHandler="com.dunan.config.JsonTypeHandler"/></resultMap><insert id="insert" keyProperty="id" useGeneratedKeys="true">INSERT INTO user (name, age, address, friend)VALUES (#{name}, #{age}, #{address, typeHandler=com.dunan.config.JsonTypeHandler},#{friend, typeHandler=com.dunan.config.JsonTypeHandler})</insert><select id="findAll" resultMap="jsonMapper">SELECT *FROM user</select></mapper>