BULK INSERT in MyBatis

BULK Insert in MyBatis

Method One:

<insert id= "Insertbatch" parametertype= "Java.util.List" >

<selectkey keyproperty= "Fetchtime" order= "before"

Resulttype= "Java.lang.String" >

SELECT Current_timestamp ()

</selectKey>

Insert into Kangaiduoyaodian (Depart1, Depart2, Product_Name,

Generic_name, IMG, product_specification, Unit,

Approval_certificate, Manufacturer, Marketprice, Vipprice,

website, fetch_time, Productdesc) values

<foreach collection= "List" item= "item" index= "Index"

Separator= "," >

(#{item.depart1}, #{item.depart2}, #{item.productname},

#{item.genericname}, #{item.img},

#{item.productspecification}, #{item.unit},

#{item.approvalcertificate}, #{item.manufacturer},

#{item.marketprice}, #{item.vipprice}, #{item.website},

#{fetchtime}, #{item.productdesc})

</foreach>

</insert>

Method Two:

<insert id= "batchinsertb2b" parametertype= "ArrayList" >
Insert into xxxxtable (Hkgs,hkgsjsda,office,asdf,ddd,ffff,supfullname,classtype,agent_type,remark)
<foreach collection= "List" item= "item" index= "index" separator= "union All" >
Select #{item.hkgs,jdbctype=varchar},
#{item.hkgsjsda,jdbctype=varchar},
#{item.office,jdbctype=varchar},
#{item.asdf,jdbctype=varchar},
#{item.ddd,jdbctype=varchar},
#{item.ffff,jdbctype=varchar},
#{item.supfullname,jdbctype=varchar},0,0,
#{item.remark,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 Deletion in MyBatis

<!--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>

mybatis in clause

MyBatis in Parameter use method

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= "selectproduct" resultmap= "Map" >

SELECT *

From PRODUCT

WHERE Productno in

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

#{productno}

</foreach>

</select>

MyBatis Batch Modification

<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>

MyBatis's experience on the operation of batch data

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:
1 < Insert id = "Insert" ParameterType = "Sdc.mybatis.test.Student" >
2 <!--warning-@mbggenerated This element was automatically generated by
3 MyBatis Generator, does not modify. This element is generated on Mon 09
4 11:09:37 CST 2011. -->
5 INSERT into student (ID, name, sex,
6 address, telephone, t_id
7)
8 Values (#{id,jdbctype=integer}, #{name,jdbctype=varchar},
9 #{sex,jdbctype=varchar},
#{address,jdbctype=varchar}, #{telephone,jdbctype=varchar}, #{tid,jdbctype=integer}
11)
</Insert >

I do not know where the reason, on the configuration of the MyBatis log4j, want to view the next log. Download the Log4j.jar and Commons-logging.jar and configure the classpath to the project, and then create a new file log4j.properties under the code path, which reads as follows:
Log4j.rootlogger=debug, stdout

# Sqlmap Logging configuration ...
Log4j.logger.com.ibatis=debug
Log4j.logger.com.ibatis.common.jdbc.simpledatasource=debug
Log4j.logger.com.ibatis.sqlmap.engine.cache.cachemodel=debug
Log4j.logger.com.ibatis.sqlmap.engine.impl.sqlmapclientimpl=debug
Log4j.logger.com.ibatis.sqlmap.engine.builder.xml.sqlmapparser=debug
Log4j.logger.com.ibatis.common.util.stopwatch=debug
Log4j.logger.java.sql.connection=debug
Log4j.logger.java.sql.statement=debug
Log4j.logger.java.sql.preparedstatement=debug
Log4j.logger.java.sql.resultset=debug

# Console Output ...
Log4j.appender.stdout=org.apache.log4j.consoleappender
Log4j.appender.stdout.layout=org.apache.log4j.patternlayout
log4j.appender.stdout.layout.conversionpattern=%5p [%t]-%m%n then test the normal sqlsession again, found that although 1000 data were inserted in the log content, only one new connection was created. The connection was finally closed (log below). That is to say, the normal sqlsession in MyBatis seems to have been done for bulk inserts by default in a single connection. Then also provide executortype.batch way to do, and this way seems to be inefficient, or I use Executortype.batch way wrong.
DEBUG [main]-Created connection 3502256.
DEBUG [main]-ooo Connection opened
DEBUG [main]-==> Executing:insert into student (name, sex, address, telephone, t_id) values (?,?,?,?, ? )
DEBUG [main]-==> Parameters: Newcomer 0 (String), male (string), Addr0 (String), DD (String), 3 (Integer)
DEBUG [main]-==> Executing:insert into student (name, sex, address, telephone, t_id) values (?,?,?,?, ? )
DEBUG [main]-==> Parameters: Newcomer 1 (string), male (string),
...............
...............
DEBUG [main]-xxx Connection Closed
DEBUG [main]-returned connection 3502256 to pool. The last point is about SQL statement-level optimization for database bulk inserts, and I've 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 indeed much faster than the former. Here are two insert modes and the corresponding graphs of the test results:
<!--called 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}, #