JDBC批量插入数据效率分析

来源：互联网发布：锦瑟 priest 知乎编辑：程序博客网时间：2024/05/29 18:34

对于需要批量插入数据库操作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 statement114      * @param connection115      * <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不需要判断注参的原因吧

阅读全文

0 0