Several batch operations of MyBatis

BULK Insert in MyBatis

Method One:

<InsertID= "Insertbatch"ParameterType= "Java.util.List">    <SelectkeyKeyproperty= "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<foreachCollection= "List"Item= "Item"Index= "Index"Separator=",">(#{item.depart1}, #{item.depart2}, #{item.productname}, #{item.genericname}, #{item.img}, #{ite M.productspecification}, #{item.unit}, #{item.approvalcertificate}, #{item.manufacturer}, #{item.marketprice }, #{item.vipprice}, #{item.website}, #{fetchtime}, #{item.productdesc})</foreach></Insert>

Method Two:

<InsertID= "Batchinsertb2b"ParameterType= "ArrayList">INSERT INTO xxxxtable (Hkgs,hkgsjsda,office,asdf,ddd,ffff,supfullname,classtype,agent_type,remark)<foreachCollection= "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 insertions.

For example:

Insert  intoXx_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 in MyBatis

<!--bulk Delete records from a primary key collection -<DeleteID= "Batchremoveuserbypks"ParameterType= "Java.util.List">DELETE from Ld_user WHERE ID in<foreachItem= "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 parameter to declare as a list or array

The SQL configuration is as follows:

<SelectID= "Selectproduct"Resultmap= "Map">SELECT * from PRODUCT WHERE Productno in<foreachItem= "Productno"Index= "Index"Collection= "type of parameter list or array">#{productno}</foreach></Select>

2. Multiple parameters

The first thing to do is to write multiple parameters to the same map and pass the map as a parameter into the mapper

The SQL configuration is as follows:

<SelectID= "Selectproduct"Resultmap= "Map">SELECT * from PRODUCT WHERE Productno in<foreachItem= "Productno"Index= "Index"Collection= "Name of the collection parameter in map">#{productno}</foreach></Select>

MyBatis Batch Modification

<UpdateID= "Updateorders"ParameterType= "Java.util.List">Update orders set state = ' 0 ' where No. in<foreachCollection= "List"Item= "Nos"Open="("Separator=","Close=")">#{nos}</foreach></Update>

MyBatis's experience on batch data operation!

MyBatis's predecessor was the famous Ibatis, and somehow detached from the Apache renamed 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 MyBatis's official documentation, when obtaining sqlsession, it is specially prepared for batch updates:

Session = Sessionfactory.opensession (); // for normal update true); // for batch update

In general, the speed of batch operations on the MySQL database depends on whether a connection is established for each process, or a connection is established for this batch of processing. 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:

<InsertID= "Insert"ParameterType= "Sdc.mybatis.test.Student">    <!--warning-@mbggenerated This element was automatically generated by 3 MyBatis Generator, does not modify. This element is generated on Mon May 4 11:09:37 CST 2011.  -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}, #{tI D,jdbctype=integer})</Insert>

I do not know why, I configured 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, as follows:

Log4j.rootlogger=DEBUG, stdout# sqlmap logging Configuration...log4j.logger.com.ibatis=DEBUGlog4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUGlog4j.logger.com.ibatis.sqlmap.engine.cache.CacheModel=DEBUGlog4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientImpl=DEBUGlog4j.logger.com.ibatis.sqlmap.engine.builder.xml.SqlMapParser=DEBUGlog4j.logger.com.ibatis.common.util.StopWatch=DEBUGlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUGlog4j.logger.java.sql.ResultSet=debug# Console output...log4j.appender.stdout=Org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layout=Org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%5p [%t]-%m%n

Then test the normal sqlsession again, found that although the log content inserted 1000 data, but only a new connection, and finally closed the connection (log as follows). In other words, the normal sqlsession in MyBatis seems to have been done for bulk insert by default in a single connection. Then also provide executortype.batch way to do, moreover this way seems not efficient, or I use Executortype.batch way wrong??

 DEBUG [main]-Created connection 3502256. DEBUG [main] - ooo Connection Openeddebug [main] -==> Executing:insert I Nto 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 closeddebug [main] -returned Connection 3502256 to pool. 

The last point is about SQL statement-level optimizations 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 much faster than the former. Here are the two insert modes, and the corresponding graphs for the test results:

<!--called 1000 times in the external for loop -<InsertID= "Insert"ParameterType= "Sdc.mybatis.test.Student">INSERT into student (ID, name, sex, address, telephone, t_id) VALUES (#{id,jdbctype=integer}, #{name,jdb Ctype=varchar}, #{sex,jdbctype=varchar}, #{address,jdbctype=varchar}, #{telephone,jdbctype=varchar}, #{tId, Jdbctype=integer})</Insert><!--batch, pass in a list of length 1000 -<InsertID= "Insertbatch">INSERT INTO student (<includerefID= "Base_column_list" />) Values<foreachCollection= "List"Item= "Item"Index= "Index"Separator=",">(Null,#{item.name},#{item.sex},#{item.address},#{item.telephone},#{item.tid})</foreach></Insert>

This article transferred from: http://topmanopensource.iteye.com/blog/1833001

Several batch operations of MyBatis