以前总是说批量插入和更新的效率比非批量的要高,但是到底高多少,没有评估过,今天我就具体的测试下
(1)三种插入操作的方法
1.1 利用for循环的批量插入
示例xml
-
<insert id="insertUser">
-
insert into test_user (u_name,create_date) value (#{userName},SYSDATE())
-
</insert>
- 1
- 2
- 3
示例代码:
-
for (int i = 1; i <= num; i++) {
-
User user = new User();
-
user.setUserName("a" + i);
-
user.setCreateDate(new Date());
-
userDao.insertUser(user);
-
}
- 1
- 2
- 3
- 4
- 5
- 6
2. 采用jdbc
示例代码:
-
Connection conn;
-
try {
-
Class.forName("com.mysql.jdbc.Driver");
-
conn = DriverManager.getConnection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
-
conn.setAutoCommit(false);
-
String sql = "insert into test_user (u_name,create_date) value (?,SYSDATE())";
-
PreparedStatement prest = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
-
ResultSet.CONCUR_READ_ONLY);
-
conn.setAutoCommit(false);
-
for (int i = 1; i <= 100; i++) {
-
prest.setString(1, "a" + i);
-
prest.addBatch();
-
}
-
prest.executeBatch();
-
conn.commit();
-
conn.close();
-
} catch (Exception ex) {
-
ex.printStackTrace();
-
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
数据量分别是10,100,300,1000,5000条数据
数据量:10
批量更新耗时:279
非批量更新耗时:1522
jdbc批量更新耗时:255
数据量:100
批量更新耗时:720
非批量更新耗时:3391
jdbc批量更新耗时:1912
数据量:300
批量更新耗时:987
非批量更新耗时:9827
jdbc批量更新耗时:7616
数据量:500
批量更新耗时:1649
非批量更新耗时:16253
jdbc批量更新耗时:10475
数据量:1000
批量更新耗时:2552
非批量更新耗时:33048
jdbc批量更新耗时:20793
数据量:5000
批量更新耗时:19066
非批量更新耗时:239127
jdbc批量更新耗时:103273
综上分析,效率排比如下
mybatis批量更新 > jdbc批量更新 > 循环调用update语句
(2)三种批量更新的方法
2.1 利用for循环批量更新
示例xml
-
<update id="updateUser">
-
update test_user set test_user.u_name = (#{updateUserName}) where test_user.u_name = (#{userName})
-
</update>
- 1
- 2
- 3
- 1
- 2
- 3
示例代码
-
for (int i = 1; i <= num; i++) {
-
User user = new User();
-
user.setUserName("a" + i);
-
user.setUpdateUserName("b" + i);
-
userDao.updateUser(user);
-
}
- 1
- 2
- 3
- 4
- 5
- 6
2.2 jdbc 批量更新
示例代码
-
Connection conn;
-
try {
-
Class.forName("com.mysql.jdbc.Driver");
-
conn = DriverManager.getConnection("jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
-
conn.setAutoCommit(false);
-
// 保存当前自动提交模式
-
boolean autoCommit = conn.getAutoCommit();
-
// 关闭自动提交
-
conn.setAutoCommit(false);
-
Statement stmt =conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
-
for (int i = 1; i <= num; i++) {
-
stmt.addBatch("update test_user set test_user.u_name = ('d"+i+"') where test_user.u_name = ('c"+i+"')");
-
}
-
stmt.executeBatch();
-
conn.commit();
-
conn.close();
-
} catch (Exception ex) {
-
ex.printStackTrace();
-
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
2.3 mybatis 批量更新
其实是利用了mysql的批量更新的语法
case when的语法
详见 【case when 语法】
示例xml
-
<update id="batchUpdateList">
-
update test_user
-
<trim prefix="set" suffixOverrides=",">
-
<trim prefix="u_name =case" suffix="end,">
-
<foreach item="item" collection="userList">
-
when test_user.u_name = (#{item.userName})
-
then #{item.updateUserName}
-
</foreach>
-
</trim>
-
</trim>
-
where
-
<foreach item="item" collection="userList" separator="or">
-
(test_user.u_name = (#{item.userName}))
-
</foreach>
-
</update>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
示例代码
-
for (int i = 1; i <= num; i++) {
-
User user = new User();
-
user.setUserName("a" + i);
-
user.setUpdateUserName("b" + i);
-
userList.add(user);
-
}
-
userDao.batchUpdateList(userList);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
数据量分别是10,100,300,1000,5000条数据
数据量:10
批量更新耗时:279
非批量更新耗时:1522
jdbc批量更新耗时:255
数据量:100
批量更新耗时:720
非批量更新耗时:3391
jdbc批量更新耗时:1912
数据量:300
批量更新耗时:987
非批量更新耗时:9827
jdbc批量更新耗时:7616
数据量:500
批量更新耗时:1649
非批量更新耗时:16253
jdbc批量更新耗时:10475
数据量:1000
批量更新耗时:2552
非批量更新耗时:33048
jdbc批量更新耗时:20793
数据量:5000
批量更新耗时:19066
非批量更新耗时:239127
jdbc批量更新耗时:103273
综上分析,效率排比如下
mybatis批量更新 > jdbc批量更新 > 循环调用update语句