Database--mybatis (Insert,update,delete) Three batch operations __ database

Some time ago due to the project is not in the process of development performance problems, the process of optimizing some of the code is found in the data in the case of a large amount of data on the operation of the response seems to be a little slow, think of the database DML operations when the bulk operation. Said here also think of yourself in an interview don't ask the problem of bulk operation data.

The application notes are recorded as follows:

Insert inserts in bulk

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, Sweek,
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 inserts.
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 (delete)

<!--bulk delete records by 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= ")" >

#{item}

</foreach>

</delete>

Batch modification (UPDATE)

<update id= "Updateorders" parametertype= "Java.util.List" >
Update orders set state = ' 0 ' where no in
<foreach collection= "list" item= "Nos" open= "(" separator= "," close= ")" >
#{nos}
</foreach>
</update>

Method of using in clause in parameter in MyBatis

1. Only one parameter

The type of the argument is declared as a list or array

The SQL configuration is as follows:

<select id= "selectproduct" resultmap= "Map" >

SELECT * from PRODUCT

WHERE Productno in

<foreach item= "Productno" index= "index" collection= "parameter type list or array" >

#{productno}

</foreach>

</select>

2. Multiple parameters

First, you write multiple parameters to the same map and pass the map as a parameter to the mapper

The SQL configuration is as follows:

<select id= "Selectsoucedoc" resultmap= "Map" >

SELECT *

From PRODUCT

WHERE Productno in

<foreach item= "id" index= "index" collection= "name of the collection parameter in map" >

#{id}

</foreach>

</select>

Experience of batch data operation

MyBatis's predecessor was the famous Ibatis, somehow divorced from the Apache renamed MyBatis.
MyBatis is a lightweight ORM framework that looks at a test report on the web and feels less advantageous than hibernate.

The interesting thing to say is that, according to MyBatis's official document, when Sqlsession is obtained, it is specially prepared for batch updates:

Session = Sessionfactory.opensession ()//For general update
Session = Sessionfactory.opensession (Executortype.batch, true);//For batch update

In general, the speed of bulk operations on MySQL databases depends on whether to establish a connection for each process, or to establish a connection for this batch of processing altogether. According to MyBatis's manual, selecting Executortype.batch means that the obtained sqlsession will execute all UPDATE statements in bulk. However, I tested a bit, insert 1000 data, found that the efficiency of Executortype.batch way is much worse than the ordinary way. The insert configuration in the Mapper I tested is as follows, and then insert 1000 records in the 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>