Mybatis深度整合Mysql的Json字段問(wèn)題

更新時(shí)間：2023年12月07日 09:43:03 作者：thethefighter

這篇文章主要介紹了Mybatis深度整合Mysql的Json字段問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

概述

以前當(dāng)業(yè)務(wù)數(shù)據(jù)結(jié)構(gòu)變化時(shí)，往往需要采用的方案是：

修改表結(jié)構(gòu)增加字段

遇到數(shù)據(jù)結(jié)構(gòu)有l(wèi)ist結(jié)構(gòu)時(shí)，新建1對(duì)多的關(guān)聯(lián)子表

用字典表表示字段的增加

以上方案對(duì)代碼侵入性很強(qiáng)，同時(shí)與舊業(yè)務(wù)數(shù)據(jù)結(jié)構(gòu)不兼容。導(dǎo)致代碼從實(shí)體類(lèi)、Dao、Service、Controller層都要修改。

隨著NOSQL數(shù)據(jù)庫(kù)的廣泛應(yīng)用，可擴(kuò)展的存儲(chǔ)方式在關(guān)系型數(shù)據(jù)庫(kù)中也有了很好的支持，最新的MySQL5.7中就新增加了一個(gè)數(shù)據(jù)類(lèi)型JSON，使用mysql的json類(lèi)型字段做擴(kuò)展字段，可以以json串形式動(dòng)態(tài)的存儲(chǔ)任意結(jié)構(gòu)的數(shù)據(jù)，包括list結(jié)構(gòu)的數(shù)據(jù)也不必再創(chuàng)建子表。代碼的實(shí)體類(lèi)和Dao層不必修改，其他層代碼修改量也能夠減少。

在這里插入圖片描述

Mysql常見(jiàn)json字段操作

Mysql5.7開(kāi)始支持json字段

創(chuàng)建帶有json字段的表micro_test,其中extcol為json類(lèi)型字段

CREATE TABLE `micro_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meta_name` varchar(100) DEFAULT NULL COMMENT '元數(shù)據(jù)名稱(chēng)',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
  `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());

查詢(xún)json字段

可以根據(jù)path查詢(xún)json字段中全部或部分?jǐn)?shù)據(jù)

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

修改json字段

可以根據(jù)path局部更新json字段中數(shù)據(jù)

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

Mysql5.7.22版本以后支持JSON_MERGE_PATCH

可以省略path參數(shù)，全面更新json字段中數(shù)據(jù)

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

Mybatis使用Json字段

按照mybatis常規(guī)方式把json函數(shù)寫(xiě)入到xml文件中的sql中，即可支持json字段增刪改查。

但查詢(xún)出的json字段為字符串類(lèi)型，需要手工轉(zhuǎn)成bean，插入時(shí)需手工把bean轉(zhuǎn)成json字符串，這樣做不利于面向?qū)ο缶幊獭?/p>

在這里插入圖片描述

Mybatis深度整合Json字段

實(shí)現(xiàn)bean與json串在mybatis內(nèi)部轉(zhuǎn)換，這樣做的優(yōu)點(diǎn)是dao層代碼和sql不變，service層可以增刪改查不同的動(dòng)態(tài)Entity對(duì)象。更符合面向?qū)ο缶幊塘?xí)慣提高開(kāi)發(fā)效率。

在這里插入圖片描述

Extcol開(kāi)源項(xiàng)目實(shí)現(xiàn)Mybatis與mysql的json字段深度整合

項(xiàng)目地址為：

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子類(lèi)TagToJsonTypeHandler 實(shí)現(xiàn)mybatis在數(shù)據(jù)庫(kù)操作過(guò)程中的參數(shù)輸入和結(jié)果轉(zhuǎn)換的攔截。攔截父類(lèi)為ExtBeanWrapper的對(duì)象。

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

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

Extcol包中ExtBeanWrapper類(lèi)，作為json對(duì)象轉(zhuǎn)換的目標(biāo)對(duì)象，內(nèi)有map成員變量(innerMap)保存實(shí)際數(shù)據(jù),getobj和setobj方法是使用fastjson做對(duì)象與map的轉(zhuǎn)換。

Extcol組件的Demo

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

引入和配置好extcol后，在demo業(yè)務(wù)系統(tǒng)工程中編寫(xiě)對(duì)應(yīng)micro_test表的實(shí)體類(lèi)TestDto，其中json字段的成員變量類(lèi)型是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; }
}

擴(kuò)展字段業(yè)務(wù)bean

例如擴(kuò)展bean為ExtEntity（保險(xiǎn)訂單）有3個(gè)在數(shù)據(jù)庫(kù)中json字段動(dòng)態(tài)存儲(chǔ)的字段insureNum（保險(xiǎn)單號(hào)）、insureType(保險(xiǎn)類(lèi)型)、contacts(聯(lián)系電話(huà))

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為更新和插入時(shí)的參數(shù)操作時(shí)，mybatis將負(fù)責(zé)將bean轉(zhuǎn)為json串。

當(dāng)執(zhí)行查詢(xún)語(yǔ)句時(shí)，返回的結(jié)果映射到ExtBeanWrapper 類(lèi)型的字段時(shí)，mybatis將負(fù)責(zé)將json串轉(zhuǎn)為ExtBeanWrapper ，且這個(gè)ExtBeanWrapper 可以按照不同的業(yè)務(wù)bean自適應(yīng)轉(zhuǎn)化。

<?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 {
    //創(chuàng)建記錄
    public void createJson(TestDto testDto);
    //查詢(xún)記錄
    public List<TestDto> getInfo4JsonXml();
    //動(dòng)態(tài)局部更新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>

查詢(xún)結(jié)果中取JSON字段中存儲(chǔ)的業(yè)務(wù)

類(lèi)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;
}

對(duì)于返回結(jié)果中有List字段且list中有子對(duì)象的情況，使用public T getObj(TypeReference type)方法設(shè)置子對(duì)象類(lèi)型，進(jìn)行自動(dòng)轉(zhuǎn)換。

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字段的整體更新或執(zhí)行特定子元素的更新

進(jìn)行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);

更新結(jié)果示例

假設(shè)json字段原始數(shù)據(jù)為

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

更新后數(shù)據(jù)為

{"insureNum":123123}

使用json_set進(jìn)行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);

更新結(jié)果示例

假設(shè)json字段原始數(shù)據(jù)為

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

更新后數(shù)據(jù)為

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

Mysql5.7.22+版本能做到j(luò)son字段的動(dòng)態(tài)局部更新

使用json_merge_patch做json字段的動(dòng)態(tài)局部更新示例

  <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);

更新結(jié)果示例

假設(shè)json字段原始數(shù)據(jù)為

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

更新后數(shù)據(jù)為

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

在進(jìn)行插入和更新操作時(shí)，如果想將ExtEntity中值為null的字段也轉(zhuǎn)為json，則需要設(shè)置ExtBeanWrapper.setIgnoreNull(false)

如果與MybatisPlus框架整合，需做如下定制（只與標(biāo)準(zhǔn)mybatis框架整合不必做以下改動(dòng)）

修改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);
}