Java中使用Jdbc如何批量插入数据最高效
【摘要】 在Java中,当需要成批插入或者更新记录时,可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更高效。
1、批量执行SQL语句
在Java中,当需要成批插入或者更新记录时,可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更高效。
JDBC的批量处理语句包括下面三个方法:
- addBatch(String):添加需要批量处理的SQL语句或是参数;
- executeBatch():执行批量处理语句;
- clearBatch():清空缓存的数据。
通常我们会遇到两种批量执行SQL语句的情况:
- 多条SQL语句的批量处理;
- 一个SQL语句的批量传参;
2、数据库表结构准备
数据库中提供一个users表,创建语句如下:
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
3、数据的批量插入
以下测试均以向数据表中插入20000条数据为例,对程序执行时间作比较。
3.1、实现层次一:使用Statement
测试代码如下:
@Test
public void testBatchInsert1() {
long start = System.currentTimeMillis();
Connection conn = null;
Statement st = null;
try {
// 1.获取数据库连接
conn = JdbcUtils.getConnection();
// 2.创建Statement实例
st = conn.createStatement();
// 3.插入数据
for (int i = 1; i <= 20000; i++) {
String sql = "insert into users(name) values('name_ + " + i + "')";
st.executeUpdate(sql);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭资源
JdbcUtils.closeResource(conn, st);
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
}
测试结果如下,合计用时17.554秒:
3.2、实现层次二:使用PreparedStatement
测试代码如下:
@Test
public void testBatchInsert2() {
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.获取数据库连接
conn = JdbcUtils.getConnection();
// 2.创建PreparedStatement实例
String sql = "insert into users(name) values(?)";
ps = conn.prepareStatement(sql);
// 3.插入数据
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "name_" + i);
ps.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭资源
JdbcUtils.closeResource(conn, ps);
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
}
测试结果如下,合计用时17.762秒:
3.3、实现层次三:在层次二的基础上做优化
修改配置文件,url后面添加?rewriteBatchedStatements=true
,mysql服务器默认是关闭批处理的,我们需要通过该参数,让mysql开启批处理的支持:
user=root
password=root
url=jdbc:mysql://172.16.2.105:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver
测试代码如下:
/**
* 使用 addBatch() / executeBatch() / clearBatch()
*/
@Test
public void testBatchInsert3() {
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.获取数据库连接
conn = JdbcUtils.getConnection();
// 2.创建PreparedStatement实例
String sql = "insert into users(name) values(?)";
ps = conn.prepareStatement(sql);
// 3.插入数据
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "name_" + i);
//每满500条数据执行一次更新
ps.addBatch();
if (i % 500 == 0) {
// 执行批量更新
ps.executeBatch();
// 清空执行过的sql
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 4.关闭资源
JdbcUtils.closeResource(conn, ps);
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
}
测试结果如下,20000条数据合计用时1.561秒:
1000000条数据合计用时10.761秒:
3.4、实现层次四:在层次三的基础上进一步优化
测试代码如下:
/**
* 层次四:在层次三的基础上操作,使用Connection的setAutoCommit(false)关闭自动提交,进行手动提交
*/
@Test
public void testBatchInsert4() {
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.获取数据库连接
conn = JdbcUtils.getConnection();
// 2.设置为不自动提交数据
conn.setAutoCommit(false);
// 3.创建PreparedStatement实例
String sql = "insert into users(name) values(?)";
ps = conn.prepareStatement(sql);
// 4.插入数据
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "name_" + i);
//每满500条数据执行一次更新
ps.addBatch();
if (i % 500 == 0) {
// 执行批量更新
ps.executeBatch();
// 清空执行过的sql
ps.clearBatch();
}
}
// 5.提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 6.关闭资源
JdbcUtils.closeResource(conn, ps);
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
}
测试结果如下,20000条数据合计用时1.586秒:
1000000条数据合计用时8.701秒:
说明:以上测试结果用时仅供参考,会因电脑配置不同而存在差异。
经过上述实践不难发现,最后的层次四的执行效率最高,也说明在大批量插入数据时使用PreparedStatement比Statement的效率要高很多,但是要记得配置文件中开启数据库对批处理的支持,且不要频繁的执行更新和提交。
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)