mybatis-plus读取JSON类型并处理JSON中数据

mybatis-plus读取JSON类型

本文总共三个步骤：
1、在数据库表定义JSON字段；
2、在实体类加上@TableName(value = “extra_info”, autoResultMap = true)、在JSON字段映射的属性加上
@TableField(typeHandler = )；

@TableField(typeHandler = );
3、建一些业务代码进行测试；

在数据库表定义JSON字段

DROP TABLE IF EXISTS `extra_info`;
CREATE TABLE `extra_info`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `extra_json` json NULL,
  `extra_object` json NULL,
  `extra_list` json NULL,
  `extra_array` json NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `extra_info` VALUES (1, '{\"id\": 6, \"name\": \"7\"}', '{\"id\": 1, \"name\": \"2\"}', '[{\"id\": 1, \"name\": \"2\"}, {\"id\": 2, \"name\": \"3\"}]', '[{\"id\": 1, \"name\": \"2\"}, {\"id\": 4, \"name\": \"5\"}]');

实体类处理

在实体类加上@TableName(value = "extra_info", autoResultMap = true)、在JSON字段映射的属性加上@TableField(typeHandler = ),@TableField(typeHandler = ) JacksonTypeHandler为mybatisplus自带,ObjectAndJsonHandler自定义；

ExtraInfo,ExtraNode

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import com.util.ObjectAndJsonHandler;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
@TableName(value = "extra_info", autoResultMap = true)
public class ExtraInfo implements Serializable {

    @TableId(type = IdType.AUTO)
    private Integer id;

    @TableField(typeHandler = JacksonTypeHandler.class)
    private ExtraNode extraObject;

    @TableField(typeHandler = FastjsonTypeHandler.class)
    private JSONObject extraJson;

    @TableField(typeHandler = ObjectAndJsonHandler.class)
    private List<ExtraNode> extraList;

    @TableField(typeHandler = JacksonTypeHandler.class)
    private ExtraNode[] extraArray;
}

import lombok.Data;

@Data
public class ExtraNode {
	private Integer id;
	private String name;
}

业务代码

dao

@Mapper
public interface ExtraInfoDao extends BaseMapper<ExtraInfo> {
	
}

service

public interface ExtraInfoService extends IService<ExtraInfo> {
	List<ExtraInfo> selectAll();
}

serviceImpl

@Service("extraInfoService")
public class ExtraInfoServiceImpl extends ServiceImpl<ExtraInfoDao, ExtraInfo> implements ExtraInfoService {
	@Autowired
	ExtraInfoDao extraInfoDao;

	@Override
	public List<ExtraInfo> selectAll() {
		return extraInfoDao.selectList(null);
	}
}

工具类

import com.fasterxml.jackson.core.type.TypeReference;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;

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

/**
 * object和json字符串 互相转化
 */
@MappedJdbcTypes({JdbcType.VARCHAR})
public class ObjectAndJsonHandler extends BaseTypeHandler<Object> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        String json = GenericAndJson.objectToJson(parameter);
        ps.setString(i, json);
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String string = rs.getString(columnName);
        return GenericAndJson.jsonToObject(string, new TypeReference<Object>() {
        });
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String string = rs.getString(columnIndex);
        return GenericAndJson.jsonToObject(string, new TypeReference<Object>() {
        });
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String string = cs.getString(columnIndex);
        return GenericAndJson.jsonToObject(string, new TypeReference<Object>() {
        });
    }
}

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class GenericAndJson {
    private static ObjectMapper mapper;

    @Autowired
    public void setMapper(ObjectMapper mapper) {
        GenericAndJson.mapper = mapper;
    }

    public static <T> String objectToJson(T o) {
        try {
            return GenericAndJson.mapper.writeValueAsString(o);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    public static <T> T jsonToObject(String s, TypeReference<T> typeReference) {
        if (s == null) {
            return null;
        }
        try {
            return GenericAndJson.mapper.readValue(s, typeReference);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/">

<mapper namespace="">
	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="" id="extraInfoMap">
        <result property="id" column="id"/>
        <result property="extraObject" column="extra_object" typeHandler=""/>
        <result property="extraJson" column="extra_json" typeHandler=""/>
        <result property="extraList" column="extra_list" typeHandler=""/>
        <result property="extraArray" column="extra_array" typeHandler=""/>
    </resultMap>
</mapper>

测试

import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.TypeReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/test")
public class TestExtraInfoController {

	@Autowired
	private ExtraInfoService extraInfoMapper;

	@GetMapping
	public List<ExtraNode> listAll() {
		List<ExtraNode> listExtraNode = new ArrayList<>();
		ExtraNode[] strArr = new ExtraNode[0];
		String str = null;
		String str2 = null;
		List<ExtraInfo> listExtraInfo = extraInfoMapper.selectAll();

		for (ExtraInfo extraInfo : listExtraInfo) {
			listExtraNode = extraInfo.getExtraList();
			strArr = extraInfo.getExtraArray();
			extraNode = extraInfo.getExtraObject();
			str2 = extraInfo.getExtraJson().get("name").toString();
		}

		System.out.println(extraNode.getName());
		System.out.println(str2);
		//类型转换异常：:  cannot be cast to com.所以要用下面方法解决
		ObjectMapper mapper = new ObjectMapper();
		List<ExtraNode> list = mapper.convertValue(listExtraNode, new TypeReference<List<ExtraNode>>() {
		});

		str = list.stream().filter(f -> f.getId() == 2).map(ExtraNode::getName).collect(Collectors.joining(""));
		System.out.println(str);

		List<ExtraNode> listExtraNodeArr = new ArrayList<>(Arrays.asList(strArr));
		System.out.println(listExtraNodeArr.stream().map(ExtraNode::getName).collect(Collectors.joining(",")));
		return listExtraNode;
	}
}