package com.dbtest;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import com.util.DB2Util;
/**
* 这是个批处理和事务的例子,不是工具类!
*
*
* 这个类涉及了jdbc批处理和事务!
*
* 具体测试逻辑就是批量的把一张表的数据转移到另外一张表中去。
* 即把一张表A中的数据插入到表B中,然后再从表A中删除已经转移的数据。
*
* @author lushuaiyin
*
*/
public class BatchTest {
/**
* @param args
*/
public static void main(String[] args) {
// String failIds="a,b,";
// int ddd=failIds.split(",").length;
// System.out.println("ddd==="+ddd);
// System.out.println(Arrays.toString(failIds.split(","))+"==");
// double dd=-16456;
// System.out.println(dd);
//
// String aa=formatDouble(dd);
// System.out.println("aa==="+aa);
doit();
}
private static final long serialVersionUID = 8179632967640530390L;
private static final String JOB_DO="[测试##事后小结数据迁移]";
private static final String TABLE_NAME_TELRECORD="xxx";//事后小结历史表
private static final String TABLE_NAME_TELRECORD_DAY="qqq";//事后小结当日表
private static int batchNum=10;//批处理数
private static void doit() {
long oldTime11=getCurrentTimeMillis();//计时开始
StringBuffer failedIdBuf=new StringBuffer("");//记录失败的数据id
//截止时间点之前的数据要执行批处理
Date startTime=new Date();
String startTimeStr=getDateNow(startTime);
System.out.println(JOB_DO+"定时任务开始时间:" + startTime+",导入数据截止时间点["+startTimeStr+"]");
/*********************导数据start*************************************/
// String sql_data_select="select * from "+TABLE_NAME_TELRECORD_DAY+" a " +
// " where a.STARTTIME !='' and a.STARTTIME is not null " +
// " and a.STARTTIME<='"+startTimeStr+"'";
String sql_data_select2="select ID from "+TABLE_NAME_TELRECORD_DAY+" a " +
" where a.STARTTIME !='' and a.STARTTIME is not null " +
" and a.STARTTIME<='"+startTimeStr+"'";
System.out.println(JOB_DO+"定时任务准备数据sql[ " + sql_data_select2+" ]");
// String sql_data_insert="insert into "+TABLE_NAME_TELRECORD+"(" +
// "CALLID, DEALOPER,EMPLYEENAME,EXTENNO,CALLNO," +
// "CALLTYPE, CUSTNAME, STARTTIME, ENDTIME, CALLTIME, " +
// " RECNUMBER, RECURL, CUSTTYPE, ACCOUNTNO, CUSTOMNO," +
// "OPERTYPE, DISFLAG, SERVEITEM, FIRSTCLASS, SECONDCLASS," +
// "SERVECONTENT, SERVERESULT, REFITEM,STOCK, EVALUATE," +
// " EFFECT, REMARK, ZONEN, SERVTYPE, CONNECTID," +
// "RECORDID, CERTTYPE, CERTNO,CALLEDNO, EXAMINE," +
// "EXAMMARKFLAG, EXAMMARKFLAG1,MOBILE,HOMETEL, OFFICETEL," +
// "ISPJ)" +
// "values ( ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// " ?,?,?,?,?," +
// "?);";
String sql_data_insert2="insert into "+TABLE_NAME_TELRECORD+"(" +
"CALLID, DEALOPER,EMPLYEENAME,EXTENNO,CALLNO," +
"CALLTYPE, CUSTNAME, STARTTIME, ENDTIME, CALLTIME, " +
" RECNUMBER, RECURL, CUSTTYPE, ACCOUNTNO, CUSTOMNO," +
"OPERTYPE, DISFLAG, SERVEITEM, FIRSTCLASS, SECONDCLASS," +
"SERVECONTENT, SERVERESULT, REFITEM,STOCK, EVALUATE," +
" EFFECT, REMARK, ZONEN, SERVTYPE, CONNECTID," +
"RECORDID, CERTTYPE, CERTNO,CALLEDNO, EXAMINE," +
"EXAMMARKFLAG, EXAMMARKFLAG1,MOBILE,HOMETEL, OFFICETEL," +
"ISPJ)" +
" select " +
"CALLID, DEALOPER,EMPLYEENAME,EXTENNO,CALLNO," +
"CALLTYPE, CUSTNAME, STARTTIME, ENDTIME, CALLTIME, " +
" RECNUMBER, RECURL, CUSTTYPE, ACCOUNTNO, CUSTOMNO," +
"OPERTYPE, DISFLAG, SERVEITEM, FIRSTCLASS, SECONDCLASS," +
"SERVECONTENT, SERVERESULT, REFITEM,STOCK, EVALUATE," +
" EFFECT, REMARK, ZONEN, SERVTYPE, CONNECTID," +
"RECORDID, CERTTYPE, CERTNO,CALLEDNO, EXAMINE," +
"EXAMMARKFLAG, EXAMMARKFLAG1,MOBILE,HOMETEL, OFFICETEL," +
"ISPJ " +
" from " +TABLE_NAME_TELRECORD_DAY+
" where ID=";
String sql_data_delete="delete from "+TABLE_NAME_TELRECORD_DAY+" t where t.ID=";
//查询
DB2Util dB2Util=new DB2Util();
Connection con2=null;
Statement stmt2=null;
ResultSet rs2=null;
//插入和删除/
Connection con3=null;
// PreparedStatement pstmt3=null;
Statement stmt3=null;
double allNum=0;//记录数量
double failNum=0;//失败数量
try {
con3=dB2Util.getConnectionNew();//插入和删除,需要事务处理
con3.setAutoCommit(false);//禁用自动提交
//
con2=dB2Util.getConnectionNew();
stmt2 = con2.createStatement();
rs2=stmt2.executeQuery(sql_data_select2);
stmt3=con3.createStatement();
int bat=0;//本批条数
int[] idArrTemp=new int[batchNum];//本批中的id
// if(rs2.next()){
while(rs2.next()){
String id=rs2.getString("ID");
System.out.println("id=="+id);
if(id==null||id.trim().equals("")){
System.out.println("出现id为空!!!时间:"+getDateNow());
continue;
}
int id2=Integer.valueOf(id);
idArrTemp[bat]=id2;
// //插入到历史表
// pstmt3=con3.prepareStatement(sql_data_insert2);
// System.out.println("sql_data_insert2=="+sql_data_insert2);
// pstmt3.setObject(1, id2);
// pstmt3.addBatch();
//
// //同时删除当日表里的记录(顺序不能反)
pstmt3=con3.prepareStatement(sql_data_delete);
System.out.println("sql_data_delete=="+sql_data_delete);
pstmt3.setObject(1, id2);
// String sqlDel=sql_data_delete+id2;
// pstmt3.addBatch(sqlDel);
// pstmt3.addBatch();
/*
批处理这里不能使用PreparedStatement。因为这里是不同的sql!
如果你执行的是同一条sql,只是参数不同,那么可以使用PreparedStatement。
如果是不同的sql,那么在con3.prepareStatement时就把以前的对象覆盖掉了!
这样运行的结果总是最后一次循环的参数!
所以这里只能使用Statement!
*/
//插入到历史表
String sqlInsert=sql_data_insert2+id2;
System.out.println("sqlInsert=="+sqlInsert);
stmt3.addBatch(sqlInsert);
//同时删除当日表里的记录(顺序不能反)
String sqlDel=sql_data_delete+id2;
System.out.println("sqlDel=="+sqlDel);
stmt3.addBatch(sqlDel);
allNum++;
bat++;//同时插入并删除一条记录算一个
//执行批处理
if(bat==batchNum){
int[] resArr=stmt3.executeBatch();
con3.commit();//提交事务
stmt3.clearBatch();
System.out.println(allNum+",执行结果打印:"+Arrays.toString(resArr));
String failIds=getFailIds(resArr,idArrTemp);
if(failIds!=null){
failNum+=failIds.split(",").length;
failedIdBuf.append(failIds);
System.out.println("本批次执行失败的数据id是:"+failIds);
}
bat=0;//清0
}
}//end while
//执行批处理
if(bat>0){
int[] resArr=stmt3.executeBatch();
con3.commit();//提交事务
stmt3.clearBatch();
System.out.println(formatDouble(allNum)+",执行结果打印:"+Arrays.toString(resArr));
String failIds=getFailIds(resArr,idArrTemp);
if(failIds!=null){
failNum+=failIds.split(",").length;
failedIdBuf.append(failIds);
System.out.println("..本批次执行失败的数据id是:"+failIds);
}
bat=0;//清0
}
/**************统计定时任务结果start*********************/
StringBuffer resMessage=new StringBuffer();
resMessage.append(JOB_DO+"定时任务执行完毕!");
resMessage.append("执行时间:"+getPassTimeStrFromOld(oldTime11));
resMessage.append(",涉及数据总量[ "+formatDouble(allNum)+" ],失败数量[ "
+formatDouble(failNum)+" ],失败数据id ["+failedIdBuf.toString()+" ]");
System.out.println(resMessage.toString());
/**************统计定时任务结果end*********************/
} catch (SQLException ex) {
System.out.println(ex.getMessage());
if(con3!=null){
try {
con3.rollback();
con3.setAutoCommit(true);
System.out.println("出现SQLException异常,事务回滚。。。");
} catch (SQLException exxx) {
exxx.printStackTrace();
}
}
} catch (Exception e) {
if(con3!=null){
try {
con3.rollback();
con3.setAutoCommit(true);
System.out.println("出现Exception异常,事务回滚。。。");
} catch (SQLException exxx) {
exxx.printStackTrace();
}
}
} finally {
try {
if(stmt3!=null){
stmt3.close();
}
if(con3!=null){
con3.close();
}
/
if(rs2!=null){
rs2.close();
}
if(stmt2!=null){
stmt2.close();
}
if(con2!=null){
con2.close();
}
///
} catch (SQLException e) {
e.printStackTrace();
}
}
/*********************导数据end*************************************/
}
public static String getDateNow(){
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(new Date());
}
public static String getDateNow(Date date){
Date dd=new Date();
if(date==null){
dd=new Date();
}else{
dd=date;
}
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(dd);
}
//执行结果和对应的id
<span style="white-space:pre"> </span>public static String getFailIds(int[] resArr,int[] idArrTemp){
<span style="white-space:pre"> </span>String fails=null;
<span style="white-space:pre"> </span>if(resArr==null||idArrTemp==null||(2*idArrTemp.length)<resArr.length){
<span style="white-space:pre"> </span>System.out.println("方法getFailIds参数不合法!");
<span style="white-space:pre"> </span>return null;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>for(int i=0;i<resArr.length;i++){
<span style="white-space:pre"> </span>if(1!=resArr[i]){
<span style="white-space:pre"> </span>fails+=idArrTemp[i/2]+",";
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>return fails;
<span style="white-space:pre"> </span>}
public static long getCurrentTimeMillis(){
long timeNow=System.currentTimeMillis();
return timeNow;
}
public static String getPassTimeStrFromOld(long oldTime){
long passTime=getExecuteTimeMillis(oldTime);
return getPassTimeStr(passTime);
}
public static long getExecuteTimeMillis(long oldTime){
long timeNow=getCurrentTimeMillis();
long passTime=timeNow-oldTime;
return passTime;
}
public static String getPassTimeStr(long passTime){
String res="";
long passTimeMillis=passTime%1000;
long passTimeSeconds=passTime/1000;//秒
if(passTime<1000){
res=passTime+"毫秒";
}else{//有秒
long passTimeSl=passTimeSeconds%60;//实际秒
long passTimeMins=passTimeSeconds/60;//分
if(passTimeMins==0){
res=passTimeSl+"秒,"+passTimeMillis+"毫秒";
}else{//有分
long passTimeMinreal=passTimeMins%60;//实际分
long passTimeHour=passTimeMins/60;//小时
if(passTimeHour==0){//没有小时
res=passTimeMinreal+"分,"+passTimeSl+"秒,"+passTimeMillis+"毫秒";
}else{//有小时
res=passTimeHour+"小时,"+passTimeMinreal+"分,"+passTimeSl+"秒,"+passTimeMillis+"毫秒";
}
}
}
return res;
}
public static String formatDouble(double dd){
String res="";
res=String.valueOf(dd);
res=res.substring(0, res.indexOf("."));
return res;
}
}