JDBC批量插入数据效率分析

Posted on 2017-01-10 00:12 路伟阅读(9179) 评论(2) 编辑收藏举报

对于需要批量插入数据库操作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 1025742048@qq.com <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 1025742048@qq.com <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 1025742048@qq.com <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 1025742048@qq.com <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 1025742048@qq.com <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 1025742048@qq.com <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不需要判断注参的原因吧

会员力量，点亮园子希望

刷新页面返回顶部

路伟

公告

JDBC批量插入数据效率分析