Database--mybatis (insert,update,delete) Three kinds of bulk operations

Transferred from: http://blog.csdn.net/starywx/article/details/23268465

Some time ago because the project was in a hurry.performance issues in the development process, the process of optimizing some of the code has found that in the case of large amounts of data, the operation of the data seems to be slow, the thought of database DML operations in bulk operations. It also reminds me that you don't ask questions about bulk operation data during an interview. The application notes are recorded as follows: Bulk Insert method One: <insert id= "Insertbatch" parametertype= "java.util.List" > <selectkey keyproperty= "id" order= "after" resulttype= "int" > select last_insert_id () </selectKey> INSERT into SourceDoc (Sdate, SWE  Ek, Roomno, daysched, nightsched, adminsched, vacationsched, Programdept, ProgramName) Values<foreach collection= "List" item= "item" index= "index" separator= "," > (#{item.sdate},#{item.sweek},#{item.roomno},#{ item.daysched},#{item.nightsched},#{item.adminsched},#{item.vacationsched},#{item.programdept},#{ Item.programname}) </foreach></insert>

Method Two: <insert id= "Batchinsert" parametertype= "ArrayList" >insert into table1 (sdate,sweek,roomno,daysched, Nightsched,adminsched,vacationsched,programdept,programname) <foreach collection= "list" item= "Item" index= " Index "separator=" union All ">select #{item.sdate,jdbctype=varchar},#{item.sweek,jdbctype=varchar},#{ item.roomno,jdbctype=varchar},#{item.nightsched,jdbctype=varchar},#{item.adminsched,jdbctype=varchar},#{ Item.vacationsched,jdbctype=varchar},#{item.programdept,jdbctype=varchar},0,0,#{item.programname,jdbctype= VARCHAR} from dual</foreach></insert> you can consider using union all to implement bulk insertions. For example: INSERT INTO xx_table (xx,xx,xx) Select ' xx ', ' xx ', ' xx ' union ALL select ' xx ', ' xx ', ' xx ' union ALL select ' xx ', ' xx ', ' xx '. . First assemble the statement and then dynamically pass in the insert into Xx_table (XX,XX,XX) later section
Bulk Delete <!--bulk delete records via primary key collection--><delete id= "Batchremoveuserbypks" parametertype= "Java.util.List" > DELETE from Ld_user WHERE ID in <foreach item= "item" index= "index" collection= "list" open= "(" separator= "," close= ")" & Gt #{item} </foreach></delete>
Bulk modification (update) <update id= "Updateorders" parametertype= "java.util.List" > Update orders set state = ' 0 ' where no in &L T;foreach collection= "list" item= "Nos" open= "(" separator= "," close= ")" > #{nos} </foreach> </update>
MyBatis in clause in parameter use Method 1. Only one parameter of the type to be declared as a list or array SQL is configured as follows: <select id= "selectproduct" resultmap= "Map" > select * F                   ROM PRODUCT WHERE Productno in <foreach item= "Productno" index= "index" collection= "parameter type list or array" > #{productno} </foreach></select>
2. Multiple parameters first to write multiple parameters to the same map, the map as a parameter passed mapper SQL configuration as follows: <select id= "Selectsoucedoc" resultmap= "Map" > select * FROM PRODUCT WHERE Productno in <foreach item= "id" index= "index" collection= "name of collection parameter in map" > #{id} &lt ;/foreach> </select> Batch Data operation experience MyBatis's predecessor is the famous Ibatis, somehow separated from the Apache renamed to MyBatis. MyBatis is a lightweight ORM framework that has seen a test report on the web, feeling that the advantages are not obvious compared to hibernate. Here is a more interesting phenomenon, according to the official document of MyBatis, when obtaining sqlsession, it is specially prepared for batch update: Session = Sessionfactory.opensession ();// For normal updatesession = Sessionfactory.opensession (Executortype.batch, true);//For batch update generally speaking, the speed of batch operation on MySQL database depends on, is to establish a connection for each process, or to establish a connection for this batch of processing altogether. By MyBatis's manual, selecting Executortype.batch means that the obtained sqlsession will execute all UPDATE statements in bulk. But I tested, BULK INSERT 1000 data, found that the executortype.batch way of efficiency than the ordinary way much worse. The insert configuration in my test mapper is as follows, then insert 1000 records with A For loop: <insert id= "Insert" parametertype= "Sdc.mybatis.test.Student" > INSERT into student (ID, name, sex,address, telephone, t_id) VALUES (#{id,jdbctype=integer}, #{name,jdbctype=varchar},#{ Sex,jdbctype=varchar},#{addreSs,jdbctype=varchar}, #{telephone,jdbctype=varchar}, #{tid,jdbctype=integer}) </insert> about SQL statement-level optimizations for database bulk inserts, I have deliberately tested two ways to configure two insert modes in Studentmapper. The first corresponds to the insert Value1,insert value2,,,,; the second corresponds to insert values (value1, value2,....). found that the latter is much faster than the former. Here are the two insert modes, and the test results map: <!--batch, pass in a 1000-length List-->insert into student (<include refid= "Base_column_list"/& Gt Values <foreach collection= "list" item= "item" index= "index" separator= "," > (null,#{item.name},#{item.sex},#{ Item.address},#{item.telephone},#{item.tid}) </foreach></insert><!--call 1000 times in the external for loop-->< Insert id= "Insert" parametertype= "Sdc.mybatis.test.Student" >insert into Student (ID, name, sex,address, telephone, t _ID) VALUES (#{id,jdbctype=integer}, #{name,jdbctype=varchar},#{sex,jdbctype=varchar},#{address,jdbctype=varchar} , #{telephone,jdbctype=varchar}, #{tid,jdbctype=integer}) </insert>

　　

Database--mybatis (insert,update,delete) Three kinds of bulk operations