批处理,可以大幅度提升大量增、删、改的速度,当然这也看具体的数据库驱动的实现。
package cn.itcast.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/**
* 测试批处理性能
*/
public class BatchTest {
/**
* 测试使用for循环插入100条记录所花费的时间
* @throws SQLException
*/
@Test
public void testUseFor() throws SQLException {
long start = System.currentTimeMillis();
for(int i = 0; i < 100; i ++) {
create(i);
}
long end = System.currentTimeMillis();
System.out.println("create:" + (end - start));
}
/**
* 使用批处理所用的时间
* @throws SQLException
*/
@Test
public void testCreateBatch() throws SQLException {
long start = System.currentTimeMillis();
createBatch();
long end = System.currentTimeMillis();
System.out.println("createBatch:" + (end - start));
}
/**
* 单条记录一个一个测试
* @param i
* @throws SQLException
*/
static void create(int i) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into user(name,birthday, money) values (?, ?, ?)";
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "no batch name" + i);
ps.setDate(2, new Date(System.currentTimeMillis()));
ps.setFloat(3, 100f + i);
ps.executeUpdate();
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
/**
* 创建批处理操作
* @throws SQLException
*/
static void createBatch() throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into user(name,birthday, money) values (?, ?, ?)";
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for(int i = 0; i < 100; i ++) {
ps.setString(1, "batch name" + i);
ps.setDate(2, new Date(System.currentTimeMillis()));
ps.setFloat(3, 100f + i);
//添加批处理操作
ps.addBatch();
}
//执行批量操作
int[] is = ps.executeBatch();
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}