JDBC批量插入数据效率分析
对于需要批量插入数据库操作JDBC有多重方式，本利从三个角度对Statement和PreparedStatement两种执行方式进行分析，总结较优的方案。
当前实现由如下条件：
执行数据库：Mysql
执行数据数量：10万条
执行前提：执行差入数据库钱均需要提供空表，防止数据量大造成的影响
执行方式：Statement和PreparedStatement两种方式
执行步骤开始：
1、创建表
1 CREATE TABLE T_PRODUCT (
2 ID bigint(12) NOT NULL AUTO_INCREMENT COMMENT '主键',
3 NAME varchar(60) NOT NULL COMMENT '产品名称',
4 WEIGHT varchar(60) NOT NULL COMMENT '产品重量',
5 MARK varchar(60) NOT NULL COMMENT '产品说明',
6 PRIMARY KEY (ID)
7 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='产品表';
2、编写操作数据库工具类
1 package com.luwei.test.jdbc;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.util.ResourceBundle;
8
9 /**
10 * <Description> TODO<br>
11 *
12 * @author lu.wei<br>
13 * @email [email protected] <br>
14 * @date 2017年1月9日 <br>
15 * @since V1.0<br>
16 * @see com.luwei.test.jdbc <br>
17 */
18 public class JdbcTemplate {
19 private static String DRIVER_CLASS_NAME = null;
20 private static String URL = null;
21 private static String USERNAME = null;
22 private static String PASSWORD = null;
23
24 static {
25 ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
26 DRIVER_CLASS_NAME = bundle.getString("jdbc.driverClassName");
27 URL = bundle.getString("jdbc.url");
28 USERNAME = bundle.getString("jdbc.username");
29 PASSWORD = bundle.getString("jdbc.password");
30 }
31
32 /**
33 *
34 * <Description> 获取数据库连接<br>
35 *
36 * @author lu.wei<br>
37 * @email [email protected] <br>
38 * @date 2017年1月9日 下午10:19:41 <br>
39 * @return
40 * @throws Exception
41 * <br>
42 */
43 public static Connection getConnection() throws Exception {
44 Class.forName(DRIVER_CLASS_NAME);
45 Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
46 return connection;
47 }
48
49 /**
50 *
51 * <Description> 提交事务<br>
52 *
53 * @author lu.wei<br>
54 * @email [email protected] <br>
55 * @date 2017年1月9日 下午10:20:48 <br>
56 * @param connection
57 * <br>
58 */
59 public static void commit(Connection connection) {
60 try {
61 connection.commit();
62 }
63 catch (SQLException e) {
64 e.printStackTrace();
65 }
66 }
67
68 /**
69 *
70 * <Description> 开启事务<br>
71 *
72 * @author lu.wei<br>
73 * @email [email protected] <br>
74 * @date 2017年1月9日 下午10:23:56 <br>
75 * @param connection
76 * <br>
77 */
78 public static void beginTx(Connection connection) {
79 try {
80 connection.setAutoCommit(false);
81 }
82 catch (SQLException e) {
83 e.printStackTrace();
84 }
85 }
86
87 /**
88 *
89 * <Description> 回滚<br>
90 *
91 * @author lu.wei<br>
92 * @email [email protected] <br>
93 * @date 2017年1月9日 下午10:24:33 <br>
94 * @param connection
95 * <br>
96 */
97 public static void rollback(Connection connection) {
98 try {
99 connection.rollback();
100 }
101 catch (SQLException e) {
102 e.printStackTrace();
103 }
104 }
105
106 /**
107 *
108 * <Description> TODO<br>
109 *
110 * @author lu.wei<br>
111 * @email [email protected] <br>
112 * @date 2017年1月9日 下午10:28:49 <br>
113 * @param statement
114 * @param connection
115 * <br>
116 */
117 public static void releaseDb(Statement statement, Connection connection) {
118 try {
119 statement.close();
120 connection.close();
121 }
122 catch (SQLException e) {
123 e.printStackTrace();
124 }
125 }
126 }
3、执行数据库插入操作
3.1、使用Statement直接插入，三次执行耗时：41979 42608 42490
1 @Test
2 public void testStatement() {
3 Connection connection = null;
4 Statement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8
9 statement = connection.createStatement();
10 long begin = System.currentTimeMillis();
11 for (int i = 0; i < 100000; i++) {
12 String sql = "insert into t_product values(null,'name_" + i + "','120kg','mark_" + i + "')";
13 statement.execute(sql);
14 }
15 long end = System.currentTimeMillis();
16 System.out.println(end - begin);
17 JdbcTemplate.commit(connection);
18 }
19 catch (Exception e) {
20 e.printStackTrace();
21 JdbcTemplate.rollback(connection);
22 }
23 finally {
24 JdbcTemplate.releaseDb(statement, connection);
25 }
26 }
3.2、使用PreparedStatement直接插入，三次执行耗时：22808 24675 22281
1 @Test
2 public void testPreparedStatement() {
3 Connection connection = null;
4 PreparedStatement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8 String sql = "insert into t_product values(null,?,?,?)";
9
10 statement = connection.prepareStatement(sql);
11 long begin = System.currentTimeMillis();
12 for (int i = 0; i < 100000; i++) {
13 statement.setString(1, "name_" + i);
14 statement.setString(2, "120kg");
15 statement.setString(3, "mark_" + i);
16 statement.executeUpdate();
17 }
18 long end = System.currentTimeMillis();
19 System.out.println(end - begin);
20 JdbcTemplate.commit(connection);
21 }
22 catch (Exception e) {
23 e.printStackTrace();
24 JdbcTemplate.rollback(connection);
25 }
26 finally {
27 JdbcTemplate.releaseDb(statement, connection);
28 }
29 }
3.3、使用BatchStatement直接插入，三次执行耗时：15342 15235 15485
1 @Test
2 public void testBatchStatement() {
3 Connection connection = null;
4 Statement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8
9 statement = connection.createStatement();
10 long begin = System.currentTimeMillis();
11 for (int i = 0; i < 100000; i++) {
12 String sql = "insert into t_product values(null,'name_" + i + "','120kg','mark_" + i + "')";
13 statement.addBatch(sql);
14
15 if ((i + 1) % 100 == 0) {
16 statement.executeBatch();
17 statement.clearBatch();
18 }
19 }
20 statement.executeBatch();
21 statement.clearBatch();
22 long end = System.currentTimeMillis();
23 System.out.println(end - begin);
24 JdbcTemplate.commit(connection);
25 }
26 catch (Exception e) {
27 e.printStackTrace();
28 JdbcTemplate.rollback(connection);
29 }
30 finally {
31 JdbcTemplate.releaseDb(statement, connection);
32 }
33 }
3.4、使用BatchPreparedStatement直接插入，三次执行耗时：21913 22045 23291
1 @Test
2 public void testBatchPreparedStatement() {
3 Connection connection = null;
4 PreparedStatement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8 String sql = "insert into t_product values(null,?,?,?)";
9
10 statement = connection.prepareStatement(sql);
11 long begin = System.currentTimeMillis();
12 for (int i = 0; i < 100000; i++) {
13 statement.setString(1, "name_" + i);
14 statement.setString(2, "120kg");
15 statement.setString(3, "mark_" + i);
16 statement.addBatch();
17 if ((i + 1) % 100 == 0) {
18 statement.executeBatch();
19 statement.clearBatch();
20 }
21 }
22 statement.executeBatch();
23 statement.clearBatch();
24 long end = System.currentTimeMillis();
25 System.out.println(end - begin);
26 JdbcTemplate.commit(connection);
27 }
28 catch (Exception e) {
29 e.printStackTrace();
30 JdbcTemplate.rollback(connection);
31 }
32 finally {
33 JdbcTemplate.releaseDb(statement, connection);
34 }
35 }
3.5、使用采用多Value值Statement直接插入，三次执行耗时：2931 3007 3203 2964
1 @Test
2 public void testMutilValueStatement() {
3 Connection connection = null;
4 Statement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8
9 statement = connection.createStatement();
10
11 StringBuffer sql = new StringBuffer("insert into t_product values");
12 long begin = System.currentTimeMillis();
13 for (int i = 0; i < 100000; i++) {
14 if (i != 0) {
15 sql.append(",");
16 }
17 sql.append("(null,'name_" + i + "','120kg','mark_" + i + "')");
18 }
19 statement.execute(sql.toString());
20 long end = System.currentTimeMillis();
21 System.out.println(end - begin);
22 JdbcTemplate.commit(connection);
23 }
24 catch (Exception e) {
25 e.printStackTrace();
26 JdbcTemplate.rollback(connection);
27 }
28 finally {
29 JdbcTemplate.releaseDb(statement, connection);
30 }
31 }
3.6、使用采用多Value值PreparedStatement直接插入，三次执行耗时：3356 3218 3233
1 @Test
2 public void testMutilValuePreparedStatement() {
3 Connection connection = null;
4 PreparedStatement statement = null;
5 try {
6 connection = JdbcTemplate.getConnection();
7 JdbcTemplate.beginTx(connection);
8
9 StringBuffer sql = new StringBuffer("insert into t_product values");
10 long begin = System.currentTimeMillis();
11 for (int i = 0; i < 100000; i++) {
12 if (i != 0) {
13 sql.append(",");
14 }
15 sql.append("(null,'name_" + i + "','120kg','mark_" + i + "')");
16 }
17 statement = connection.prepareStatement(sql.toString());
18 statement.executeUpdate();
19 long end = System.currentTimeMillis();
20 System.out.println(end - begin);
21 JdbcTemplate.commit(connection);
22 }
23 catch (Exception e) {
24 e.printStackTrace();
25 JdbcTemplate.rollback(connection);
26 }
27 finally {
28 JdbcTemplate.releaseDb(statement, connection);
29 }
30 }
通过以上时间结果得出如下数据表格：
总结：通过如上的数据对比发现
1、PreparedStatement执行数据库插入比使用Statement执行数据库插入明显有性能优势，原因归功于PreparedStatement能够预先对SQL进行编译，做到执行时进行SQL共享
2、执行数据库批量操作是使用Batch方式对数据库采用批次操作能够明显提升数据库操作性能能
3、不管是直接多次插入数据库还是采用Batch方式执行数据库的插入，均会发送多次SQL脚本去执行，这样明显没有发送一次SQL脚本执行来的效率高
4、采用单SQL执行数据库批量操作时Statement对比PreparedStatement有微弱的优势，可能是Statement不需要判断注参的原因吧