JDBC各种插入数据的速度对比

迷南。 2022-06-12 01:54 188阅读 0赞

对于需要批量插入数据库操作JDBC有多重方式，本利从三个角度对Statement和PreparedStatement两种执行方式进行分析，总结较优的方案。

当前实现由如下条件：

执行数据库：Mysql

执行数据数量：10万条

执行前提：执行差入数据库钱均需要提供空表，防止数据量大造成的影响

执行方式：Statement和PreparedStatement两种方式

执行步骤开始：

1、创建表

![复制代码][copycode.gif]

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='产品表';

![复制代码][copycode.gif]

2、编写操作数据库工具类

![复制代码][copycode.gif]

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 
 11 * 
 12 * @author lu.wei 
 13 * @email 1025742048@qq.com 
 14 * @date 2017年1月9日 
 15 * @since V1.0 
 16 * @see com.luwei.test.jdbc 
 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> 获取数据库连接 
 35 * 
 36 * @author lu.wei 
 37 * @email 1025742048@qq.com 
 38 * @date 2017年1月9日 下午10:19:41 
 39 * @return
 40 * @throws Exception
 41 * 
 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> 提交事务 
 52 * 
 53 * @author lu.wei 
 54 * @email 1025742048@qq.com 
 55 * @date 2017年1月9日 下午10:20:48 
 56 * @param connection
 57 * 
 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> 开启事务 
 71 * 
 72 * @author lu.wei 
 73 * @email 1025742048@qq.com 
 74 * @date 2017年1月9日 下午10:23:56 
 75 * @param connection
 76 * 
 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> 回滚 
 90 * 
 91 * @author lu.wei 
 92 * @email 1025742048@qq.com 
 93 * @date 2017年1月9日 下午10:24:33 
 94 * @param connection
 95 * 
 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 
 109 * 
 110 * @author lu.wei 
 111 * @email 1025742048@qq.com 
 112 * @date 2017年1月9日 下午10:28:49 
 113 * @param statement
 114 * @param connection
 115 * 
 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 }

![复制代码][copycode.gif]

3、执行数据库插入操作

3.1、使用Statement直接插入，三次执行耗时：41979 42608 42490

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

3.2、使用PreparedStatement直接插入，三次执行耗时：22808 24675 22281

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

3.3、使用BatchStatement直接插入，三次执行耗时：15342 15235 15485

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

3.4、使用BatchPreparedStatement直接插入，三次执行耗时：21913 22045 23291

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

3.5、使用采用多Value值Statement直接插入，三次执行耗时：2931 3007 3203 2964

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

3.6、使用采用多Value值PreparedStatement直接插入，三次执行耗时：3356 3218 3233

![复制代码][copycode.gif]

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 }

![复制代码][copycode.gif]

通过以上时间结果得出如下数据表格：

![1082159-20170110000211900-384900792.png][]

![1082159-20170110000225666-1051892039.png][]

总结：通过如上的数据对比发现

1、PreparedStatement执行数据库插入比使用Statement执行数据库插入明显有性能优势，原因归功于PreparedStatement能够预先对SQL进行编译，做到执行时进行SQL共享

2、执行数据库批量操作是使用Batch方式对数据库采用批次操作能够明显提升数据库操作性能能

3、不管是直接多次插入数据库还是采用Batch方式执行数据库的插入，均会发送多次SQL脚本去执行，这样明显没有发送一次SQL脚本执行来的效率高

4、采用单SQL执行数据库批量操作时Statement对比PreparedStatement有微弱的优势，可能是Statement不需要判断注参的原因吧

[copycode.gif]: /images/20220612/cd457fd034ec401db087249df29c96f4.png
[1082159-20170110000211900-384900792.png]: /images/20220612/5faab2c294b1494d9690fb3f85116bc0.png
[1082159-20170110000225666-1051892039.png]: /images/20220612/1e97f0e53b624e2992b69d8c442031e4.png