Introduction to MyBatis
MyBatis is an excellent persistence layer framework that supports plain SQL queries, stored procedures and advanced mapping. MyBatis eliminates manual settings of almost all JDBC code and parameters and search encapsulation of the result set. MyBatis can use simple XML or annotations for configuration and original mapping, mapping interfaces and Java's POJOs (Plain Old Java Objects) into records in the database.
1. Mybiats foreach tag
Foreach is mainly used in building in conditions, it can iterate over a collection in SQL statements. The attributes of the foreach element mainly include item, index, collection, open, separator, and close. Item represents the alias when each element in the collection is iterated. Index specifies a name to represent the position to which each iteration is reached during the iteration process. Open represents what starts with the statement, separator represents what symbols are used as separators between each iteration, and close represents what ends with. The most critical and most error-prone thing when using foreach is the collection attribute. This attribute must be specified, but in different cases, the value of the attribute is different. There are three main situations:
If the passed in is a single parameter and the parameter type is a List, the collection attribute value is list
If the passed in is a single parameter and the parameter type is an array, the property value of the 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>2. mybatis ExecutorType.BATCH
There are 3 types of ExecutorTypes built in Mybatis. The default is simple. In this mode, it creates a new preprocessing statement for the execution of each statement and submits SQL in a single line; while the batch mode reuses preprocessed statements and executes all update statements in batches. Obviously, the batch mode has its own problems. For example, when an Insert operation, there is no way to obtain the self-incremented id before the transaction is submitted, which does not meet the business requirements in a certain situation.
The specific usage is as follows:
*Method one spring+mybatis
//Get sqlsession//Inject the original sqlSessionTemplate from spring@Autowiredprivate SqlSessionTemplate sqlSessionTemplate;// Get a new session with BATCH, automatically submitting it to false// If the automatic submission is set to true, the number of submissions cannot be controlled and the last unified submission is changed, which may cause memory overflow SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);//Get mapperfooMapper = 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 submit every 1000, session.commit() cannot be rolled back after commit;//Clean the cache to prevent overflow session.clearCache();}}} catch (Exception e) {//No submitted data can be rolled back session.rollback();} Finally{session.close();}spring+mybatisMethod 2:
Combined with the general mapper sql alias, it is best to have package name + class name
public void insertBatch(Map<String,Object> paramMap, List<User> list) throws Exception {// Get a new session with BATCH, automatically submitting it to false// If the automatic submission is set to true, the number of submissions cannot be controlled and the last unified submission is changed 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, session.commit() cannot be rolled back after commit;// Clean the cache to prevent overflow session.clearCache();}}}} catch (Exception e) {// No submitted data can be rolled back session.rollback();e.printStackTrace();} finally {session.close();}}The above are two ways of batch insertion in mybatis introduced to you by the editor (efficient insertion). I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!