对于需要批量插入数据库操作JDBC有多重方式，本利从三个角度对Statement和PreparedStatement两种执行方式进行分析，总结较优的方案。
当前实现由如下条件：
执行数据库：Mysql
执行数据数量：10万条
执行前提：执行差入数据库钱均需要提供空表，防止数据量大造成的影响
执行方式：Statement和PreparedStatement两种方式
执行步骤开始：
1、创建表
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=’产品表’;
2、编写操作数据库工具类
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();
}
}
}
3、执行数据库插入操作
3.1、使用Statement直接插入，三次执行耗时：41979 42608 42490
@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);
}
}
3.3、使用BatchStatement直接插入，三次执行耗时：15342 15235 15485
@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);
}
}
3.4、使用BatchPreparedStatement直接插入，三次执行耗时：21913 22045 23291
@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);
}
}
3.5、使用采用多Value值Statement直接插入，三次执行耗时：2931 3007 3203 2964
@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);
}
}
3.6、使用采用多Value值PreparedStatement直接插入，三次执行耗时：3356 3218 3233
@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);
}
}
通过以上时间结果得出如下数据表格：
总结：通过如上的数据对比发现
1、PreparedStatement执行数据库插入比使用Statement执行数据库插入明显有性能优势，原因归功于PreparedStatement能够预先对SQL进行编译，做到执行时进行SQL共享
2、执行数据库批量操作是使用Batch方式对数据库采用批次操作能够明显提升数据库操作性能能
3、不管是直接多次插入数据库还是采用Batch方式执行数据库的插入，均会发送多次SQL脚本去执行，这样明显没有发送一次SQL脚本执行来的效率高
4、采用单SQL执行数据库批量操作时Statement对比PreparedStatement有微弱的优势，可能是Statement不需要判断注参的原因吧
本文链接：https://www.aiunk.com/1165/
“兼山艮，君子以思不出其位。”
评论(0)