JDBC批量插入数据效率分析

CREATE TABLE T_PRODUCT (
ID bigint(12) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
NAME varchar(60) NOT NULL COMMENT ‘产品名称’,
WEIGHT varchar(60) NOT NULL COMMENT ‘产品重量’,
MARK varchar(60) NOT NULL COMMENT ‘产品说明’,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=’产品表’;

package com.luwei.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

/**
* <Description> TODO<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 <br>
* @since V1.0<br>
* @see com.luwei.test.jdbc <br>
*/
public class JdbcTemplate {
private static String DRIVER_CLASS_NAME = null;
private static String URL = null;
private static String USERNAME = null;
private static String PASSWORD = null;

static {
ResourceBundle bundle = ResourceBundle.getBundle(“jdbc”);
DRIVER_CLASS_NAME = bundle.getString(“jdbc.driverClassName”);
URL = bundle.getString(“jdbc.url”);
USERNAME = bundle.getString(“jdbc.username”);
PASSWORD = bundle.getString(“jdbc.password”);
}

/**
*
* <Description> 获取数据库连接<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 下午10:19:41 <br>
* @return
* @throws Exception
* <br>
*/
public static Connection getConnection() throws Exception {
Class.forName(DRIVER_CLASS_NAME);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return connection;
}

/**
*
* <Description> 提交事务<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 下午10:20:48 <br>
* @param connection
* <br>
*/
public static void commit(Connection connection) {
try {
connection.commit();
}
catch (SQLException e) {
e.printStackTrace();
}
}

/**
*
* <Description> 开启事务<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 下午10:23:56 <br>
* @param connection
* <br>
*/
public static void beginTx(Connection connection) {
try {
connection.setAutoCommit(false);
}
catch (SQLException e) {
e.printStackTrace();
}
}

/**
*
* <Description> 回滚<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 下午10:24:33 <br>
* @param connection
* <br>
*/
public static void rollback(Connection connection) {
try {
connection.rollback();
}
catch (SQLException e) {
e.printStackTrace();
}
}

/**
*
* <Description> TODO<br>
*
* @author lu.wei<br>
* @email 1025742048@qq.com <br>
* @date 2017年1月9日 下午10:28:49 <br>
* @param statement
* @param connection
* <br>
*/
public static void releaseDb(Statement statement, Connection connection) {
try {
statement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}

@Test
public void testStatement() {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);

statement = connection.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
String sql = “insert into t_product values(null,’name_” + i + “‘,’120kg’,’mark_” + i + “‘)”;
statement.execute(sql);
}
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}

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

@Test
public void testPreparedStatement() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);
String sql = “insert into t_product values(null,?,?,?)”;

statement = connection.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
statement.setString(1, “name_” + i);
statement.setString(2, “120kg”);
statement.setString(3, “mark_” + i);
statement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}

@Test
public void testBatchStatement() {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);

statement = connection.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
String sql = “insert into t_product values(null,’name_” + i + “‘,’120kg’,’mark_” + i + “‘)”;
statement.addBatch(sql);

if ((i + 1) % 100 == 0) {
statement.executeBatch();
statement.clearBatch();
}
}
statement.executeBatch();
statement.clearBatch();
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}

@Test
public void testBatchPreparedStatement() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);
String sql = “insert into t_product values(null,?,?,?)”;

statement = connection.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
statement.setString(1, “name_” + i);
statement.setString(2, “120kg”);
statement.setString(3, “mark_” + i);
statement.addBatch();
if ((i + 1) % 100 == 0) {
statement.executeBatch();
statement.clearBatch();
}
}
statement.executeBatch();
statement.clearBatch();
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}

@Test
public void testMutilValueStatement() {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);

statement = connection.createStatement();

StringBuffer sql = new StringBuffer(“insert into t_product values”);
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
if (i != 0) {
sql.append(“,”);
}
sql.append(“(null,’name_” + i + “‘,’120kg’,’mark_” + i + “‘)”);
}
statement.execute(sql.toString());
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}

@Test
public void testMutilValuePreparedStatement() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JdbcTemplate.getConnection();
JdbcTemplate.beginTx(connection);

StringBuffer sql = new StringBuffer(“insert into t_product values”);
long begin = System.currentTimeMillis();
for (int i = 0; i < 100000; i++) {
if (i != 0) {
sql.append(“,”);
}
sql.append(“(null,’name_” + i + “‘,’120kg’,’mark_” + i + “‘)”);
}
statement = connection.prepareStatement(sql.toString());
statement.executeUpdate();
long end = System.currentTimeMillis();
System.out.println(end – begin);
JdbcTemplate.commit(connection);
}
catch (Exception e) {
e.printStackTrace();
JdbcTemplate.rollback(connection);
}
finally {
JdbcTemplate.releaseDb(statement, connection);
}
}