记录一次批量操作（mybtatis+mysql）

最新推荐文章于 2024-09-20 21:48:23 发布

我有一头小毛驴我从来都不骑

最新推荐文章于 2024-09-20 21:48:23 发布

阅读量339

点赞数

分类专栏： mybatis

本文链接：https://blog.youkuaiyun.com/qq_33854869/article/details/79379505

版权

mybatis 专栏收录该内容

3 篇文章

订阅专栏

在进行mybatis批量操作时笔记。

环境搭建（数据库更换mysql方便更改搜索引擎，h2默认的搜索（好像是MyISAM）对事物不友好）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!--可在SqlSessionFactory中配置 -->
	<typeAliases>
		<package name="mybatis.model" />
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=false" />
				<property name="username" value="root" />
				<property name="password" value="1234" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- 在application中统一注解 -->
		<mapper resource="mapper/CityMapper.xml" />
		<mapper resource="mapper/HotelMapper.xml" />
	</mappers>
</configuration>

使用mybatis批量插入时，有两个思路，一种是使用ExecutorType.BATCH，一种使用insert语句传递list。

ExecutorType.BATCH

ExecutorType有三种类型：

SIMPLE 独立预处理（preparing 语句），每次单独提交
REUSE 重用preparing语句，每次单独提交
BATCH 重用preparing语句，批量提交

测试代码

package mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mybatis.model.City;

public class T {

	public static void main(String[] args) {
		SqlSession session = null;
		try {
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(Resources.getResourceAsReader("mybatis-config.xml"));
			session = sqlSessionFactory.openSession(ExecutorType.BATCH);
			session.insert("insert", new City("a", "", ""));
			session.insert("insert", new City("b", "", ""));
			session.commit();
		} catch (Exception e) {
			session.rollback();
		} finally {
			session.close();
		}
	}

}

autoCommit默认为false（true对batch不生效），即两次insert都在一个事物以内。

simple

mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: a(String), (String), (String)
mybatis.mapper.CityMapper.insert - <==    Updates: 1
mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: b(String), (String), (String)
mybatis.mapper.CityMapper.insert - <==    Updates: 1

reuse

mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: a(String), (String), (String)
mybatis.mapper.CityMapper.insert - <==    Updates: 1
mybatis.mapper.CityMapper.insert - ==> Parameters: b(String), (String), (String)
mybatis.mapper.CityMapper.insert - <==    Updates: 1

batch

mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: a(String), (String), (String)
mybatis.mapper.CityMapper.insert - ==> Parameters: b(String), (String), (String)

插入异常

package mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mybatis.model.City;

public class T {

	public static void main(String[] args) {
		SqlSession session = null;
		try {
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(Resources.getResourceAsReader("mybatis-config.xml"));
			session = sqlSessionFactory.openSession(ExecutorType.BATCH);
			session.insert("insert", new City("a", "", ""));
			//会报错，ExecutorType=simple or reuse不会进行commit，因此不会入库
			session.insert("insert", new City(null, "", ""));
			session.commit();
		} catch (Exception e) {
			//此处不需要进行rollback，simple或reuse没有进行commit，batch批量操作有错误也不会入库
			//session.rollback();
		} finally {
			session.close();
		}
	}

}

一旦进行了commit，rollback将不起作用。当出错时，自动回滚。

mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: a(String), (String), (String)
mybatis.mapper.CityMapper.insert - <==    Updates: 1
mybatis.mapper.CityMapper.insert - ==>  Preparing: insert into city (name,state,country) values (?,?,?) 
mybatis.mapper.CityMapper.insert - ==> Parameters: null, (String), (String)
org.apache.ibatis.transaction.jdbc.JdbcTransaction - Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@55e26b7f]
org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@55e26b7f]
org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@55e26b7f]
org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1440902015 to pool.

其实此处的rollback没有什么用，rollback要保证在commit之前使用，对于autoCommit=true无作用，autoCommit=false，出错了，没有进行commit，不需要进行rollback的。特殊需求，也插入时，发现错误，需要回滚，此时才使用rollback。列举一处rollback的test。

package mybatis;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mybatis.model.City;

public class T {

	public static void main(String[] args) {
		SqlSession session = null;
		try {
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(Resources.getResourceAsReader("mybatis-config.xml"));
			session = sqlSessionFactory.openSession(ExecutorType.BATCH);
			List<City> list = new ArrayList<>();
			for (int i = 0; i < 10; i++) {
				list.add(new City(i + "", "", ""));
				// 添加一个错误
				if (i == 5) {
					list.add(new City(null, "", ""));
				}
			}
			// 批量添加，一次添加2个
			int i = 0;
			while (i < list.size()) {
				int end = Math.min(i + 2, list.size());
				List<City> subList=null;
				try {
					subList = list.subList(i, end);
					for (City city : subList) {
						session.insert("insert", city);
					}
					session.commit();
				} catch (Exception e) {
					for (City city : subList) {
						System.out.println("uninsert : "+city.getName());
					}
					session.rollback();
				}
				i = end;
			}
		} catch (Exception e) {
			
		} finally {
			session.close();
		}
	}

}

name=6与name=null作为同一批数据进行commit，当使用session.rollback，name=null因为语句错误没有commit成功，name=6也被回滚。当不使用session.rollback，name=6没有被回滚，commit成功。

commit / rollback这两个命令用的时候要小心。 commit / rollback 都是用在执行 DML语句（INSERT / DELETE / UPDATE / SELECT ）之后的。DML 语句，执行完之后，处理的数据，都会放在回滚段中（除了 SELECT 语句），等待用户进行提交（commit）或者回滚（rollback），当用户执行 commit / rollback后，放在回滚段中的数据就会被删除。

list参数进行批量提交

    <insert id="batchinsert" parameterType="java.util.List">  
    <selectKey resultType ="java.lang.Long" keyProperty= "id" order= "AFTER">  
        SELECT LAST_INSERT_ID()  
    </selectKey >  
    insert into city (name,state,country)  
    VALUES  
    <foreach collection="list" item="c" index="index" separator=",">  
       (#{c.name},#{c.state},#{c.country})  
    </foreach>  
    </insert>

package mybatis;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mybatis.model.City;

public class T {

	public static void main(String[] args) {
		SqlSession session = null;
		try {
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(Resources.getResourceAsReader("mybatis-config.xml"));
			session = sqlSessionFactory.openSession(ExecutorType.SIMPLE,true);
			List<City> list = new ArrayList<>();
			list.add(new City("a", "", ""));
			list.add(new City("b", "", ""));
			session.insert("batchinsert",list);
		} catch (Exception e) {
		} finally {
			session.close();
		}
	}

}