A general implementation of MyBatis dynamic batch insert and update MySQL database

First, business background

Due to the need to extract a large number of data from a database to the B system, the use of TOMIKOS+JTA for distributed transaction management, the system data source to switch to the data provider, the need to synchronize the data query, and then switch the system data source to the data receiver, BULK INSERT and update operations,

The switch to the data source can refer to the previous article, "Spring+springmvc+mybatis architecture uses Abstractroutingdatasource, Atomikos, JTA for flexible switching of multiple data sources and distributed transaction management"

Second, the specific implementation of bulk insertion

1. Query for data that needs to be synchronized:

    @Autowired    syspersonpomapper syspersonpomapper;          Public void datads () {        /// Depending on the situation, you can create a query condition or use a custom mapper to query the        new  Syspersonpoexample ();        Syspersonpoexample.createcriteria (). Andisdeleteequalto (false);         // querying for data        that needs to be synchronized list<syspersonpo> persons = syspersonpomapper.selectbyexample (syspersonpoexample);    }

2. To map a PO entity object that cannot be traversed, here is a precondition: table fields in MySQL are named by all uppercase and underlined, the Entity Class Po mapping field is the corresponding standard hump naming method, PO to map

, this rule is followed when the conversion occurs.

@Autowired Syspersonpomapper Syspersonpomapper;  Public voidDatads ()throwsexception{//1. Querying for data that needs to be synchronized//Depending on the situation, you can create a query condition or use a custom mapper to querySyspersonpoexample syspersonpoexample =Newsyspersonpoexample (); Syspersonpoexample.createcriteria (). Andisdeleteequalto (false); //querying for data that needs to be synchronizedlist<syspersonpo> persons =syspersonpomapper.selectbyexample (syspersonpoexample); //2. Convert PO entity objects that cannot be traversed to map//list to hold converted objectsList<map<string,object>> Insertitems =lists.newarraylist ();  for(Syspersonpo syspersonpo:persons) {Map<String,Object> InsertItem =Beanmaputil.convertbean2mapwithunderscorename (Syspersonpo);        Insertitems.add (InsertItem); }    }

　　

Beanmaputil class, PO to map conversion method, based on class reflection technology:

ImportCom.fms.common.utils.other.StringUtil;ImportJava.beans.BeanInfo;ImportJava.beans.Introspector;ImportJava.beans.MethodDescriptor;ImportJava.beans.PropertyDescriptor;ImportJava.lang.reflect.Method;ImportJava.sql.Timestamp;Importjava.util.Date;ImportJava.util.HashMap;ImportJava.util.Map;Importorg.apache.commons.beanutils.BeanUtils; Public classBeanmaputil {@SuppressWarnings ({"Unchecked", "Rawtypes"})     Public StaticMap convertbean2mapwithunderscorename (Object Bean)throwsException {Map Returnmap=NULL; Try{Class type=Bean.getclass (); Returnmap=NewHashMap (); BeanInfo BeanInfo=introspector.getbeaninfo (type); Propertydescriptor[] PropertyDescriptors=beanInfo. getpropertydescriptors ();  for(inti = 0; i < propertydescriptors.length; i++) {PropertyDescriptor descriptor=Propertydescriptors[i]; String PropertyName=Descriptor.getname (); if(!propertyname.equalsignorecase ("Class") {Method Readmethod=Descriptor.getreadmethod (); Object result= Readmethod.invoke (Bean,NewObject[0]);                Returnmap.put (Stringutil.underscorename (PropertyName), result); }            }        } Catch(Exception e) {//Throw server Exception Log when parsing error            Throw NewException ("Exception when converting from Bean to map!"), E); }        returnReturnmap; }}

Stringutil class, a method of converting between standard hump naming and database underline naming:

 Public classStringutil {/*** Converts a camel-named string to an underscore capitalization method. An empty string is returned if the pre-converted camel-named string is empty. </br> * For example: Helloworld->hello_world * *@paramname of the camel-named string before conversion *@returnA string named after the converted underscore capitalization*/     Public Staticstring Underscorename (string name) {StringBuilder result=NewStringBuilder (); if(Name! =NULL&& name.length () > 0) {            //process the first character into uppercaseResult.append (name.substring (0, 1). toUpperCase ()); //Loop through the remaining characters             for(inti = 1; I < name.length (); i++) {String s= Name.substring (I, i + 1); //underline before capital letters                if(S.equals (S.touppercase ()) &&! Character.isdigit (S.charat (0)) {result.append ("_"); }                //other characters go directly to uppercaseResult.append (S.touppercase ()); }        }        returnresult.tostring (); }}

3. Write the MyBatis mapping file Fmsdatadsmapper.xml

<?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.fms.common.dao.fmsDataDsMapper" ><!--BULK INSERT, incoming table name and collection of data to be inserted--    <insert id= " Insertdatas "parametertype=" map ">       INSERT INTO ${table_name}       <foreach collection=" Fields " index= "field"  item= "Fieldval" separator= "," open= "(" close= ")" >                ${field}       </foreach>                values         <foreach collection= "list" index= "index" item= "record" separator= "," >            < foreach collection= "Record" index= "key"  item= "item" separator= "," open= "(" close= ")" >                #{item}             </foreach>        </foreach>     </insert></mapper>

4. Call the Insert method of the Sqlsession-related API for inserting data:

@Autowired Syspersonpomapper Syspersonpomapper;  Public voidDatads ()throwsexception{//1. Querying for data that needs to be synchronized//Depending on the situation, you can create a query condition or use a custom mapper to querySyspersonpoexample syspersonpoexample =Newsyspersonpoexample (); Syspersonpoexample.createcriteria (). Andisdeleteequalto (false); //querying for data that needs to be synchronizedlist<syspersonpo> persons =syspersonpomapper.selectbyexample (syspersonpoexample); //2. Convert PO entity objects that cannot be traversed to map//list to hold converted objectsList<map<string,object>> Insertitems =lists.newarraylist ();  for(Syspersonpo syspersonpo:persons) {Map<String,Object> InsertItem =Beanmaputil.convertbean2mapwithunderscorename (Syspersonpo);        Insertitems.add (InsertItem); }                //3. Inserting DataInsertdatas (Insertitems, "Sys_person");    } @Autowired sqlsessiontemplate sqlsessiontemplate; PrivateString datadsnamespace = "Com.fms.common.dao.fmsDataDsMapper"; Private voidInsertdatas (list<map<string,object>>Insertitems, String tableName) {        if(!Insertitems.isempty ()) {Map<String,Object> params =Maps.newhashmap ();
This divides the data into every 1000 executions, which can be adjusted according to the actual situation.intCount = Insertitems.size ()/1000; intYu = insertitems.size ()% 1000;  for(inti = 0; I <= count; i++) {List<Map<String,Object>> sublist =lists.newarraylist (); if(i = =count) {                    if(Yu! = 0) {sublist= Insertitems.sublist (I * +, + * i +Yu); }Else {                        Continue; }                } Else{sublist= Insertitems.sublist (i + 1)); } params.put ("TABLE_NAME", TableName); Params.put ("Fields", Sublist.get (0)); Params.put ("List", sublist); Sqlsessiontemplate.insert (Datadsnamespace+ ". Insertdatas", params); }        }    }

Third, the specific implementation of batch update

Usually we update the statement according to the primary key is update table_name set column1 = val1, Column2 = Val2 [,......] WHERE id =? or using the Updatebyprimarykey interface provided by MyBatis,

When we want to update a large amount of data at once, each SQL can only perform one update, and MySQL supports another update method, allowing us to implement a single SQL statement to update multiple records based on the primary key:

On DUPLICATE KEY UPDATE

Note: This is different from updating a field in a table to the same value when the Where condition is met, but instead changing the value of each record.

1. To build the data that needs to be updated, it is important to note that if there are fields in the PO that you do not want to update, you have two ways to do so, one is to set the value of the field here as the original value in the database, and the other way is to make a map conversion.

Do not build this field into the map, this only needs to make some minor changes to the Convertbean2mapwithunderscorename method, you can pass the fields that do not need to be reserved as parameters to it, and then filter out when the conversion (the primary key cannot be omitted).

        // 4. Build volume-Updated data         // List to hold converted objects        list<map<string,object>> Updateitems = lists.newarraylist ();          for (Syspersonpo syspersonpo:persons) {            Syspersonpo.setcode (Syspersonpo.getcode ()+ "Updatetest") ;            Map<String,Object> updatetitem = beanmaputil.convertbean2mapwithunderscorename (Syspersonpo);            Updateitems.add (Updatetitem);        }

2. Write the MyBatis mapping file Fmsdatadsmapper.xml

<!--batch updates a field based on the primary key, passing in the table name and the collection of data that needs to be updated--<insert id= "Updatedatas" parametertype= "Map" >INSERT into ${table_name}<foreach collection= "Fields" index= "field" item= "Fieldval" separator= "," open= "(" close= ")" >${field}</foreach>VALUES<foreach collection= "list" index= "index" item= "record" separator= "," > <foreach collection= "Record" in dex= "Key" item= "item" separator= "," open= "(" close= ")" >#{item}</foreach> </foreach>On DUPLICATE KEY UPDATE<foreach collection= "Fields" index= "field" item= "Fieldval" separator= "," >${field}=VALUES (${field})</foreach> </insert>

3. Updating data

//5. Updating DataUpdatedatas (Updateitems, "Sys_person");Private voidUpdatedatas (list<map<string,object>>Updateitems, String tableName) {        if(!Updateitems.isempty ()) {Map<String,Object> params =Maps.newhashmap ();
This divides the data into every 1000 executions, which can be adjusted according to the actual situationintCount = Updateitems.size ()/1000; intYu = updateitems.size ()% 1000;  for(inti = 0; I <= count; i++) {List<Map<String,Object>> sublist =lists.newarraylist (); if(i = =count) {                    if(Yu! = 0) {sublist= Updateitems.sublist (I * +, + * i +Yu); }Else {                        Continue; }                } Else{sublist= Updateitems.sublist (i + 1)); } params.put ("TABLE_NAME", TableName); Params.put ("Fields", Sublist.get (0)); Params.put ("List", sublist); Sqlsessiontemplate.insert (Datadsnamespace+ ". Updatedatas", params); }        }    }

A general implementation of MyBatis dynamic batch insert and update MySQL database