jdbc批处理和事务的例子

小灰灰 2022-08-24 04:18 202阅读 0赞

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
 	public static String getFailIds(int[] resArr,int[] idArrTemp){
 		String fails=null;
 		if(resArr==null||idArrTemp==null||(2*idArrTemp.length)<resArr.length){
 			System.out.println("方法getFailIds参数不合法！");
 			return null;
 		}
 		for(int i=0;i<resArr.length;i++){
 			if(1!=resArr[i]){
 				fails+=idArrTemp[i/2]+",";
 			}
 		}
 		return fails;
 	}
 	
 	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;
 	}
 }

其中DB2Util类就是获取数据库连接和执行sql的类，因为本例都是原生的sql代码，只有获取链接的时候用了这个类，所以jdbc链接的代码就不贴了。