Two ways to BULK insert in MyBatis (insert efficiently) _java

MyBatis Introduction

MyBatis is an excellent persistence layer framework that supports common SQL queries, stored procedures, and advanced mappings. MyBatis eliminates the manual setting of almost all JDBC code and parameters and the retrieval encapsulation of the result set. MyBatis can use simple XML or annotations for configuration and raw mappings, mapping interfaces and Java Pojo (Plain old Java Objects, normal Java objects) to records in the database.

First, mybiats foreach label

The main use of foreach is in the build in condition, which can iterate over a collection in an SQL statement. The properties of a foreach element are mainly item,index,collection,open,separator,close. Item represents the alias for each element of the collection when it is iterated, and index specifies a name that represents where each iteration is in the iteration, open indicates what the statement starts with, and separator represents what symbol to use as a separator between iterations, Close indicates what ends with foreach, and the most critical and error-prone is the collection property, which must be specified, but in different cases the value of the property is not the same, mainly for 3 things:

If a single parameter is passed in and the parameter type is a list, the collection property value is List

If a single parameter is passed in and the parameter type is an array, the collection property value is array

If the incoming arguments are multiple, we need to encapsulate them into a map.

The specific usage is as follows:

<insert id= "Insertbatch" parametertype= "List" >
insert INTO Tstudent (name,age)
<foreach collection= "List" item= "item" index= "Index" open= "(" close= ")" separator= "union All" >
SELECT #{item.name} as a, #{item.age} As b from DUAL
</foreach>
</insert>

Second, MyBatis Executortype.batch

The MyBatis built-in Executortype has 3 kinds, the default is simple, which creates a new preprocessing statement for each statement execution, a single commit to SQL, and batch mode repeats the preprocessed statements and executes all the update statements in bulk. Obviously batch performance will be better, but the batch model has its own problems, such as in the insert operation, there is no way to obtain the ID when the transaction is not committed, which in a case is not in line with the business requirements

The specific usage is as follows:

* Method One, spring+mybatis

Get sqlsession
//from spring into the original sqlsessiontemplate
@Autowired
Private sqlsessiontemplate Sqlsessiontemplate;
A new session
/////If Autocommit is set to true, the number of batch submitted will not be controlled, and the last uniform commit may result in a memory overflow
sqlsession session = Sqlsessiontemplate.getsqlsessionfactory (). Opensession (Executortype.batch,false);
Obtains mapper
Foomapper = Session.getmapper (Foomapper.class) through the new session;
int size = 10000;
try{for
(int i = 0; i < size; i++) {
foo foo = new Foo ();
Foo.setname (String.valueof (System.currenttimemillis ()));
Foomapper.insert (foo);
if (i% 1000 = = 0 | | i = = size-1) {
//manual Every 1000 submissions, can not be rolled back after submission 
session.commit ();
Cleanup cache, preventing overflow
session.clearcache ();}}
catch (Exception e) {
//No committed data can be rolled
back Session.rollback ();
} finally{
session.close ();
}
Spring+mybatis

*Method Two:

Combined with Universal Mapper SQL alias preferably package name + class name

public void insertBatch(Map<String,Object> paramMap, List<User> list) throws Exception {
// Get a new session whose mode is BATCH and automatically submitted as false
// If the automatic submission is set to true, you will not be able to control the number of submissions, and change to the last unified submission, which may cause memory overflow
SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
try {
if(null != list || list.size()>0){
int lsize=list.size();
for (int i = 0, n=list.size(); i <n; i++) {
User user = list.get(i);
user.setIndate((String)paramMap.get("indate"));
user.setDatadate((String)paramMap.get("dataDate"));//Data attribution time
//session.insert("com.xx.mapper.UserMapper.insert",user);
//session.update("com.xx.mapper.UserMapper.updateByPrimaryKeySelective",_entity);
session.insert("package name + class name", user);
if ((i>0 && i% 1000 == 0) || i == lsize-1) {
// Manually submit every 1000, and cannot be rolled back after submission
session.commit();
// Clean up the cache to prevent overflow
session.clearCache();
}
}
}
} catch (Exception e) {
// Data that is not submitted can be rolled back
session.rollback();
e.printStackTrace();
} finally {
session.close();
}
} 

The above is a small set of mybatis to introduce the two ways of mass insertion (efficient insertion), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch