Java批量删除需要多线程吗_【Oracle/Java】批量删除16张十万数据的表 单线程耗时45秒 多线程耗时38秒...

packagecom.hy.delete.multithread;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.log4j.Logger;importcom.hy.DBParam;public class DeleteThread extendsThread {private static Logger log = Logger.getLogger(DeleteThread.class);//Commit size

private static final int commitSize=10000;private int tableIndex;//table serial number

private String table;//table will be deleted

private String expiredDate;//expired date

private DeleteManager menager; //reference to manager

public DeleteThread(inttableIndex,String table,String expiredDate,DeleteManager menager) {this.tableIndex=tableIndex;this.table=table;this.expiredDate=expiredDate;this.menager=menager;

}public voidrun() {

Connection conn= null;

Statement stmt= null;try{long startTime =System.currentTimeMillis();

Class.forName(DBParam.Driver).newInstance();

conn=DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);

stmt=conn.createStatement();

log.info("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"...");int total=delete(tableIndex,table,expiredDate,conn,stmt);

log.info("#"+tableIndex+" "+total+" records deleted from table:'"+table+"'.");long endTime =System.currentTimeMillis();

String timeElasped=sec2DHMS(startTime,endTime);

menager.reportFinished(String.valueOf(tableIndex), table, timeElasped,String.valueOf(total));

}catch(Exception e) {

e.printStackTrace();

}finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

log.error("Can't close stmt/conn because of " +e.getMessage());

}

}

}/*** 按日期删一张表的记录

*@paramtableIndex

*@paramtable

*@paramexpiredDate

*@paramconn

*@paramstmt

*@throwsSQLException*/

private int delete(int tableIndex,String table,String expiredDate,Connection conn,Statement stmt) throwsSQLException {int totalDeleted=0;int expiredCount=0;do{

String sql="delete from "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum

totalDeleted+=deleted;

expiredCount=queryExpiredCount(table,expiredDate,stmt);

}while(expiredCount>0);returntotalDeleted;

}/*** 查询过期记录数量

*@paramtable

*@paramexpiredDate

*@paramconn

*@paramstmt

*@return*@throwsSQLException*/

private int queryExpiredCount(String table,String expiredDate,Statement stmt) throwsSQLException {

String sql="SELECT COUNT (*) as cnt FROM "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<10 ";

ResultSet rs=stmt.executeQuery(sql);while(rs.next()) {int count = rs.getInt("cnt");returncount;

}return 0;

}/*** change seconds to DayHourMinuteSecond format

*@paramstratMs

*@paramendMs

*@return

*/

private static String sec2DHMS(long stratMs,longendMs) {

String retval= null;long secondCount=(endMs-stratMs)/1000;long days = secondCount / (60 * 60 * 24);long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);long minutes = (secondCount % (60 * 60)) / 60;long seconds = secondCount % 60;if (days > 0) {

retval= days + "d" + hours + "h" + minutes + "m" + seconds + "s";

}else if (hours > 0) {

retval= hours + "h" + minutes + "m" + seconds + "s";

}else if (minutes > 0) {

retval= minutes + "m" + seconds + "s";

}else{

retval= seconds + "s";

}returnretval;

}

}