Mybatis深度整合Mysql的Json字段问题

概述以前当业务数据结构变化时，往往需要采用的方案是：修改表结构增加字段遇到数据结构有list结构时，新建1对多的关联子表用字典表表示字段的增加以上方案

概述

以前当业务数据结构变化时，往往需要采用的方案是：

修改表结构增加字段

遇到数据结构有list结构时，新建1对多的关联子表

用字典表表示字段的增加

以上方案对代码侵入性很强，同时与旧业务数据结构不兼容。导致代码从实体类、Dao、Service、Controller层都要修改。

随着NOSQL数据库的广泛应用，可扩展的存储方式在关系型数据库中也有了很好的支持，最新的MySQL5.7中就新增加了一个数据类型JSON，使用mysql的json类型字段做扩展字段，可以以json串形式动态的存储任意结构的数据，包括list结构的数据也不必再创建子表。代码的实体类和Dao层不必修改，其他层代码修改量也能够减少。

在这里插入图片描述

Mysql常见json字段操作

Mysql5.7开始支持json字段

创建带有json字段的表micro_test,其中extcol为json类型字段

CREATE TABLE `micro_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meta_name` varchar(100) DEFAULT NULL COMMENT '元数据名称',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `extcol` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

插入json字段

可按照json字符串插入json字段

Insert into micro_test (extcol,meta_name,create_time) 
values('{"name":"tomcat","age":15}','123',now());

查询json字段

可以根据path查询json字段中全部或部分数据

Select meta_name,extcol->>'$.name' as name,extcol->>'$.age' as age from micro_test;

修改json字段

可以根据path局部更新json字段中数据

Update micro_test set extcol=json_set(extcol,'$.name','jeffrey') where meta_name='123'

Mysql5.7.22版本以后支持JSON_MERGE_PATCH

可以省略path参数，全面更新json字段中数据

Update micro_test set extcol=json_set(extcol,'{“name”:”n1”,”age”:30}') where meta_name='123'

Mybatis使用Json字段

按照mybatis常规方式把json函数写入到xml文件中的sql中，即可支持json字段增删改查。

但查询出的json字段为字符串类型，需要手工转成bean，插入时需手工把bean转成json字符串，这样做不利于面向对象编程。

在这里插入图片描述

Mybatis深度整合Json字段

实现bean与json串在mybatis内部转换，这样做的优点是dao层代码和sql不变，service层可以增删改查不同的动态Entity对象。更符合面向对象编程习惯提高开发效率。

在这里插入图片描述

Extcol开源项目实现Mybatis与mysql的json字段深度整合

项目地址为：

https://github.com/jeffreyning/extcol.git

pom引用extcol的jar

<dependency>
	<groupId>com.github.jeffreyning</groupId>
	<artifactId>extcol</artifactId>
	<version>0.0.3-RELEASE</version>
</dependency>

Extcol包中TypeHandler子类TagToJsonTypeHandler 实现mybatis在数据库操作过程中的参数输入和结果转换的拦截。拦截父类为ExtBeanWrapper的对象。

使TagToJsonTypeHandler生效需要配置mybatis.typeHandlersPackage(如果使用mybatisplus，则配置mybatis-plus.typeHandlersPackage)

mybatis:
  typeHandlersPackage: com.nh.micro.ext.th

Extcol包中ExtBeanWrapper类，作为json对象转换的目标对象，内有map成员变量(innerMap)保存实际数据,getobj和setobj方法是使用fastjson做对象与map的转换。

Extcol组件的Demo

demo工程地址为 https://github.com/jeffreyning/extcol-demo.git

引入和配置好extcol后，在demo业务系统工程中编写对应micro_test表的实体类TestDto，其中json字段的成员变量类型是ExtBeanWrapper。

public class TestDto  {
	private Integer id;
	private String metaKey;
	private String metaName;
	private String metaType;
	private Date createTime;
	private ExtBeanWrapper extcol;
	public Integer getId() { return id; }
    public void setId(Integer id) {this.id = id;}
    public String getMetaKey() {return metaKey;}
    public void setMetaKey(String metaKey) {this.metaKey = metaKey;}
    public String getMetaName() {return metaName;}
    public void setMetaName(String metaName) {this.metaName = metaName;}
    public String getMetaType() {return metaType;}
    public void setMetaType(String metaType) {this.metaType = metaType; }
    public Date getCreateTime() {return createTime;}
    public void setCreateTime(Date createTime) {this.createTime = createTime;}
    public ExtBeanWrapper getExtcol() {return extcol; }
    public void setExtcol(ExtBeanWrapper extcol) {this.extcol=extcol; }
}

扩展字段业务bean

例如扩展bean为ExtEntity（保险订单）有3个在数据库中json字段动态存储的字段insureNum（保险单号）、insureType(保险类型)、contacts(联系电话)

public class ExtEntity<T> {
    private Integer insureNum;
    private String insureType;
    private List contacts;
    public Integer getInsureNum() {return insureNum;}
    public void setInsureNum(Integer insureNum) {this.insureNum = insureNum;}
    public String getInsureType() {return insureType; }
    public void setInsureType(String insureType) {this.insureType = insureType;}
    public List<T> getContacts() {return contacts; }
    public void setContacts(List<T> contacts) {this.contacts = contacts;    }
}

在以TestDto为更新和插入时的参数操作时，mybatis将负责将bean转为json串。

当执行查询语句时，返回的结果映射到ExtBeanWrapper 类型的字段时，mybatis将负责将json串转为ExtBeanWrapper ，且这个ExtBeanWrapper 可以按照不同的业务bean自适应转化。

<?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.nh.micro.demo.dao.MicroTestMapper" >
  <resultMap id="TestDto" type="com.nh.micro.demo.entity.TestDto" >
      <id column="id" property="id" jdbcType="INTEGER" />
      <result column="meta_name" property="metaName" jdbcType="VARCHAR" />
      <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
      <result column="extcol" property="extcol" jdbcType="VARCHAR" />
  </resultMap>
  <insert id="createJson" parameterType="com.nh.micro.demo.entity.TestDto">
	insert into micro_test(meta_name, create_time, extcol) values(#{metaName}, now(), #{extcol})
  </insert>
  <select id="getInfo4JsonXml" resultMap="TestDto" >
	SELECT * from micro_test
  </select>
  <update id="updateJson" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=json_merge_patch(extcol, #{extcol}) where id=#{id}
  </update>
  <update id="updateJsonSubcol" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=json_set(extcol,'$.insureNum', #{extcol.innerMap.insureNum}) where id=#{id}
  </update>
  <update id="updateJsonAll" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=#{extcol} where id=#{id}
  </update>
</mapper>

Mapper(dao)层代码示例

package com.nh.micro.demo.dao;
import com.nh.micro.demo.entity.TestDto;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface MicroTestMapper {
    //创建记录
    public void createJson(TestDto testDto);
    //查询记录
    public List<TestDto> getInfo4JsonXml();
    //动态局部更新json字段
    public void updateJson(TestDto testDto);
    //更新指定json字段中的子元素
    public void updateJsonSubcol(TestDto testDto);
    //整体更新json字段
    public void updateJsonAll(TestDto testDto);
}

插入json字段

service层代码示例

@Test
public void createJson(){
    TestDto testDto=new TestDto();
    testDto.setId(1);
    testDto.setMetaName("n1");
    ExtBeanWrapper extBeanWrapper=new ExtBeanWrapper();
    ExtEntity extEntity=new ExtEntity();
    extEntity.setInsureNum(123123);
    extEntity.setInsureType("car");
    List contacts=new ArrayList();
    contacts.add("13512345678");
    contacts.add("13512345679");
    extEntity.setContacts(contacts);
    extBeanWrapper.setObj(extEntity);
    testDto.setExtcol(extBeanWrapper);
    microTestMapper.createJson(testDto);
}

sql代码示例

<insert id="createJson" parameterType="com.nh.micro.demo.entity.TestDto">
	insert into micro_test(meta_name, create_time, extcol) values(#{metaName}, now(), #{extcol})
</insert>

查询结果中取JSON字段中存储的业务

类ExtEntity 的Service层示例代码

public List<TestDto> testQuery4JsonXml(){
	List<TestDto> retList=testDao.getInfo4JsonXml();
	if(retList!=null){
		for(TestDto testDto:retList){
			ExtBeanWrapper extBeanWrapper=testDto.getExtcol();
			ExtEntity extEntity=(ExtEntity) extBeanWrapper.getObj(ExtEntity.class);
			System.out.println(extEntity.getInsureNum());
		}
	}
	return retList;
}

对于返回结果中有List字段且list中有子对象的情况，使用public T getObj(TypeReference type)方法设置子对象类型，进行自动转换。

public void getSubEntity4JsonXml(){
    List<TestDto> retList=microTestMapper.getInfo4JsonXml();
    if(retList!=null){
       for(TestDto testDto:retList){
            ExtBeanWrapper extBeanWrapper=testDto.getExtcol();
            ExtEntity extEntity= extBeanWrapper.getObj(new TypeReference<ExtEntity<SubEntity>>(){});
            System.out.println(extEntity.getInsureNum());
        }
    }
    return ; }

Mysql5.7.22之前的版本只能做json字段的整体更新或执行特定子元素的更新

进行json字段整体更新

sql示例

  <update id="updateJsonAll" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=#{extcol}) where id=#{id}
  </update>

json字段整体更新service层示例

TestDto testDto=new TestDto();
testDto.setId(1);
ExtBeanWrapper extBeanWrapper=new ExtBeanWrapper();
ExtEntity extEntity=new ExtEntity();
extEntity.setInsureNum(123123);
extBeanWrapper.setObj(extEntity);
testDto.setExtcol(extBeanWrapper);
microTestMapper.updateJsonAll(testDto);

更新结果示例

假设json字段原始数据为

{"insureNum":1000,"insureType":"car",contacts:["13512345678","13512345679"]}

更新后数据为

{"insureNum":123123}

使用json_set进行json字段指定子元素更新

sql示例

  <update id="updateJsonSubcol" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=json_set(extcol,'$.insureNum', #{extcol.innerMap.insureNum}) where id=#{id}
  </update>

json字段指定子元素更新service层示例

TestDto testDto=new TestDto();
testDto.setId(1);
ExtBeanWrapper extBeanWrapper=new ExtBeanWrapper();
ExtEntity extEntity=new ExtEntity();
extEntity.setInsureNum(123123);
extBeanWrapper.setObj(extEntity);
testDto.setExtcol(extBeanWrapper);
microTestMapper.updateJsonSubcol(testDto);

更新结果示例

假设json字段原始数据为

{"insureNum":1000,"insureType":"car",contacts:["13512345678","13512345679"]}

更新后数据为

{"insureNum":123123,"insureType":"car",contacts:["13512345678","13512345679"]}

Mysql5.7.22+版本能做到json字段的动态局部更新

使用json_merge_patch做json字段的动态局部更新示例

  <update id="updateJson" parameterType="com.nh.micro.demo.entity.TestDto">
	update micro_test set extcol=json_merge_patch(extcol, #{extcol}) where id=#{id}
  </update>

json字段指定子元素更新service层示例

TestDto testDto=new TestDto();
testDto.setId(1);
ExtBeanWrapper extBeanWrapper=new ExtBeanWrapper();
ExtEntity extEntity=new ExtEntity();
extEntity.setInsureNum(123123);
extBeanWrapper.setObj(extEntity);
testDto.setExtcol(extBeanWrapper);
microTestMapper.updateJson(testDto);

更新结果示例

假设json字段原始数据为

{"insureNum":1000,"insureType":"car",contacts:["13512345678","13512345679"]}

更新后数据为

{"insureNum":123123,"insureType":"car",contacts:["13512345678","13512345679"]}

在进行插入和更新操作时，如果想将ExtEntity中值为null的字段也转为json，则需要设置ExtBeanWrapper.setIgnoreNull(false)

如果与MybatisPlus框架整合，需做如下定制（只与标准mybatis框架整合不必做以下改动）

修改mybatisplus的AutoSqlInjector代码

private String getPlaceTag(String row){
	int start=row.indexOf("#{");
	int end=row.indexOf("}")+1;
	String temp=row.substring(start,end);
	System.out.println(temp);
	return temp;
}
private String getColTag(String row){
	int end=row.indexOf("=#{");
	int start=0;
	if(row.contains("<if")){
		start=row.indexOf(">")+1;
	}
	String temp=row.substring(start,end);
	System.out.println(temp);
	return temp;
}
private String createNewPlace(String colTag,String placeTag){
	String temp="json_merge_patch("+colTag+","+placeTag+")";
	return temp;
}
protected void injectUpdateByIdSql(boolean selective, Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
    SqlMethod sqlMethod = selective ? SqlMethod.UPDATE_BY_ID : SqlMethod.UPDATE_ALL_COLUMN_BY_ID;
    String temp=sqlSet(selective, table, "et.");
    String osql=temp;
    if(selective){
        String[] tempArray=temp.split("\n\t");
        StringBuilder sb=new StringBuilder("");
        for(String row:tempArray){
        	if(row.contains("typeHandler")){
        		System.out.println(getPlaceTag(row));
        		String placeTag=getPlaceTag(row);
        		System.out.println(getColTag(row));
        		String colTag=getColTag(row);
        		String nPlaceTag=createNewPlace(colTag, placeTag);
        		System.out.println(nPlaceTag);
        		row=row.replace(placeTag, nPlaceTag);
        		sb.append(row).append("\n\t");
        	}else{
        		sb.append(row).append("\n\t");
        	}
        }
        osql=sb.toString();
    }
    String sql = String.format(sqlMethod.getSql(), table.getTableName(), osql, table.getKeyColumn(),
            "et." + table.getKeyProperty(),
            "<if test=\"et instanceof java.util.Map\">"
                    + "<if test=\"et.MP_OPTLOCK_VERSION_ORIGINAL!=null\">"
                    + "and ${et.MP_OPTLOCK_VERSION_COLUMN}=#{et.MP_OPTLOCK_VERSION_ORIGINAL}"
                    + "</if>"
                    + "</if>"
    );
    System.out.println(sql);
    SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
    this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
}

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持好代码网。

全站频道

大家都在搜索：

概述

Mysql常见json字段操作

插入json字段

查询json字段

修改json字段

Mybatis使用Json字段

Mybatis深度整合Json字段

Extcol组件的Demo

总结