SpringBoot 存储JSON数据到Mysql对应的JSON字段

一. 引入Gson

<dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
        </dependency>

二. 定义要存储的JSON字段

@Data
public class LJUserExt implements Serializable {

    private static final long serialVersionUID = -1464964389043519003L;

    private String daughter;

    private String wife;
}

@Data
public class LJUser implements Serializable {

    private static final long serialVersionUID = -4002400177569822816L;
    /**
     * 主键
     */
    private  int  userId;

    private String userName;

    private String tel;

    private String passwd;

    private String picUrl;

    private String orgCode;

    private String orgName;

    private LJUserExt ext; //存储的JSON字段
}

三.定义转化器，同时mybatis的sql文件建议引用

package com.robinboot.service.utils.typehandler;


import com.alibaba.fastjson.annotation.JSONField;
import com.fasterxml.jackson.annotation.JsonIgnore;
import sun.reflect.generics.reflectiveObjects.TypeVariableImpl;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.lang.reflect.TypeVariable;
import java.util.Arrays;
import java.util.Optional;

/**
 * interface版本的TypeToken
 * @param <T>
 */
public interface GenericTypeIdentified<T> {


    default Type getType() {
        return getType(getClass());
    }

    default Type getType(Class<?> clazz) {

        Type[] interfaces = clazz.getGenericInterfaces();
        Optional<Type> opInterfaces = Arrays.stream(interfaces)
                .filter(i -> i instanceof ParameterizedType &&
                        ((ParameterizedType) i).getRawType().equals(GenericTypeIdentified.class))
                .findAny();
        Class<?> superClazz = clazz.getSuperclass();
        if (!opInterfaces.isPresent()) {
            if (superClazz == Object.class)
                return null;
            Type tmpT = getType(superClazz);
            if (tmpT == null || tmpT instanceof TypeVariable) { //泛型继承被擦除
                if (clazz.getGenericSuperclass() instanceof ParameterizedType)
                    // 直接拿取最近父类泛型，这里有坑，因为没有判断继承关系中，如果有多个泛型参数，所要识别的泛型参数的位置
                    // 这里直接用了下标0的具体类型
                    return ((ParameterizedType) clazz.getGenericSuperclass()).getActualTypeArguments()[0];
                return null;
            }
            return tmpT;
        }
        return ((ParameterizedType) opInterfaces.get()).getActualTypeArguments()[0];
    }

    @JSONField(serialize = false)
    @JsonIgnore
    default Class<T> getGenericTypeClass() {
        Type genericType = getType();
        return unwrapClass(genericType);
    }

    @SuppressWarnings("unchecked")
    default Class<T> unwrapClass(Type genericType) {
        if (genericType == null)
            return null;
        if (genericType instanceof ParameterizedType) {
            ParameterizedType pt = (ParameterizedType) genericType;
            return (Class<T>) pt.getRawType();
        } else if (genericType instanceof TypeVariableImpl) {
            // 如果得到的是泛型参数，说明泛型赋给对象，运行时擦除，尝试获取上下边界返回
            Type[] bounds = ((TypeVariableImpl) genericType).getBounds();
            if (bounds == null || bounds.length == 0)
                return null;
            return unwrapClass(bounds[0]);
        } else try {
            return (Class<T>) genericType;
        } catch (Exception e) {
            return null;
        }
    }
}

package com.robinboot.service.utils.typehandler;

import com.google.gson.Gson;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public abstract class JsonTypeHandler<T>
        extends BaseTypeHandler<T>
        implements GenericTypeIdentified<T> {

	protected Gson gson = new Gson();

    protected Class<T> clazz = getGenericTypeClass();

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

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

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

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

    private String toJson(T object) {
        try {
            return gson.toJson(object);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

	private T toObject(String content) {
        if (content != null && !content.isEmpty()) {
            try {
                return gson.fromJson(content, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }




}

package com.robinboot.service.utils.typehandler;

import com.robinboot.service.domain.LJUserExt;

/**
 * @auther: TF12778
 * @date: 2021/8/17 16:22
 * @description:
 */
public class userExtTypeHandler extends JsonTypeHandler<LJUserExt> {
}

sql文件，此处引用ext的转化器，

typeHandler = com.robinboot.service.utils.typehandler.userExtTypeHandler

 <update id="updateUser" parameterType="com.robinboot.service.domain.LJUser">
        UPDATE user
        <set>
            <if test="userName != null and userName.toString() != ''" >userName = #{userName},</if>
            <if test="tel != null and tel.toString() != ''" >tel = #{tel},</if>
            <if test="passwd != null and passwd.toString() != ''" >passwd = #{passwd},</if>
            <if test="picUrl != null and picUrl.toString() != ''" >picUrl = #{picUrl},</if>
            <if test="orgCode != null and orgCode.toString() != ''" >orgCode = #{orgCode},</if>
            <if test="orgName != null and orgName.toString() != ''" >orgName = #{orgName},</if>
            <if test="ext != null and ext.toString() != ''" >ext = #{ext ,typeHandler = com.robinboot.service.utils.typehandler.userExtTypeHandler},</if>
        </set>
        WHERE userId = #{userId}
    </update>

四. 更新用户信息的ext字段

先看下mysql中ext的格式，为json格式。

 更新用户信息，关键信息存储user.setExt(ext); // json字段

  @Override
    public Result<Integer> updateUser(LJUserDto dto) {

        if (StringUtils.isBlank(ObjectUtils.toString(dto.getUserId()))) {
            throw new ServiceException("userId不能为空", "500");
        }
        LJUserQuery query = new LJUserQuery();
        query.setUserId(dto.getUserId());
        LJUser ljUser = ljUserService.selectUserDetail(query);
        if (ljUser == null) {
            throw new ServiceException("数据为空", "500");
        }

        LJUser user = new LJUser();
        user.setUserId(dto.getUserId());

        LJUserExt ext = new LJUserExt();
        BeanUtils.copyProperties(dto, ext);
        user.setExt(ext); // json字段
        Integer i = ljUserService.updateUser(user);
        if (i == 0) {
            throw new ServiceException("更新失败", "500");
        }
        return new Result<Integer>("success", "操作成功", "0", null, "200");
    }

五. JSON数据格式存储到mysql中的测试结果

以上就是将json数据存储到mysql中json对应字段的全过程