第一周:JDBC中批量插入数据问题
在向数据库中添加数据时,难免会遇到批量添加数据的问题。下面就是使用JDBC来实现批量插入的几种方法。
准备工作:
- 在MySQL5数据库中创建一个
names
表 - 表中就两个字段
id
:主键,自增name
:varchar(25),保证长度够用就行
CREATE TABLE names(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25)
);
方法一:
最直接的频繁执行SQL语句来插入
long start = System.currentTimeMillis();
// 获取数据库连接
Connection conn= DriverManager.getConnection(url, user, password);
// SQL语句
String sql = "insert into names(name) values(?);";
// 预编译SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
// 批量插入 2万 条数据
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name_"+i); // 填充占位符?
ps.execute(); // 每一条数据都执行一次
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start)); // 花费的时间为:794551
// 关闭资源
ps.close();
conn.close();
方式二:
使用executeBatch()
来批量插入数据
需要在数据库连接的url中添加rewriteBatchedStatements=true
字段,让数据库开启批处理默认
long start = System.currentTimeMillis();
// 获取数据库连接
Connection conn= DriverManager.getConnection(url, user, password);
// SQL语句
String sql = "insert into names(name) values(?)"; // 注意这里! 一定不要加结尾的分号;
// 预编译SQL语句
PreparedStatement ps = conn.prepareStatement(sql);
// 批量插入 100万 条数据
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_"+i);
// 添加到同一batch中
ps.addBatch();
if (i % 500 == 0) { // 每批次够500条才执行 控制这个数也可以提高点速度
// 执行该batch的插入操作
ps.executeBatch();
// 清空已执行的batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start)); // 花费的时间为:5177
// 关闭资源
ps.close();
conn.close();
注意:一定不要给SQL语句添加结尾的
;
。否则会抛异常。
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('name_2'),('name_3'),('name_4'),('name_5'),('name_6'),('name_7'),('name_8'),('n' at line 1...
至于原因,JDBC的源码实在太晦涩了,看一会就前后连接不上了,所以笔者认为应该是由于在MySQL中批量插入的SQL语句的问题。
就是对于names
表,直接在MySQL中用SQL语句来批量添加数据时,可以这样
insert into names(`name`) values("name_1"), ("name_2"), ("name_3");
这行去掉;
也能正常运行
但是如果这样,注意分号
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")
那么"name_1"插入表中,后面2和3没有,并且MySQL抛异常。
那么或许在JDBC中,每次addBatch()
,都是将要放在占位符?
的数据先存在ArrayList
中,当执行executeBatch()
时,遍历ArrayList
将第一个数据"name_1"
放在SQL语句的?
处,后续的全部构造成,("name_2")
、,("name_3")
的形式连接在这条SQL语句后面,最终构造成一个长的插入SQL语句,再执行,完成批量插入。
即:
insert into names(`name`) values("name_1")
insert into names(`name`) values("name_1"), ("name_2")
insert into names(`name`) values("name_1"), ("name_2"), ("name_3")
insert into names(`name`) values("name_1"), ("name_2"), ("name_3")..., ("name_batchSize")
这样由于执行拼在一起的SQL就可以完成批量插入。
但是如果insert into names(name) values(?);
结尾有个;
,就变成这样:
insert into names(`name`) values("name_1");
insert into names(`name`) values("name_1");, ("name_2")
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")..., ("name_batchSize")
那么JDBC的对SQL语句的语法检查或语义检查无法通过,就会抛异常。
数据库中也不会有"name_1"这条数据。
以上是笔者的推测,并没有通过JDBC源码验证。
方式三:
在方式二的基础上再进一步优化,除了改变一批次的容量(上面是500)外,还可以设置不允许自动提交数据,改为手动提交数据。
// 设置不允许自动提交数据
conn.setAutoCommit(false); // 该行代码放在获取数据库连接后
// ... 批量插入操作同上
// 提交数据
conn.commit(); // 在批量插入的for循环后
// 花费时间为:3954
另外,还有个executeLargeBatch()
方法
当要总共要插入1亿条数据,并且一个batch为100万
executeBatch()
花费了413635毫秒
executeLargeBatch()
花费了386389毫秒
emmm...可能不是单纯替换着用的,哈哈哈!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析