Introduction to MyBatis

MyBatis is an excellent persistence layer framework that supports common SQL queries, stored procedures and advanced mapping. MyBatis eliminates almost all the manual setting of JDBC code and parameters and the retrieval encapsulation of result sets. MyBatis can use simple XML or annotations for configuration and original mapping, and map the interface and Java POJO (Plain Old Java Objects) into records in the database.

1, Mybias foreach tag

Foreach is mainly used to build in conditions. It can iterate a set in SQL statements. The attributes of foreach elements mainly include item, index, collection, open, separator and close. Item indicates the alias of each element in the collection during iteration, index specifies a name, which is used to indicate the location of each iteration in the iteration process, open indicates what the statement starts with, separator indicates what symbol is used as the separator between each iteration, and close indicates what it ends with, When using foreach, the most critical and error prone attribute is the collection attribute, which must be specified, but the value of this attribute is different in different cases. There are three main cases:

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 attribute value of collection is array

If there are multiple parameters passed in, 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>

mybatis ExecutorType.BATCH

There are three built-in executortypes in Mybatis. The default is simple. In this mode, it creates a new preprocessing statement for the execution of each statement and submits a single sql; The batch mode repeats the preprocessed statements and executes all update statements in batch. Obviously, the batch performance will be better; However, the batch mode also has its own problems. For example, during the Insert operation, there is no way to obtain the self increasing id before the transaction is committed, which does not meet the business requirements in some cases

The specific usage is as follows:

*Mode 1: spring+mybatis

//Get sqlsession
//Inject the original sqlSessionTemplate from spring
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
// Obtain a new session with the mode of BATCH and the automatic submission of false
// If Auto submit is set to true, the number of submitted items cannot be controlled. The last unified submission may lead to memory overflow
SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
//Get mapper through new session
fooMapper = session.getMapper(FooMapper.class);
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) {
//Manually commit every 1000, and cannot roll back after committing 
session.commit();
//Clean up the cache to prevent overflow
session.clearCache();
}
}
} catch (Exception e) {
//No committed data can be rolled back
session.rollback();
} finally{
session.close();
}
spring+mybatis

Method 2:

Combined with general mapper, the SQL alias is preferably package name + class name

public void insertBatch(Map<String,Object> paramMap, List<User> list) throws Exception {
// Obtain a new session with the mode of BATCH and the automatic submission of false
// If Auto submit is set to true, the number of submitted items cannot be controlled. The last unified submission may lead to 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 commit every 1000, and cannot roll back after committing
session.commit();
// Clean up the cache to prevent overflow
session.clearCache();
}
}
}
} catch (Exception e) {
// No committed data can be rolled back
session.rollback();
e.printStackTrace();
} finally {
session.close();
}
}