批处理基本介绍
当需要成批插入或者更新记录时,可以采用java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理,通常情况下比单独提交处理更有效率 JDBC的批量处理语句包括下面方法: addBatch():添加需要批量处理的sql语句或参数 executeBatch():执行批量处理语句 clearBatch():清空批处理包的语句 JDBC连接 mysql 时,如果要使用批处理功能,需要在 url 中加参数 ?rewriteBatchedStatements=true 批处理往往和 PreparedStatement 一起搭配使用,可以既减少编译次数,又减少运行次数,效率大大提高
package com. ftn. jdbc. batch_ ;
import com. ftn. jdbc. JDBCutils. JDBCUtils ;
import org. junit. jupiter. api. Test ;
import java. sql. Connection ;
import java. sql. PreparedStatement ;
import java. sql. SQLException ;
public class Batch_ {
@Test
public void noBatch ( ) {
Connection connect = null ;
String sql = "insert into admin2 values(null,?,?)" ;
PreparedStatement preparedStatement = null ;
try {
connect = JDBCUtils . connect ( ) ;
preparedStatement = connect. prepareStatement ( sql) ;
System . out. println ( "开始执行" ) ;
long start = System . currentTimeMillis ( ) ;
for ( int i = 0 ; i < 5000 ; i++ ) {
preparedStatement. setString ( 1 , "jack" + i) ;
preparedStatement. setString ( 2 , "123456" + i) ;
preparedStatement. executeUpdate ( ) ;
}
long end = System . currentTimeMillis ( ) ;
System . out. println ( "传统方法耗时:" + ( end- start) ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . close ( null , preparedStatement, connect) ;
}
}
@Test
public void Batch ( ) {
Connection connect = null ;
String sql = "insert into admin2 values(null,?,?)" ;
PreparedStatement preparedStatement = null ;
try {
connect = JDBCUtils . connect ( ) ;
preparedStatement = connect. prepareStatement ( sql) ;
System . out. println ( "开始执行" ) ;
long start = System . currentTimeMillis ( ) ;
for ( int i = 0 ; i < 5000 ; i++ ) {
preparedStatement. setString ( 1 , "jack" + i) ;
preparedStatement. setString ( 2 , "123456" + i) ;
preparedStatement. addBatch ( ) ;
if ( ( i+ 1 ) % 1000 == 0 ) {
preparedStatement. executeBatch ( ) ;
preparedStatement. clearBatch ( ) ;
}
}
long end = System . currentTimeMillis ( ) ;
System . out. println ( "批处理方法耗时:" + ( end- start) ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
JDBCUtils . close ( null , preparedStatement, connect) ;
}
}
}