JDBC批量处理
批量处理JDBC语句提高处理速度
~addBatch(String):添加需要批量处理的SQL语句或是参数;
~executeBatch():执行批量处理语句;
/**
* 向Oracle的customers数据表中插入10万条记录
* 测试如何插入,用时最短
* 1.使用Statement
* 2.使用PreparedStatement
* 3.使用Batch
*/
@Test
public void testBatchWithStatement(){
Connection conn = null;
Statement statement = null;
String sql = null;
try {
conn = Methods.getConnection();
Methods.beginTx(conn);
statement = conn.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i <10000 ; i++) {
sql="insert into account values("+(i+1) + ",name_"+i+")";
statement.executeUpdate(sql);
}
long end = System.currentTimeMillis();
System.out.println(end-begin);//39567
Methods.commit(conn);
} catch (Exception e) {
e.printStackTrace();
Methods.rollback(conn);
}finally {
Methods.release(statement,conn,null);
}
}
@Test
public void testBatchWithPreparedStatement(){
Connection conn = null;
PreparedStatement preparedStatement= null;
String sql = null;
try {
conn = Methods.getConnection();
Methods.beginTx(conn);
sql="insert into account(name,password) values(?,?)";
preparedStatement = conn.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i <10000 ; i++) {
preparedStatement.setString(1,"name_"+i);
preparedStatement.setString(2,"i");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println(end-begin);//9819
Methods.commit(conn);
} catch (Exception e) {
e.printStackTrace();
Methods.rollback(conn);
}finally {
Methods.release(preparedStatement,conn,null);
}
}
@Test
public void testBatch(){
Connection conn = null;
PreparedStatement preparedStatement= null;
String sql = null;
try {
conn = Methods.getConnection();
Methods.beginTx(conn);
sql="insert into account(name,password) values(?,?)";
preparedStatement = conn.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i <10000 ; i++) {
preparedStatement.setString(1,"name_"+i);
preparedStatement.setString(2,"i");
//“积攒”SQL
preparedStatement.addBatch();
//当积攒到一定程度,就统一的执行一次,并且清空先前"积攒"的SQL
if((i+1)%300==0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
//若总条数不是批量数值的整数倍,则还需要再额外的执行一次
if(100000%300!=0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println(end-begin);//569
Methods.commit(conn);
} catch (Exception e) {
e.printStackTrace();
Methods.rollback(conn);
}finally {
Methods.release(preparedStatement,conn,null);
}
}
从util.Date转为sql.Date
Date date = new Date(new java.util.Date().getTime());