In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.