前言
在上一篇中提到了这么一个问题:
for(Integer id : ids) {
userDao.deleteUserById(id);
}
但我们需要批量删除的时候,这样会不停的获取数据库连接,资源消耗是很大的。
接下来就利用Mybatis的动态SQL功能,去验证一下这个问题(传送门:动态SQL)。
首先搭建Mybatis环境和日志环境搭建mybatis环境和日志环境(参考第一篇和第二篇),并创建如下一张表tb_user(id、姓名)和相应的持久层对象:
批量新增
A.利用想当然的方式测试
mapper文件和dao层接口
<insert id="saveUser" parameterType="com.zepal.mybatis.domain.User" useGeneratedKeys="true">
INSERT INTO tb_user (user_name, user_age) VALUEs (#{userName}, #{userAge});
</insert>
int saveUser(User user);
测试程序和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
for(int i=0;i<10000;i++) {
User user = new User();
user.setUserName("name"+i);
user.setUserAge(i);
userDao.saveUser(user);
System.out.println("===循环执行中===");
}
sqlSession.commit();
sqlSession.close();
System.out.println("耗时 : " + (System.currentTimeMillis()-start_time));
}
}
耗时 : 9414
增加1000条数据用了近10S钟。
B.利用动态SQL批量执行
mapper文件和dao层接口
<insert id="saveUserWithBatch" parameterType="list" useGeneratedKeys="true">
INSERT INTO tb_user (user_name, user_age) VALUES
<foreach collection="list" item="item" separator="," close=";">
(#{item.userName},#{item.userAge})
</foreach>
</insert>
int saveUserWithBatch(List<User> users);
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
List<User> users = new LinkedList<>();
for(int i=0;i<10000;i++) {
User user = new User();
user.setUserName("name"+i);
user.setUserAge(i);
users.add(user);
}
int result = userDao.saveUserWithBatch(users);
System.out.println("受影响的行 : " + result);
System.out.println("耗时:" + (System.currentTimeMillis()-start_time));
sqlSession.commit();
sqlSession.close();
}
}
受影响的行 : 10000
耗时:1882
可以明显的看到,用动态SQL构建批量操作,快了5倍以上。
核心就是构建如下SQL语句:
INSERT INTO tb_user (user_name, user_age) VALUES
(?,?),(?,?),(?,?),(?,?),(?,?),(?,?)....;
这里有个主意的地方:就是mysql允许的最大通讯数据
用sql语句查看:
show VARIABLES LIKE 'max_allowed_packet%';
如果想查看mysql所有配置属性,去掉like条件即可。
得到的结果是以B为单位的数值就是mysql最大允许的传输数据。如果往mysql发送的数据过大,会报错,可以通过修改my.ini再重启Mysql服务。
max_allowed_packet=20M
或者直接执行
set global max_allowed_packet = 2*1024*1024*10
上面修改为了20M,然后再执行show VARIABLES LIKE 'max_allowed_packet%'查看是否修改成功。
批量删除
利用上面的结果
1、利用错误的方式测试
mapper文件和dao层接口
<delete id="deleteUser" parameterType="int">
delete from tb_user where user_id = #{userId};
</delete>
int deleteUser(@Param("userId")Integer userId);
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
for(int i=1;i<=10000;i++) {
userDao.deleteUser(i);
}
System.out.println("耗时 : " + (System.currentTimeMillis() - start_time));
sqlSession.commit();
sqlSession.close();
}
}
耗时 : 7911
可以看到删除1000条数据也差不多用了8s钟。
B.利用动态SQL构建批量删除
mapper文件和dao层接口
<delete id="deleteUserWithBatch" parameterType="list">
delete from tb_user where user_id in
<foreach collection="list" item="item" open="(" separator="," close=");">
#{item}
</foreach>
</delete>
int deleteUserWithBatch(List<Integer> userIds);
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
List<Integer> ids = new LinkedList<>();
for(int i=1;i<=10000;i++) {
ids.add(i);
}
int result = userDao.deleteUserWithBatch(ids);
System.out.println("受影响的行 : " + result);
System.out.println("耗时 : " + (System.currentTimeMillis()-start_time));
sqlSession.commit();
sqlSession.close();
}
}
受影响的行 : 10000
耗时 : 1043
很明显,快了近8倍。
核心就是构建如下SQL语句:
DELETE FROM tb_user WHERE user_id in (?,?,?,?.....);
批量修改
A、利用错误的方式测试
mapper文件和dao层接口
<update id="updateUser" parameterType="com.zepal.mybatis.domain.User">
UPDATE tb_user SET user_name = #{userName},
user_age = #{userAge}
WHERE user_id = #{userId};
</update>
int updateUser(User user);
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
for(int i=1;i<=10000;i++) {
User user = new User();
user.setUserName("name"+i);
user.setUserAge(i);
user.setUserId(i);
userDao.updateUser(user);
}
System.out.println("耗时 :" + (System.currentTimeMillis()-start_time));
sqlSession.commit();
sqlSession.close();
}
}
耗时 :8295
可以看到循环修改了10000条数据用了8s钟
B.利用动态SQL执行批量修改
mapper文件和dao层接口
<update id="updateUserWithBatch" parameterType="list">
UPDATE tb_user
SET user_name =
CASE
<foreach collection="list" item="item">
WHEN user_id = #{item.userId} THEN #{item.userName}
</foreach>
END
WHERE user_id in
<foreach collection="list" item="item" open="(" separator="," close=");">
#{item.userId}
</foreach>
</update>
int updateUserWithBatch(List<User> users);
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
List<User> users = new LinkedList<>();
for(int i=1;i<=10000;i++) {
User user = new User();
user.setUserName("name"+i);
user.setUserId(i);
users.add(user);
}
int result = userDao.updateUserWithBatch(users);
System.out.println("受影响的行 :" + result);
System.out.println("耗时 :" + (System.currentTimeMillis()-start_time));
sqlSession.commit();
sqlSession.close();
}
}
受影响的行 :10000
耗时 :1761
核心就是要构造如下的SQL语句
UPDATE tb_user
SET user_name=
CASE
WHEN user_id=? THEN ?(这里是要修改的值)
WHEN user_id=? THEN ?
......
END
WHERE user_id in (?,?...);
快是快了不少,但是这种方式有短板,就是修改多个字段的时候,mapper文件中会充斥着大量的<foreach>标签,反而会更慢,因为在遍历一遍后又遍历了一次。
尝试一下Mybatis自身的批处理
在mybatis配置文件中开启批处理(settings标签下)
<setting name="defaultExecutorType" value="BATCH"/>
获取SqlSession对象的时候打开批处理,如果整合Spring,则在配置IOC的时候加入ExecutorType.BATCH
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
mapper文件和dao层接口沿用了上面批量修改中的第一种方法
完整测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//注意这里--
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
UserDao userDao = sqlSession.getMapper(UserDao.class);
long start_time = System.currentTimeMillis();
for(int i=1;i<=10000;i++) {
User user = new User();
user.setUserId(i);
user.setUserName("name"+i);
user.setUserAge(i);
userDao.updateUser(user);
}
System.out.println(System.currentTimeMillis()-start_time);
sqlSession.commit();
sqlSession.close();
}
}
DEBUG [main] - ==> Parameters: name10000(String), 10000(Integer), 10000(Integer)
耗时 : 1206
根据结果看,好像比动态SQL还快了,所以,在修改数据量大或者多字段修改的时候,用Mybatis提供的批量处理比较好,新增和删除,经过测试动态SQL要快(自己动手哦)。
但是这中间肯定是有个临界值的,因为发送数据到数据库也是要耗时的,数据量大肯定慢,况且,在mapper文件中,同样存在遍历操作。
所以,具体还得看自身的需求及对事务的要求。然后综合测试对比一下,再决定采用的方式。这里有个注意的地方就是,开启了Mybatis的批处理后,不能正确的返回受影响的行,如果执行成功,永远都是返回-2147482646,官方文档也给出了说明:“If the BATCH executor is in use, the update counts are being lost. ”。那么要正确捕获受影响的行数,只能自己设计计数器去实现了。
总之,批量处理的核心就是,所有的操作要保持在一个数据库连接之下,不要不停地获取数据库连接,再释放连接(Mybatis自身的批处理也是保持在当前连接下进行的,要区别对待循环调用)。
此篇完结