这篇文章主要介绍了java中JDBC实现往MySQL插入百万级数据的实例代码，具有一定的参考价值，感兴趣的小伙伴们可以参考一下。
想往某个表中插入几百万条数据做下测试，原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真….
|
1
2
3
4
5
6
7
8
9
10
11
|
DROP
PROCEDURE
IF EXISTS proc_initData;
--如果存在此存储过程则删掉
DELIMITER $
CREATE
PROCEDURE
proc_initData()
BEGIN
DECLARE
i
INT
DEFAULT
1;
WHILE i<=100000 DO
INSERT
INTO
text
VALUES
(i,CONCAT(
'姓名'
,i),
'XXXXXXXXX'
);
SET
i = i+1;
END
WHILE;
END
$
CALL proc_initData();
|
执行CALL proc_initData()后，本来想想，再慢10W条数据顶多30分钟能搞定吧，结果我打了2把LOL后，回头一看，还在执行，此时心里是彻底懵逼的….待我打完第三把结束后，终于执行完了，这种方法若是让我等上几百万条数据，是不是早上去上班，下午下班回来还没结束呢？10W条数据，有图有真相
JDBC往数据库中普通插入方式
后面查了一下，使用JDBC批量操作往数据库插入100W+的数据貌似也挺快的，
先来说说JDBC往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
private
String user =
"root"
;
private
String password =
"123456"
;
@Test
public
void
Test(){
Connection conn =
null
;
PreparedStatement pstm =
null
;
ResultSet rt =
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn = DriverManager.getConnection(url, user, password);
String sql =
"INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)"
;
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
Random rand =
new
Random();
int
a,b,c,d;
for
(
int
i =
1
; i <=
1000
; i++) {
pstm.setInt(
1
, i);
pstm.setInt(
2
, i);
a = rand.nextInt(
10
);
b = rand.nextInt(
10
);
c = rand.nextInt(
10
);
d = rand.nextInt(
10
);
pstm.setString(
3
,
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.setString(
4
,
"xxxxxxxxxx_"
+
"188"
+a+
"88"
+b+c+
"66"
+d);
27
pstm.executeUpdate();
}
Long endTime = System.currentTimeMillis();
System.out.println(
"OK,用时："
+ (endTime - startTime));
}
catch
(Exception e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
finally
{
if
(pstm!=
null
){
try
{
pstm.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
if
(conn!=
null
){
try
{
conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
}
}
|
输出结果：OK,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。
在讨论批量处理之前，先说说遇到的坑，首先，JDBC连接的url中要加rewriteBatchedStatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100W条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来…
那么低版本的驱动包是否对100W+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。
使用事务提交方式
先将命令的提交方式设为false，即手动提交conn.setAutoCommit(false);最后在所有命令执行完之后再提交事务conn.commit();
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
private
String user =
"root"
;
private
String password =
"123456"
;
@Test
public
void
Test(){
Connection conn =
null
;
PreparedStatement pstm =
null
;
ResultSet rt =
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn = DriverManager.getConnection(url, user, password);
String sql =
"INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)"
;
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(
false
);
Long startTime = System.currentTimeMillis();
Random rand =
new
Random();
int
a,b,c,d;
for
(
int
i =
1
; i <=
100000
; i++) {
pstm.setInt(
1
, i);
pstm.setInt(
2
, i);
a = rand.nextInt(
10
);
b = rand.nextInt(
10
);
c = rand.nextInt(
10
);
d = rand.nextInt(
10
);
pstm.setString(
3
,
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.setString(
4
,
"xxxxxxxxxx_"
+
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.executeUpdate();
}
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println(
"OK,用时："
+ (endTime - startTime));
}
catch
(Exception e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
finally
{
if
(pstm!=
null
){
try
{
pstm.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
if
(conn!=
null
){
try
{
conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
}
}
|
以上代码插入10W条数据，输出结果：OK,用时：18086，也就十八秒左右的时间，理论上100W也就是3分钟这样，勉强还可以接受。
批量处理
接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
private
String user =
"root"
;
private
String password =
"123456"
;
@Test
public
void
Test(){
Connection conn =
null
;
PreparedStatement pstm =
null
;
ResultSet rt =
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn = DriverManager.getConnection(url, user, password);
String sql =
"INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)"
;
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
Random rand =
new
Random();
int
a,b,c,d;
for
(
int
i =
1
; i <=
100000
; i++) {
pstm.setInt(
1
, i);
pstm.setInt(
2
, i);
a = rand.nextInt(
10
);
b = rand.nextInt(
10
);
c = rand.nextInt(
10
);
d = rand.nextInt(
10
);
pstm.setString(
3
,
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.setString(
4
,
"xxxxxxxxxx_"
+
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.addBatch();
}
pstm.executeBatch();
Long endTime = System.currentTimeMillis();
System.out.println(
"OK,用时："
+ (endTime - startTime));
}
catch
(Exception e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
finally
{
if
(pstm!=
null
){
try
{
pstm.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
if
(conn!=
null
){
try
{
conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
}
}
|
10W输出结果：OK,用时：3386，才3秒钟.
批量操作+事务
然后我就想，要是批量操作+事务提交呢？会不会有神器的效果？
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
private
String user =
"root"
;
private
String password =
"123456"
;
@Test
public
void
Test(){
Connection conn =
null
;
PreparedStatement pstm =
null
;
ResultSet rt =
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn = DriverManager.getConnection(url, user, password);
String sql =
"INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)"
;
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(
false
);
Long startTime = System.currentTimeMillis();
Random rand =
new
Random();
int
a,b,c,d;
for
(
int
i =
1
; i <=
100000
; i++) {
pstm.setInt(
1
, i);
pstm.setInt(
2
, i);
a = rand.nextInt(
10
);
b = rand.nextInt(
10
);
c = rand.nextInt(
10
);
d = rand.nextInt(
10
);
pstm.setString(
3
,
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.setString(
4
,
"xxxxxxxxxx_"
+
"188"
+a+
"88"
+b+c+
"66"
+d);
pstm.addBatch();
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println(
"OK,用时："
+ (endTime - startTime));
}
catch
(Exception e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
finally
{
if
(pstm!=
null
){
try
{
pstm.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
if
(conn!=
null
){
try
{
conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
throw
new
RuntimeException(e);
}
}
}
}
|
以下是100W数据输出对比：（5.1.17版本MySql驱动包下测试，交替两种方式下的数据测试结果对比）
| 批量操作(10W) | 批量操作+事务提交(10W) | 批量操作(100W) | 批量错作+事务提交(100W) |
OK,用时：3901
OK,用时：3343
OK,用时：44242
OK,用时：39798
OK,用时：4142
OK,用时：2949
OK,用时：44248
OK,用时：39959
OK,用时：3664
OK,用时：2689
OK,用时：44389
OK,用时：39367
可见有一定的效率提升，但是并不是太明显，当然因为数据差不算太大，也有可能存在偶然因数，毕竟每项只测3次。
预编译+批量操作
网上还有人说使用预编译+批量操作的方式能够提高效率更明显，但是本人亲测，效率不高反降，可能跟测试的数据有关吧。
预编译的写法，只需在JDBC的连接url中将写入useServerPrepStmts=true即可，
如：
复制代码 代码如下:
private String url = “jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true”
好了，先到这里…
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
</div>
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
