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;
}
}