My question: How to implement BULK INSERT and update operations under MyBatis.
My confusion:
1, seemingly mybatis does not support the XML configuration in the SQL with a semicolon ";", which requires as far as possible in the configuration through a SQL statement implementation;
2, different databases can be supported by the batch insert SQL statements are different, the Oracle batch insert SQL statements are as follows:
Writing: "INSERT all into a table values (each value) into a table values (other values) into a table values (other values) ...." And then with a SELECT statement "
Reference article: http://blog.csdn.net/chenleixing/article/details/45165761/
3, in the SQL statement patchwork batch INSERT statement, the data volume is not good after all, and not convenient to get the ID value of the inserted data.
4, if the exclusion of SQL statements to implement a BULK insert scheme, you can consider the use of Java code, JDBC or MyBatis implementation of the partial submission implementation.
Reference article: http://www.cnblogs.com/robinjava77/p/5530681.html
My implementation:
Weigh repeatedly, decide to adopt MyBatis batch to implement batch operation, at the same time this also can make the code more generality.
Batch Actuator:
Public abstract class Batchexecutor<t> {private list<t> List = null;
Public Batchexecutor (list<t> list) {this.list = list;
public void execute () throws Exception {if (Collectionutils.isempty (list)) {return;
} Sqlsessionfactorybean sqlsessionfactory = (Sqlsessionfactorybean) contextutils.getbean ("SqlSessionFactory");
Sqlsession batchsqlsession = null;
try {batchsqlsession = Sqlsessionfactory.getobject (). Opensession (Executortype.batch, false);
Basemapper basemapper = Batchsqlsession.getmapper (Basemapper.class);
int batchcount = 500;
for (int index = 0; index < list.size (); index++) {T object = list.get (index);
Doexecute (object, Basemapper);
if (index!= 0 && index% batchcount = 0) {batchsqlsession.commit ();
} batchsqlsession.commit ();
catch (Exception e) {batchsqlsession.rollback ();
Throw e;
finally {if (batchsqlsession!= null) {batchsqlsession.close ();
}} protected abstract void Doexecute (T object, Basemapper basemapper) throws Exception; }
Business methods:
@Override public
void Batchaddnew (list<t> List) throws Exception {
batchexecutor<t> executor = new Batchexecutor<t> (list) {
@Override
protected void Doexecute (T object, Basemapper basemapper) throws Exception {
AddNew (object, Basemapper);
}
};
Executor.execute ();
}
This article summarizes the writing is very good, extracts the reference as follows:
----------------------------------------------------------------------------
Bulk Insert data (based on MyBatis implementation-oracle)
Objective: To do a data synchronization project, require: The synchronization of data is not lost, improve the insertion performance.
Project DB Framework: MyBatis. Database:oracle.
----------------------------------------------------------------------------
Bulk INSERT Data mode:
One, MyBatis global set batch processing;
Second, MyBatis local set batch processing;
Three, MyBatis foreach BULK Insert:
①select UNION All;
②begin INSERT into ...; insert INTO ...; ...; End;
Four, Java with the batch processing inserts;
V. Other ways
-----------------------------------------------------------------------------
First of all, the conclusion: MyBatis (Global/local) batch processing and Java with the batch processing performance is similar to the optimal treatment, my side of the various tests, the final use of MyBatis local batch processing.
One, MyBatis global set batch processing
First, spring-mybatis.xml configuration information
1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <beans xmlns= "Http://www.springframework.org/schema/beans" 3 Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:context= "Http://www.springframework.org/schema/con Text "xmlns:tx=" HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/TX "5 xsi:schemalocation=" http://www.springframework.org/sc Hema/beans 6 Http://www.springframework.org/schema/beans/spring-beans.xsd 7 Http://www.springframework.org/schema/context 8 Http://www.springframework.org/schem A/context/spring-context.xsd Http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/ Spring-tx.xsd "> 9 <!--automatic scanning (automatic injection)--> <context:annotation-config/> <context:compo Nent-scan base-package= "Com.company.dao"/> <!--Dynamic Data source--> <bean id= "DataSource" class= "com.c Ompany.dao.datasource.DataSource ">
<property name= "Myconfigfile" value= "Mysource.xml"/> </bean> <!--mybatis configuration- -> <bean id= "sqlsessionfactory" class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property Name= "DataSource" ref= "DataSource"/> <property "name=" Mapperlocations "value=" /> <property name= "configlocation" value= "Classpath:/mybatisconfig.xml"/> </bean> 25 26 <!--automatically create a mapper without mapping--> <bean class= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > 2 individually for each mapper 8 <property name= "Basepackage" value= "Com.company.dao.mapper"/> "<property name=" sqlsessionfact Orybeanname "value=" Sqlsessionfactory "/> </bean> <!--transaction manager configuration, single data source transaction--> <bean Id= "TransactionManager" class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > 35 <property name= "DaTasource "ref=" DataSource "/> </bean> notoginseng <tx:annotation-driven transaction-manager=" transactionm Anager "/> </beans>
Then on Mybatisconfig.xml (in this project, I have not set up setting.) The resulting local batch, so there is no global batch set, after which the specific reason. )
1 <?mapper.xml version= "1.0" encoding= "UTF-8"?>
2 <! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" >
3 <configuration>
4
5 <settings>
6 <!--Configure the default actuator. Simple is an ordinary executor; the reuse executor reuses the preprocessing statement (prepared statements); BATCH The executor reuses the statement and performs a batch update. -->
7 <setting name= "Defaultexecutortype" value= "BATCH"/>
8 <!--details: http:// Www.mybatis.org/mybatis-3/zh/configuration.html-->
9 </settings> <! --List List--> <typeAliases> <!--typealiases configuration is configured alias, this is not posted-->
14 </typeAliases>
</configuration>
This way is set up after the Baseservice open Savebatch (list<t> List) method
1 @Override 2 public void Save (list<t> List) {3 for (int i = 0;i <
List.size (); i++) {4 Mapper.insert (List.get (i)); 5} 6} 7 8 @Override 9 public void Savebatch (list<t> List) {int size = List.si
Ze ();
one int unitnum = 500;
int startIndex = 0;