Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Mybatis deeply integrates the Json field of Mysql

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/03 Report--

Overview

In the past, when the business data structure changed, the solutions that often needed to be adopted were:

 modifies table structure to add fields

When  encounters a data structure with list structure, create a new one-to-many associated child table

 uses a dictionary table to represent the increase of fields.

The above scheme is very intrusive to the code and is not compatible with the old business data structure. Causes the code to change from the entity class

, Dao, Service, Controller layers all need to be modified.

With the wide application of NOSQL database, the extensible storage mode has been well supported in relational database. A new data type JSON has been added to the latest MySQL5.7. Using the json type field of mysql as the extended field, the data of any structure can be dynamically stored in the form of json string, including the data of list structure, and there is no need to create subtables. The entity class and Dao layer of the code do not need to be modified, and the amount of code modification of other layers can also be reduced.

Common json field operations in Mysql

Mysql5.7 begins to support json field

Create a table micro_test with a json field, where extcol is a field of type json

CREATE TABLE `micro_ test` (`id` int (11) NOT NULL AUTO_INCREMENT, `meta_ name` varchar 'metadata name', `create_ time`datetime DEFAULT NULL COMMENT 'creation time', `update_ time`datetime DEFAULT NULL COMMENT 'update time', `extcol` json DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB CHARSET=utf8

Insert json field

Json fields can be inserted according to the json string

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

Query json field

You can query all or part of the data in the json field according to path

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

Modify the json field

The data in the json field can be updated locally according to path

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

JSON_MERGE_PATCH is supported after the Mysql5.7.22 version.

You can omit the path parameter and fully update the data in the json field.

Update micro_test set extcol=json_set (extcol,' {"name": "N1", "age": 30}') where meta_name='123'

Mybatis uses the Json field

If you write the json function to the sql in the xml file in accordance with the usual mybatis method, you can support the addition, deletion, modification and query of the json field. However, the json field queried is a string type, which needs to be manually converted into bean, and the bean needs to be manually converted into json string when inserting, which is not conducive to object-oriented programming.

Mybatis deeply integrates Json fields

Realize the conversion of bean and json strings within mybatis. The advantage of this is that the dao layer code and sql remain unchanged, and the service layer can add, delete, modify and check different dynamic Entity objects. More in line with object-oriented programming habits to improve development efficiency.

Extcol open source project realizes the deep integration of json fields of Mybatis and mysql

The project address is:

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

Pom references the jar of extcol

Com.github.jeffreyning extcol 0.0.1-RELEASE

The TypeHandler subclass TagToJsonTypeHandler in the Extcol package implements the interception of mybatis's parameter input and result transformation in the process of database operation. Intercepts objects whose parent class is ExtBeanWrapper.

Configuration is required for TagToJsonTypeHandler to take effect

Mybatis-plus: typeHandlersPackage: com.nh.micro.ext.th

The ExtBeanWrapper class in the Extcol package, as the target object of the json object transformation, contains map member variables to hold the actual data. The getobj and setobj methods use fastjson to convert the object to map.

Demo using Extcol

After introducing and configuring extcol, write the entity class TestDto corresponding to micro_ test table in demo business system engineering, where the member variable type of json field is ExtBeanWrapper.

Public class TestDto {private Integer id; private String metaKey; private String metaName; private String metaType; private Date createTime; private ExtBeanWrapper extcol; public ExtBeanWrapper getExtcol () {return extcol;} public void setExtcol (ExtBeanWrapper extcol) {}

Extended field service bean

For example, the extension bean is ExtEntity and there are two fields T1 and T2 that are dynamically stored in the json field in the database

Public class ExtEntity {private String T1; private String T2; public String getT1 () {return T1;} public void setT1 (String T1) {this.t1 = T1;} public String getT2 () {return T2;} public void setT2 (String T2) {this.t2 = T2;}}

When TestDto is used as the parameter for updating and inserting, mybatis will be responsible for converting bean to json string.

Insert into micro_test (meta_name,extcol,create_time) values (# {metaName}, # {extcol}, now ()

When the query statement is executed and the returned results are mapped to fields of type ExtBeanWrapper, mybatis will be responsible for converting the json string into ExtBeanWrapper, and this ExtBeanWrapper can be adaptively converted according to different business bean.

SELECT * from micro_test where meta_name=# {name}

Fetch the code of the business class ExtEntity stored in the JSON field in the query result

Public List testQuery4JsonXml (String name) {List retList=testDao.getInfo4JsonXml (name); if (retListrated null) {for (TestDto testDto:retList) {ExtBeanWrapper extBeanWrapper=testDto.getExtcol (); ExtEntity extEntity= (ExtEntity) extBeanWrapper.getObj (ExtEntity.class); System.out.println (extEntity.getT1 ());}} return retList;}

Use JSON_MERGE_PATCH when updating bean for Mybatisplus

Modify the AutoSqlInjector of mybatisplus

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 (") + 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 () "" + "+" and ${et.MP_OPTLOCK_VERSION_COLUMN} = # {et.MP_OPTLOCK_VERSION_ORIGINAL} "+" + ") System.out.println (sql); SqlSource sqlSource = languageDriver.createSqlSource (configuration, sql, modelClass); this.addUpdateMappedStatement (mapperClass, modelClass, sqlMethod.getMethod (), sqlSource);}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report