本文总共三个步骤:
1、在数据库表定义JSON字段;
2、在实体类加上@TableName(value = “extra_info”, autoResultMap = true)、在JSON字段映射的属性加上
@TableField(typeHandler = JacksonTypeHandler.class);
@TableField(typeHandler = ObjectAndJsonHandler.class);
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 = JacksonTypeHandler.class)
,@TableField(typeHandler = ObjectAndJsonHandler.class)
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;@Datapublic class ExtraNode {private Integer id;private String name;}
业务代码
dao
@Mapperpublic 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 {@AutowiredExtraInfoDao extraInfoDao;@Overridepublic 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;@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;@Componentpublic 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(); } }}
mapper.xml
DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dao.ExtraInfoDao"> <resultMap type="com.entity.ExtraInfo" id="extraInfoMap"> <result property="id" column="id"/> <result property="extraObject" column="extra_object" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/> <result property="extraJson" column="extra_json" typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/> <result property="extraList" column="extra_list" typeHandler="com.hftp.mq.util.ObjectAndJsonHandler"/> <result property="extraArray" column="extra_array" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/> 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 {@Autowiredprivate ExtraInfoService extraInfoMapper;@GetMappingpublic 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);//类型转换异常:java.lang.ClassCastException: java.util.LinkedHashMap 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;}}