一个jdbc工具类和批处理的例子

没保存。

package com.db2;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Timestamp;import java.text.SimpleDateFormat;import java.util.Arrays;import java.util.Date;import java.util.HashMap;import java.util.Map;import java.util.Properties;//import sun.jdbc.rowset.CachedRowSet;public class SQL2UtilJdbc {Connection conn = null; // Hold the connection to the databaseStatement stmt = null;PreparedStatement pstmt = null;ResultSet rs = null;//ServletContext ctext = null;public static boolean isTest = true;//是否测试，这个最后一定要改成falsepublic static Properties props =null;public static Properties getProp(){if(props==null){props=getCommonProp();}return props;}public SQL2UtilJdbc() {}//public SQL2UtilJdbc(ServletContext ctext) {//this.ctext = ctext;//}//public SQL2Util(ServletContext ctext, String str) {//this.ctext = ctext;//this.dbsource = str;//}///////////////////2014-05-07增加方法start//////////////////////////////////////////** * 这个方法返回的是结果集，所以不能在这个方法里关闭结果集（因为外面还要遍历）。 * 所以在调用完这个方法遍历万结果集后，记得关闭外面的结果集。 *  * pstmt.executeUpdate();//INSERT, UPDATE or DELETE *  * pstmt.execute();//true if the first result is a ResultSet object;  *                 false if the first result is an update count or there is no result *  * pstmt.executeQuery();//returns the ResultSet object generated by the query. */public ResultSet selectPrep(String sqlStr, Object[] params) throws Exception {if(sqlStr==null||sqlStr.trim().equals("")){String info1="sql语句sqlStr不能为空！ ";System.out.println(info1);throw new Exception(info1);}if(params==null||params.length<=0){String info1="sql语句参数列表params不能为空！ ";System.out.println(info1);throw new Exception(info1);}System.out.println("方法selectPrep====="+sqlStr);getconnwait();ResultSet rsn=null;try {pstmt = conn.prepareStatement(sqlStr);//pstmt = conn.prepareStatement(sqlStr,//ResultSet.TYPE_SCROLL_INSENSITIVE,//ResultSet.CONCUR_READ_ONLY);// java.sql.Typesfor (int i = 0; i < params.length; i++) {// prestmt.setObject(parameterIndex, x, targetSqlType);Object para=params[i];pstmt.setObject(i+1, para);}rsn=pstmt.executeQuery();//returns the ResultSet object generated by the query.} catch (Exception e) {System.out.println(sqlStr);e.printStackTrace();}finally{//freeconn();//查询需要对结果集操作所以必须在外面关闭。使用freeconn(Object obj)}return rsn;}/** * 该方法没有参数，即它只执行一个完整的，不带问号的sql语句。 * 为了和有参数的分开，防止参数不合法。和selectTrue作用相同。 *  * 这个方法返回的是结果集，所以不能在这个方法里关闭结果集（因为外面还要遍历）。 * 所以在调用完这个方法遍历万结果集后，记得关闭外面的结果集。 *  * pstmt.executeUpdate();//INSERT, UPDATE or DELETE *  * pstmt.execute();//true if the first result is a ResultSet object;  *                 false if the first result is an update count or there is no result *  * pstmt.executeQuery();//returns the ResultSet object generated by the query. */public ResultSet selectPrepNoParam(String sqlStr) throws Exception {if(sqlStr==null||sqlStr.trim().equals("")){String info1="sql语句sqlStr不能为空！ ";System.out.println(info1);throw new Exception(info1);}System.out.println("方法selectPrepNoParam====="+sqlStr);getconnwait();ResultSet rsn=null;try {pstmt = conn.prepareStatement(sqlStr);rsn=pstmt.executeQuery();//returns the ResultSet object generated by the query.} catch (Exception e) {System.out.println(sqlStr);e.printStackTrace();}finally{//freeconn();//查询需要对结果集操作所以必须在外面关闭。使用freeconn(Object obj)}return rsn;}/** *  * pstmt.executeUpdate();//INSERT, UPDATE or DELETE *  * pstmt.execute();//true if the first result is a ResultSet object;  *                 false if the first result is an update count or there is no result *  * pstmt.executeQuery();//returns the ResultSet object generated by the query. */public boolean othersqlPrep(String sqlStr, Object[] params) throws Exception {if(sqlStr==null||sqlStr.trim().equals("")){String info1="sql语句sqlStr不能为空！ ";System.out.println(info1);throw new Exception(info1);}if(params==null||params.length<=0){String info1="sql语句参数列表params不能为空！ ";System.out.println(info1);throw new Exception(info1);}System.out.println("方法othersqlPrep====="+sqlStr);boolean flag = false;getconnwait();pstmt = conn.prepareStatement(sqlStr);if (pstmt != null) {try {// java.sql.Typesfor (int i = 0; i < params.length; i++) {// prestmt.setObject(parameterIndex, x, targetSqlType);Object para=params[i];pstmt.setObject(i+1, para);}/*either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing*/int reNum=pstmt.executeUpdate();//INSERT, UPDATE or DELETE//pstmt.execute();flag = true;} catch (Exception e) {System.out.println("方法othersqlPrep异常："+sqlStr+"\n"+e.getMessage());throw e;}finally{freeconn();}} else {throw new Exception("获得数据库连接失败！");}System.out.println("方法othersqlPrep执行结果====="+flag+",sql:"+sqlStr);return flag;}public void freeconn(Object obj) {try {if(obj instanceof Statement){Statement stmt=(Statement)obj;if(stmt!=null){stmt.close();stmt=null;}}else if(obj instanceof PreparedStatement){PreparedStatement pstmt=(PreparedStatement)obj;if(pstmt!=null){pstmt.close();pstmt=null;}}else if(obj instanceof Connection){Connection conn=(Connection)obj;if(conn!=null){conn.close();conn=null;}}else if(obj instanceof ResultSet){ResultSet rs=(ResultSet)obj;if(rs!=null){rs.close();rs=null;}}} catch (Exception e) {e.printStackTrace();}}    ///////////////////2014-05-07增加方法end///////////////////////////////////////////public void getconnwait() throws Exception {try {conn = getweblogicConnection();stmt = conn.createStatement();try {stmt.execute("SET LOCK MODE TO WAIT 10");stmt.execute("SET ISOLATION TO DIRTY READ");} catch (Exception e) {System.out.println("方法getconnwait异常：\n"+e.getMessage());}} catch (Exception e) {System.out.println(e.getMessage());freeconn();throw e;}}public void freeconn() {try {if(rs!=null){rs.close();}if(stmt!=null){stmt.close();}if(pstmt!=null){pstmt.close();}if(conn!=null){conn.close();}rs = null;stmt = null;pstmt=null;conn = null;} catch (Exception e) {}}//public CachedRowSet select(String SqlStr) throws Exception {//getconnwait();//if (stmt != null) {//try {//rs = stmt.executeQuery(SqlStr);//} catch (Exception e) {//System.err.println("查询失败：" + SqlStr);//freeconn();//throw e;//}//} else {//throw new Exception("获得数据库连接失败！");//}//CachedRowSet crs = new CachedRowSet();//crs.populate(rs);//freeconn();//return crs;//}//// 此函数不关闭数据库连接，在调用函数出关闭//public oracle.jdbc.rowset.OracleCachedRowSet select(String SqlStr,//Connection tmpConn) throws Exception {//ResultSet rsTmp = null;//Statement stmTmp = tmpConn.createStatement();//// CachedRowSet crs =null;//oracle.jdbc.rowset.OracleCachedRowSet crs = null;//try {//rsTmp = stmTmp.executeQuery(SqlStr);//// crs=new CachedRowSet();//crs = new oracle.jdbc.rowset.OracleCachedRowSet();//crs.populate(rsTmp);//} catch (Exception e) {//System.out.println("异常：" + e.toString());//stmTmp.close();//throw e;//} finally {//stmTmp.close();//}//return crs;//}public ResultSet selectTrue(String SqlStr) throws Exception {getconnwait();if (stmt != null) {try {rs = stmt.executeQuery(SqlStr);} catch (Exception e) {freeconn();e.printStackTrace();throw e;}} else {throw new Exception("获得数据库连接失败！");}return rs;}public boolean otherOracleSql(String SqlStr, Connection tmpConn)throws Exception {boolean flag = false;Statement tmpstat = tmpConn.createStatement();try {flag = tmpstat.execute(SqlStr);} catch (Exception e) {tmpstat.close();throw e;} finally {tmpstat.close();return flag;}}public boolean othersql(String SqlStr) throws Exception {boolean flag = false;getconnwait();if (stmt != null) {try {flag = stmt.execute(SqlStr);} catch (Exception e) {System.err.println("sqlstr:" + SqlStr);freeconn();throw e;}} else {throw new Exception("获得数据库连接失败！");}freeconn();return flag;}public String getBlob(InputStream is) {// InputStream is = rs.getBinaryStream(1);String rStr = "";try {int bytesRead = 0;byte[] buffer = new byte[8 * 1924];if (is != null) {while ((bytesRead = is.read(buffer)) != -1) {// byteSum += bytesRead;// fis2.write(buffer, 0, bytesRead);String temp = new String(buffer, 0, bytesRead);rStr = rStr + temp;}is.close();}} catch (Exception e) {e.printStackTrace();}return rStr;}public Connection getweblogicConnection() throws SQLException {Connection conn = null;String  old_db2_url="jdbc:db2://10.125.190.63:60000/agent";Stringold_db2_username="agent";Stringold_db2_password="agent";try {Class.forName("com.ibm.db2.jcc.DB2Driver");conn = DriverManager.getConnection(old_db2_url, old_db2_username, old_db2_password);} catch (Exception e) {conn = null;e.printStackTrace();}return conn;}public static Properties getCommonProp(){String pah=SQL2UtilJdbc.class.getClassLoader().getResource("").getFile();File pf=new File(pah);String parentpath=pf.getParent();String propFilePath=parentpath+"\\common.properties";System.out.println(propFilePath);Properties props = new Properties();InputStream in = null;try {in =new FileInputStream(new File(propFilePath));props.load(in);} catch (Exception e) {e.printStackTrace();}finally{try {if(in!=null){in.close();}} catch (Exception e) {e.printStackTrace();}}return props;}public static String getPropValue(String key){String res="";if(key==null||key.trim().equals("")||key.trim().equals("null")){return "";}Properties props = getProp();if(props!=null){res=props.getProperty(key.trim());if(res==null||res.trim().equals("null")){res="";}}else{System.out.println("props is null");}return res;}public static void synAMDatxxxxxxxxxxxxx() {String am_tablename="ACL_OPER";String am_table_prikey="ID";String kb_tablename=am_tablename;if(isTest){kb_tablename=am_tablename+"_XXX";//测试的情况留不用整2个数据源了，在一个库里测就行，表名区分开，跑通代码后这个标志改成false}SQL2UtilJdbc sql2util=null;//这个是本系统的数据源try {sql2util = new SQL2UtilJdbc();} catch (Exception e1) {e1.printStackTrace();}Connection  conb=null;//同步到本系统Statement  stmtb=null;try {///////////////////////////////查询存量数据/////////////////////conb=sql2util.getweblogicConnection();conb.setAutoCommit(false);//进制自动提交stmtb=conb.createStatement();for(int i=0;i<5;i++){String insert1="insert into ACL_OPER_XXX(\"ID\",\"OID\",\"NAME\",\"WORKID\",\"LOGINCODE\",\"LOGINPASS\",\"LOGINSTAT\",\"REGDATE\",\"MODDATE\",\"MOBILETEL\",\"HOMETEL\",\"OFFICETEL\",\"EMAIL\",\"GROUPID\",\"GROUPPOS\",\"NOTESADDR\",\"ISAVAILABLE\",\"CRMLEVEL\",\"PASSWORDUPDATETIME\",\"RECEIVENOTIFICATIONS\",\"DEPTTYPE\",\"DEPTCODE\",\"INSERTTIME\",\"INSERTOPER\",\"UPDATETIME\",\"UPDATEOPER\",\"ACLGRPID\",\"ERRORTIMES\") " +"values ("+i+",1,'林玉贤','agent2','10001           ','888999          ',0, '2010-08-20 18:05:26' , '2015-04-10 08:16:16' ,'null','null','null','[e-mail]              ','1   ','1   ','null',1,3,null ,0,'null','001'," +"null,'null',null,'null','1',0)   ";//加入批处理任务stmtb.addBatch(insert1);System.out.println(insert1);}int[] resArr=stmtb.executeBatch();conb.commit();stmtb.clearBatch();} catch (Exception e) {e.printStackTrace();;if(conb!=null){try{conb.rollback();conb.setAutoCommit(true);System.out.println("synAMData_Axxxxxxxxxx出现异常，事务回滚xxxxxxx");}catch(Exception ee1){ee1.printStackTrace();}}}finally{try{if(stmtb!=null){stmtb.close();}if(conb!=null){conb.close();}stmtb=null;conb=null;}catch(Exception ee2){ee2.printStackTrace();}}}public static void main(String[] ddd){//synAMDatxxxxxxxxxxxxx();//Map resultMap1=new HashMap();//resultMap1=synAMData_ACL_OPER();//String res1=(String)resultMap1.get("isFinishSuccess");//String printInfo1=(String)resultMap1.get("printInfo");//测试批处理oldDataMoveAndDeal1();}/** * 批处理的例子,这个做了一些优化，可以统计失败的记录。 *  * @author lushuaiyin * @param request * @param response * @return *  * 测试sql: * update A_LSY_TEST set BAK1='' where 1=1 * select * from A_LSY_TEST * update A_LSY_TEST set BAK1=to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss') where AID='lsy111111' */public static Map oldDataMoveAndDeal1() {//除了要记录结果标志外，还要记录统计信息，方便在页面显示.//(失败的时候就不记统计信息了，结果标志就有简短的错误信息，成功后才需要记录批处理的记过，因为批处理即使成功也会有个别数据和文件处理失败，要记录并显示)Map resultMap=new HashMap();String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "");SQL2UtilJdbc sql2util=null;//这个是本系统的数据源try {sql2util = new SQL2UtilJdbc();} catch (Exception e1) {e1.printStackTrace();isFinishSuccess="failed";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "");return resultMap;}Connection  cona=null;//查询存量数据Statement  stmta=null;ResultSet    rsa=null;Connection  conb=null;//处理存量数据Statement  stmtb=null;double allNum=0;//记录数量double failNum=0;//失败数量int batchNum=3;//每批次数量StringBuffer batchResult=new StringBuffer("处理任务：oldDataMoveAndDeal1 ,处理结果：\n"); //结果信息日志StringBuffer batchFailIDs=new StringBuffer(""); //失败的ID记录String select1=" select * from A_LSY_TEST ";try {///////////////////////////////查询存量数据/////////////////////conb=sql2util.getweblogicConnection();conb.setAutoCommit(false);//进制自动提交cona=sql2util.getweblogicConnection();stmta=cona.createStatement();rsa=stmta.executeQuery(select1);///////////////////////////////处理存量数据/////////////////////stmtb=conb.createStatement();int bat=0;//本批条数String[] idArrTemp=new String[batchNum];//本批次中的id记录int testnum=0;while(rsa.next()){testnum++;//update TB_AGENT_KDATA set OFFICEFILE='init(19800101000000000).doc' where ID=471String update1="";//处理存量逻辑String id=rsa.getString("AID");String BAK1=rsa.getString("BAK1");if(id==null||id.trim().equals("")){System.out.println("出现ID位空的情况，不作处理，继续下一条数据处理。。。");continue;}idArrTemp[bat]=id;update1="update A_LSY_TEST set BAK1=to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss') where AID='"+id+"'";//正确的sqlif(testnum==2 ||testnum==3){//测试失败，故意写错sql，测试批处理异常update1="update A_LSY_TEST set adate='嚯哈哈,这局sql肯定报错' where AID='"+id+"'";//失败的sql（自己弄一个，比如插入数据类型不一致）}//加入批处理任务stmtb.addBatch(update1);System.out.println("添加一个sql；【"+update1+"】");allNum++;bat++;//执行批处理(整批)if(bat==batchNum){int[] resArr=null;//记录失败的记录String faildIDs="";/////////try{/*executeBatch这里要说明下，结果数组里可以查看执行成功和失败，成功是1，失败是其他。我最初的设计就是用这个统计成功和失败的sql。 但是 我在实际测试中（db2数据库）发现无论成功还是失败 这个数组中记得都是1，也就是说，这个接口说明和实际的效果不一致。那么我就不能用这个返回结果了。那么获取这个批次里执行失败的sql，网上说用 getNextException，但是这个只能查看信息，不能知道具体哪个sql除了问题。为了统计精确的数字。我就在执行异常的时候，回滚事务。 这样的话，这个批次就全部失败了，那么我也就能统计出失败的具体sql了。 这是好处，坏处也有，那就是一个批次里哪些本来能执行成功的sql也会因为某一条失败的sql全部回滚，即殃及池鱼了。 */resArr=stmtb.executeBatch();System.out.println("处理一次结果标志==["+Arrays.toString(resArr)+"]");conb.commit();stmtb.clearBatch();}catch(SQLException ee1){//ee1.printStackTrace();System.out.println(ee1.getNextException());//记录失败的记录faildIDs=getFailIdsDirect(idArrTemp);System.out.println("处理一次失败id==["+faildIDs+"]");if(faildIDs!=null && !(faildIDs.trim().equals("")) ){failNum+=faildIDs.split(",").length;batchFailIDs.append(faildIDs+"\n");}System.out.println("Exception1 oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");bat=0;//清零idArrTemp=new String[batchNum];//id记录清空if(conb!=null){//本批次回滚,如果不回滚，成功的和失败的sql你就没法区别出来，如果你不在意哪些sql失败了，那么就把下面几行注掉。try{conb.rollback();System.out.println("出现异常，事务回滚。。。");}catch(Exception e2){e2.printStackTrace();}}//end 回滚continue;//继续下次循环}//////////////记录失败的记录//faildIDs=getFailIds(resArr,idArrTemp);//这是最初的设计，但是实际测试结果和接口说明不一致，就不用这个方法统计了////if(faildIDs!=null && !(faildIDs.trim().equals("")) ){//failNum+=faildIDs.split(",").length;//batchFailIDs.append(faildIDs+"\n");//}//System.out.println("oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");bat=0;//清零idArrTemp=new String[batchNum];//id记录清空}}//end while end while end while end while//执行批处理(最后一个不完整的批次)if(bat>0){int[] resArr=null;//记录失败的记录String faildIDs="";        /////////try{resArr=stmtb.executeBatch();System.out.println("处理一次结果标志==["+Arrays.toString(resArr)+"]");conb.commit();stmtb.clearBatch();}catch(Exception ee1){System.out.println(ee1.getMessage());//记录失败的记录faildIDs=getFailIdsDirect(idArrTemp);if(faildIDs!=null && !(faildIDs.trim().equals("")) ){failNum+=faildIDs.split(",").length;batchFailIDs.append(faildIDs+"\n");}System.out.println("Exception2 oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");bat=0;//清零idArrTemp=new String[batchNum];//id记录清空if(conb!=null){try{conb.rollback();System.out.println("出现异常，事务回滚。。。");}catch(Exception e2){e2.printStackTrace();}}//continue;//这是最后一次了}//////////////记录失败的记录//faildIDs=getFailIdsDirect(idArrTemp);////if(faildIDs!=null && !(faildIDs.trim().equals("")) ){//failNum+=faildIDs.split(",").length;//batchFailIDs.append(faildIDs+"\n");//}//System.out.println("oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");bat=0;//清零idArrTemp=new String[batchNum];//id记录清空}isFinishSuccess="yes";} catch (Exception e) {System.out.println(e.getMessage());if(conb!=null){try{//这里的回滚其实没啥用了，但是留着也没错的conb.rollback();conb.setAutoCommit(true);System.out.println("出现异常，事务回滚。。。");isFinishSuccess="出现异常，事务回滚。。。";}catch(Exception ee1){ee1.printStackTrace();}}}finally{try{if(rsa!=null){rsa.close();}if(stmtb!=null){stmtb.close();}if(conb!=null){conb.close();}if(stmta!=null){stmta.close();}if(cona!=null){cona.close();}rsa=null;stmtb=null;conb=null;stmta=null;cona=null;}catch(Exception ee2){ee2.printStackTrace();}}batchResult.append(isFinishSuccess+"\n");batchResult.append("数据处理总条数：["+formatDouble(allNum)+"],失败总条数:["+formatDouble(failNum)+"]"+"\n");batchResult.append("失败数据的id记录：["+batchFailIDs+"]\n");System.out.println(batchResult.toString());resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", batchResult.toString());return resultMap;}//判断并获取失败的批处理idpublic static String getFailIds(int[] resArr,String[] idArrTemp){String fials="";if(resArr==null||idArrTemp==null||(idArrTemp.length)<resArr.length){System.out.println("getFailIds参数不合法。。。");return null;}for(int i=0;i<resArr.length;i++){if( 1!=resArr[i]){if(idArrTemp[i]!=null){fials+=idArrTemp[i]+",";}}}return fials;}//判断并获取失败的批处理idpublic static String getFailIdsDirect(String[] idArrTemp){String fials="";if(idArrTemp==null){System.out.println("getFailIdsDirect参数不合法。。。");return null;}for(int i=0;i<idArrTemp.length;i++){if(idArrTemp[i]!=null){fials+=idArrTemp[i]+",";}}return fials;}public static String formatDouble(double dd){String res="";res=String.valueOf(dd);res=res.substring(0,res.indexOf("."));return res;}public static void fff1343(){SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();ResultSet rs=null;try {String sSql = "select count(*) from ACL_OPER t  where t.logincode=? ";Object[] params=new Object[]{"xx7004"};rs = sQL2UtilJdbc.selectPrep(sSql, params);if (rs.next()) {String logggg=rs.getString("1");System.out.println("counttt==["+logggg+"]");}else{System.out.println("hreeeeee");}} catch (Exception e) {e.printStackTrace();}finally{sQL2UtilJdbc.freeconn(rs);sQL2UtilJdbc.freeconn();}}public static void fff111(){SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();Timestamp tt=new Timestamp(116, 1, 15, 16, 8, 1, 3);System.out.println(tt.toString());Date dd=new Date(tt.getTime());SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ");String ddstr=sdf.format(dd);System.out.println("---"+ddstr  );try {//String sSql = "UPDATE ACL_OPER_XXX  set REGDATE=TIMESTAMP('2016-10-10 12:11:11') where id="+16388;//String sSql = "UPDATE ACL_OPER_XXX  set REGDATE='2015-10-10 12:11:11' where id="+16388;String sSql = "UPDATE ACL_OPER_XXX  set REGDATE='"+ddstr+"' where id="+16388;sQL2UtilJdbc.othersql(sSql);} catch (Exception e) {e.printStackTrace();}finally{sQL2UtilJdbc.freeconn();}}public static void fff(){String sssss=getPropValue("old_dssssssssssssb2_url");System.out.println("rs=["+sssss+"]");String fff=getPropValue("old_db2_username");System.out.println("rs=["+fff+"]");SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();ResultSet rs=null;try {String sSql = "select * from ACL_OPER t  where t.logincode=? ";//String sSql = "select count(*)  from ACL_OPER t  where t.logincode <> ? ";Object[] params=new Object[]{"7004"};rs = sQL2UtilJdbc.selectPrep(sSql, params);if (rs.next()) {//String logggg=rs.getString("logincode");//String logggg=rs.getString("1");Timestamp tttt=rs.getTimestamp("REGDATE");//System.out.println("logggg==["+logggg+"]");System.out.println("tttt==["+tttt+"]");System.out.println("tttt==["+tttt.toString()+"]");}else{System.out.println("hreeeeee");}} catch (Exception e) {e.printStackTrace();}finally{sQL2UtilJdbc.freeconn(rs);sQL2UtilJdbc.freeconn();}}////////////////////////////////////////////////* * 时间戳的null不能是'' 或者是'null'，只能是null. * 如果有值那就必须是'2010-08-20 18:05:26' * 所以单引号也在这个方法内处理，不在sql拼装那边处理了 *  UPDATE ACL_OPER_XXX   SET        REGDATE = '2010-08-20 18:05:26',       INSERTTIME = null WHERE ID = 16388 */public static String dealStrForSql(String str){String res="null";if(str==null){return null;}else{return "'"+str+"'";}}public static String timestampToStr(Timestamp tt){String res="null";if(tt==null){return "null";}Date dd=new Date(tt.getTime());SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");res=sdf.format(dd);if(res==null||res.trim().equals("")){res="null";}else{res=" '"+res+"' ";}return res;}/* * 只有下面两种方式才是对的 *  UPDATE ACL_OPER_XXX SET  PASSWORDUPDATETIME = DATE('2012-11-11')        WHERE ID=16388       UPDATE ACL_OPER_XXX SET  PASSWORDUPDATETIME = null        WHERE ID=16388 */public static String DateToStrForDb2(Date dd){String res="null";//db2只认 null，不认 ''if(dd==null){return "null";}SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");res=sdf.format(dd);if(res==null||res.trim().equals("")){res="null";}else{res="DATE('"+res+"')";}return res;}public static String DateToStr(Date dd){String res="";if(dd==null){return "";}SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");res=sdf.format(dd);return res;}/* * 这个方法是比较两个库的表数据可数是否一样，不一样就说明柜员系统的数据有被删除的。 * 这个方法必须在数据同步之后才能调用，数据同步是指：把柜员系统里的数据遍历，查看本系统是否有，有就update，没有就insert。 * 这个逻辑无法保证本系统有但是柜员系统却没有的数据也能被删掉。所以才有这个方法。 * 本系统的数据只可能和柜员系统的数据一样多，或者更多。 * 这个方法就是处理多出来的数据，这些数据在柜员系统被删除了，那么本系统也要删除。 *  * 这个方法也是只针对有唯一主键的表（或者有字段可以确定唯一记录的表）. *  * 其他同步方法调用完成后还要判断是否需要调用本方法，为了减少这个判断，就统一决定都调用，是否要走这个批量的逻辑，在本方法内判断。 * 这样保证减少很多冗余代码。 */public static Map synAMDataDelete(Map param) {String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。Map resultMap=new HashMap();if(param==null){isFinishSuccess="param is null";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "synAMDataDelete 参数param空");return resultMap;}String am_tablename=(String)param.get("am_tablename");String am_table_prikey=(String)param.get("am_table_prikey");if(am_tablename==null||am_tablename.trim().equals("")||am_table_prikey==null||am_table_prikey.trim().equals("")){isFinishSuccess="param中变量null";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "param中变量有空值");return resultMap;}else{am_tablename=am_tablename.toUpperCase();am_table_prikey=am_table_prikey.toUpperCase();}String kb_tablename=am_tablename;if(isTest){kb_tablename=am_tablename+"_XXX";//测试,在一个库里测就行，表名区分开，跑通代码后这个标志改成false}//判断两个库中这张表的数据是否一致，不一致才需要走下面的逻辑String selectCount1=" select count(*) from "+kb_tablename;String selectCountAm=" select count(*) from "+am_tablename;int count1=getSqlCount(selectCount1);int countAm=getSqlCountAm(selectCountAm);if(count1==countAm){//无需走下面的删除逻辑(不可能小于，因为在走完同步后，本库数据量肯定>=柜员库)isFinishSuccess="yes";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "表"+am_tablename+"在两个库里数据量一致，无需再走删除垃圾数据逻辑。");return resultMap;}SQL2UtilJdbc sql2util=null;//这个是本系统的数据源try {sql2util = sql2util = new SQL2UtilJdbc();} catch (Exception e1) {e1.printStackTrace();isFinishSuccess="sql2util=null";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "");return resultMap;}Connection  con=null;//查询本系统Statement  stmt=null;ResultSet    rs=null;Connection  conb=null;//也是操作本系统，为了批量，再起一个链接Statement  stmtb=null;String select1=" select * from "+kb_tablename;try {con=sql2util.getweblogicConnection();stmt=con.createStatement();rs=stmt.executeQuery(select1);conb=sql2util.getweblogicConnection();conb.setAutoCommit(false);//进制自动提交stmtb=conb.createStatement();double allNum=0;//记录数量int batchNum=10;//每批次数量int bat=0;//本批条数//遍历知识库系统数据，每条去柜员系统查看有没有，如果柜员系统没有，那么说明这是一条垃圾数据，要删掉。while(rs.next()){//处理存量逻辑String id=rs.getString(am_table_prikey);if(id==null||id.trim().equals("")){continue;}String select2=" select count(*) from "+am_tablename+" where "+am_table_prikey+"="+id;//去柜员系统查看有没有这条int ishaveAm=getSqlCountAm(select2);if(ishaveAm==0){//没有就说明是垃圾数据，本系统也要删除String delete1=" DELETE FROM "+kb_tablename+" WHERE "+am_table_prikey+"="+id;//加入批处理任务stmtb.addBatch(delete1);allNum++;bat++;}else if(ishaveAm==-1){//sql执行失败 //这种情况一般不出现，出现的话，我就过，不做处理，结果也就是漏了这条数据，但是绝不能删System.out.println("synAMDataDelete查询柜员系统的一条数据sql执行失败["+select2+"],这条数据没有去删除，是否垃圾数据还需要确认。");}//执行批处理(整批)if(bat==batchNum){//执行批量int[] resArr=stmtb.executeBatch();conb.commit();stmtb.clearBatch();bat=0;//清零}}//end while end while end while end while//执行批处理(最后一个不完整的批次)if(bat>0){int[] resArr=stmtb.executeBatch();conb.commit();stmtb.clearBatch();bat=0;//清零}isFinishSuccess="yes";} catch (Exception e) {e.printStackTrace();if(conb!=null){try{conb.rollback();conb.setAutoCommit(true);System.out.println("出现异常，事务回滚。。。");isFinishSuccess="failed";}catch(Exception ee1){ee1.printStackTrace();}}}finally{try{if(rs!=null){rs.close();}if(stmtb!=null){stmtb.close();}if(conb!=null){conb.close();}if(stmt!=null){stmt.close();}if(con!=null){con.close();}rs=null;stmtb=null;conb=null;stmt=null;con=null;}catch(Exception ee2){ee2.printStackTrace();}}//System.out.println(batchResult.toString());resultMap.put("isFinishSuccess", isFinishSuccess);//resultMap.put("printInfo", batchResult.toString());return resultMap;}/* * ACL_OPERinsert into "ACL_OPER"("ID","OID","NAME","WORKID","LOGINCODE","LOGINPASS","LOGINSTAT","REGDATE","MODDATE","MOBILETEL","HOMETEL","OFFICETEL","EMAIL","GROUPID","GROUPPOS","NOTESADDR","ISAVAILABLE","CRMLEVEL","PASSWORDUPDATETIME","RECEIVENOTIFICATIONS","DEPTTYPE","DEPTCODE","INSERTTIME","INSERTOPER","UPDATETIME","UPDATEOPER","ACLGRPID","ERRORTIMES") values (24613,829,'杨恩德','a00007','10000           ','8888            ',null,'2011-04-25 15:17:22','2011-08-04 09:15:17',null,null,null,null,'1   ','1   ',null,1,-1,null,null,null,'00005',null,null,null,null,'1',0); *//* * ACL_ROLEinsert into "ACL_ROLE"("ID","EXCODE","NAME","RGID","ISAVAILABLE","HQCARDFLAG") values (39980,'000001  ','通用知识库','ims',1,null); *//* * ACL_ORGinsert into "ACL_ORG"("ID","FID","ALGCODE","NAME","ADDRESS","OFLAG","EXCODE","ISAVAILABLE","HQCARDFLAG") values (1,1,'00001     ','昆仑银行','昆仑银行','1 ','00001     ',1,1); *//* * ACL_AG_GRPinsert into "ACL_AG_GRP"("ID","NAME","HQCARDFLAG") values ('10  ','企业业务咨询组          ',1); *//* * ACL_O_R_MAPinsert into "ACL_O_R_MAP"("ID","USERID","RID") values (337099,24007,13001); *//** *  * 这个方法实现两个库之间的某张表倒数。这个只针对有单个主键的表，对多主键的关联表不使用。(就是能根据一个字段就唯一确定这条记录的表) * 就是把某一张表的数据库A中的数据同步到数据库B中（这个方法还不完全，只能同步新增和修改的数据，还要专门增加一个方法，实现同步删除的数据） *  *  * 逻辑：查询数据库A的数据，遍历结果集，去查数据库B的数据，查到就更新，查不到就插入。 * （数据库A中有，但是数据库B中没有的数据，是因为A中被删除了，这个需要另外的方法实现，这里不实现） *  * 初期测试还在同一个库，表名用_XXX区分。因为没有2个好的测试库，所以先这么测，ACL_OPER和ACL_OPER_XXX认为是不同的两个库里的同一张表。 *  * 因为在不同的两个数据源中，所以update和insert只能是解析字段值，然后拼装，不能用delete加insert into select方式做了。这样就导致每张表都要 * 单独写个方法去解析sql字段。 *  * @param request * @param response * @param param * @return */public static Map synAMData_ACL_OPER() {//除了要记录结果标志外，还要记录统计信息，方便在页面显示.//(失败的时候就不记统计信息了，结果标志就有简短的错误信息，成功后才需要记录批处理的记过，因为批处理即使成功也会有个别数据和文件处理失败，要记录并显示)Map resultMap=new HashMap();String am_tablename="ACL_OPER";String am_table_prikey="ID";String kb_tablename=am_tablename;if(isTest){kb_tablename=am_tablename+"_XXX";//测试的情况留不用整2个数据源了，在一个库里测就行，表名区分开，跑通代码后这个标志改成false}String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();//这个是柜员系统的数据源，使用jdbc方式连接SQL2UtilJdbc sql2util=null;//这个是本系统的数据源try {sql2util = new SQL2UtilJdbc();} catch (Exception e1) {e1.printStackTrace();isFinishSuccess="failed";resultMap.put("isFinishSuccess", isFinishSuccess);resultMap.put("printInfo", "");return resultMap;}Connection  am_con=null;//查询柜员系统Statement  am_stmt=null;ResultSet    am_rs=null;Connection  conb=null;//同步到本系统Statement  stmtb=null;String select1=" select * from "+am_tablename;try {am_con=sQL2UtilJdbc.getweblogicConnection();am_stmt=am_con.createStatement();am_rs=am_stmt.executeQuery(select1);///////////////////////////////查询存量数据/////////////////////conb=sql2util.getweblogicConnection();conb.setAutoCommit(false);//进制自动提交stmtb=conb.createStatement();double allNum=0;//记录数量int batchNum=1;//每批次数量int bat=0;//本批条数//遍历柜员系统数据while(am_rs.next()){//处理存量逻辑String id=am_rs.getString(am_table_prikey.toUpperCase());if(id==null||id.trim().equals("")){continue;}String select2=" select count(*) from "+kb_tablename+" where id="+id;int ishave=getSqlCount(select2);if(ishave>0){//有就更新数据/* UPDATE AGENT.ACL_OPER SET ID = ID , OID = OID, NAME = 'NAME', WORKID = 'WORKID', LOGINCODE = 'LOGINCODE', LOGINPASS = 'LOGINPASS', LOGINSTAT = LOGINSTAT, REGDATE = 'REGDATE', MODDATE = 'MODDATE', MOBILETEL = 'MOBILETEL', HOMETEL = 'HOMETEL', OFFICETEL = 'OFFICETEL', EMAIL = 'EMAIL', GROUPID = 'GROUPID', GROUPPOS = 'GROUPPOS', NOTESADDR = 'NOTESADDR', ISAVAILABLE = ISAVAILABLE, CRMLEVEL = CRMLEVEL, PASSWORDUPDATETIME = PASSWORDUPDATETIME, RECEIVENOTIFICATIONS = RECEIVENOTIFICATIONS, DEPTTYPE = 'DEPTTYPE', DEPTCODE = 'DEPTCODE', INSERTTIME = 'INSERTTIME', INSERTOPER = 'INSERTOPER', UPDATETIME = 'UPDATETIME', UPDATEOPER = 'UPDATEOPER', ACLGRPID = 'ACLGRPID', ERRORTIMES = ERRORTIMES WHERE -- Please complete; */String update1=" UPDATE "+kb_tablename+" SET " +"  OID = " +am_rs.getInt("OID")+ //int" , NAME = '" +am_rs.getString("NAME")+ "' "+" , WORKID = '" +am_rs.getString("WORKID")+  "' "+" , LOGINCODE = '" +am_rs.getString("LOGINCODE")+  "' "+" , LOGINPASS = '" +am_rs.getString("LOGINPASS")+  "' "+" , LOGINSTAT = " +am_rs.getInt("LOGINSTAT")+ " , REGDATE = " +timestampToStr(am_rs.getTimestamp("REGDATE"))+" "+ //Timestamp" , MODDATE = " +timestampToStr(am_rs.getTimestamp("MODDATE"))+ " "+" , MOBILETEL = '" +am_rs.getString("MOBILETEL")+  "' "+" , HOMETEL = '" +am_rs.getString("HOMETEL")+  "' "+" , OFFICETEL = '" +am_rs.getString("OFFICETEL")+  "' "+" , EMAIL = '" +am_rs.getString("EMAIL")+  "' "+" , GROUPID = '" +am_rs.getString("GROUPID")+  "' "+" , GROUPPOS = '" +am_rs.getString("GROUPPOS")+  "' "+" , NOTESADDR = '" +am_rs.getString("NOTESADDR")+  "' "+" , ISAVAILABLE = " +am_rs.getInt("ISAVAILABLE")+ " , CRMLEVEL = " +am_rs.getInt("CRMLEVEL")+ " , PASSWORDUPDATETIME = " +DateToStrForDb2(am_rs.getDate("PASSWORDUPDATETIME"))+" "+ //Date,不许带单引号" , RECEIVENOTIFICATIONS = " +am_rs.getInt("RECEIVENOTIFICATIONS")+ " , DEPTTYPE = '" +am_rs.getString("DEPTTYPE")+  "' "+" , DEPTCODE = '" +am_rs.getString("DEPTCODE")+  "' "+" , INSERTTIME = " +timestampToStr(am_rs.getTimestamp("INSERTTIME"))+ " "+" , INSERTOPER = '" +am_rs.getString("INSERTOPER")+  "' "+" , UPDATETIME = " +timestampToStr(am_rs.getTimestamp("UPDATETIME"))+ " "+" , UPDATEOPER = '" +am_rs.getString("UPDATEOPER")+  "' "+" , ACLGRPID = '" +am_rs.getString("ACLGRPID")+  "' "+" , ERRORTIMES = " +am_rs.getInt("ERRORTIMES")+ " WHERE ID="+id;//加入批处理任务stmtb.addBatch(update1);System.out.println(update1);}else{//没有就插入(插入采用insert into select * from xx where id= 的方式，这样能实现方法的公用，不要去解析具体的字段)String insert1="insert into "+kb_tablename+"(\"ID\",\"OID\",\"NAME\",\"WORKID\",\"LOGINCODE\",\"LOGINPASS\"" +",\"LOGINSTAT\",\"REGDATE\",\"MODDATE\",\"MOBILETEL\",\"HOMETEL\",\"OFFICETEL\",\"EMAIL\",\"GROUPID\"" +",\"GROUPPOS\",\"NOTESADDR\",\"ISAVAILABLE\",\"CRMLEVEL\",\"PASSWORDUPDATETIME\",\"RECEIVENOTIFICATIONS\"" +",\"DEPTTYPE\",\"DEPTCODE\",\"INSERTTIME\",\"INSERTOPER\",\"UPDATETIME\",\"UPDATEOPER\",\"ACLGRPID\",\"ERRORTIMES\")"+"values ("+id+","+am_rs.getInt("OID")+",'" +am_rs.getString("NAME")+ "'" +",'" +am_rs.getString("WORKID")+  "'" +",'" +am_rs.getString("LOGINCODE")+  "'" +",'" +am_rs.getString("LOGINPASS")+  "'" +","  +am_rs.getInt("LOGINSTAT")+"," +timestampToStr(am_rs.getTimestamp("REGDATE"))+"" +"," +timestampToStr(am_rs.getTimestamp("MODDATE"))+"" +",'" +am_rs.getString("MOBILETEL")+  "'" +",'" +am_rs.getString("HOMETEL")+  "'" +",'" +am_rs.getString("OFFICETEL")+  "'" +",'" +am_rs.getString("EMAIL")+  "'" +",'" +am_rs.getString("GROUPID")+  "'" +",'" +am_rs.getString("GROUPPOS")+  "'" +",'" +am_rs.getString("NOTESADDR")+  "'" +    ","+am_rs.getInt("ISAVAILABLE")+"" +    ","+am_rs.getInt("CRMLEVEL")+"" +    "," +DateToStrForDb2(am_rs.getDate("PASSWORDUPDATETIME"))+" " +    ","+am_rs.getInt("RECEIVENOTIFICATIONS")+ "" +    ",'" +am_rs.getString("DEPTTYPE")+  "'" +    ",'" +am_rs.getString("DEPTCODE")+  "'" +    "," +timestampToStr(am_rs.getTimestamp("INSERTTIME"))+ "" +    ",'" +am_rs.getString("INSERTOPER")+  "'," +timestampToStr(am_rs.getTimestamp("UPDATETIME"))+ "" +    ",'" +am_rs.getString("UPDATEOPER")+  "'" +    ",'" +am_rs.getString("ACLGRPID")+  "',"+am_rs.getInt("ERRORTIMES")+ ")   ";//注意insert的sql在最后不可以有分号！否则会报错，但是在toad里可以成功执行，发现这个错耗费了我一下午，别再犯了。//加入批处理任务stmtb.addBatch(insert1);System.out.println(insert1);}allNum++;bat++;//执行批处理(整批)if(bat==batchNum){//执行批量int[] resArr=stmtb.executeBatch();conb.commit();//stmtb.clearBatch();bat=0;//清零}}//end while end while end while end while//执行批处理(最后一个不完整的批次)if(bat>0){int[] resArr=stmtb.executeBatch();conb.commit();stmtb.clearBatch();bat=0;//清零}isFinishSuccess="yes";} catch (Exception e) {e.printStackTrace();;if(conb!=null){try{conb.rollback();conb.setAutoCommit(true);System.out.println("synAMData_ACL_OPER出现异常，事务回滚xxxxxxx");isFinishSuccess="failed";}catch(Exception ee1){ee1.printStackTrace();}}}finally{try{if(am_rs!=null){am_rs.close();}if(stmtb!=null){stmtb.close();}if(conb!=null){conb.close();}if(am_stmt!=null){am_stmt.close();}if(am_con!=null){am_con.close();}am_rs=null;stmtb=null;conb=null;am_stmt=null;am_con=null;}catch(Exception ee2){ee2.printStackTrace();}}//System.out.println(batchResult.toString());//下面走删除本系统垃圾数据的判断,是否成功也是本方法的最后一步。//Map paramDel=new HashMap();//paramDel.put("am_tablename", am_tablename);//paramDel.put("am_table_prikey", am_table_prikey);//Map delResMap=synAMDataDelete(paramDel);//String resD=(String)delResMap.get("isFinishSuccess");//String printInfoD=(String)delResMap.get("printInfo");//if(resD!=null&&resD.equals("yes")){//isFinishSuccess="yes";//}else{//isFinishSuccess=resD;//resultMap.put("printInfo", printInfoD);//}resultMap.put("isFinishSuccess", isFinishSuccess);return resultMap;}/* * 必须是count查询语句，否则报错 * 并且不能有别名，只能是select count(*) from 。。。。。 */public static int getSqlCount(String sql){int res=0;if(sql==null||sql.trim().equals("")){res=-1;//查询结果集肯定>=0，这里是为了告诉sql有问题return res;}SQL2UtilJdbc sql2util=null;//这个是本系统的数据源try {sql2util = new SQL2UtilJdbc();} catch (Exception e1) {e1.printStackTrace();}Connection  cona=null;Statement  stmta=null;ResultSet    rsa=null;try {cona=sql2util.getweblogicConnection();stmta=cona.createStatement();rsa=stmta.executeQuery(sql);if(rsa.next()){String count=rsa.getString("1");res=Integer.valueOf(count);}} catch (Exception e) {System.out.println(e.getMessage());}finally{try{if(rsa!=null){rsa.close();}if(stmta!=null){stmta.close();}if(cona!=null){cona.close();}rsa=null;stmta=null;cona=null;}catch(Exception ee2){ee2.printStackTrace();}}return res;}/* * 必须是count查询语句，否则报错 * 并且不能有别名，只能是select count(*) from 。。。。。 */public static int getSqlCountAm(String sql){int res=0;if(sql==null||sql.trim().equals("")){res=-1;//查询结果集肯定>=0，这里是为了告诉sql有问题return res;}SQL2UtilJdbc sql2util = new SQL2UtilJdbc();//这个是柜员系统的数据源，使用jdbc方式连接Connection  cona=null;Statement  stmta=null;ResultSet    rsa=null;try {cona=sql2util.getweblogicConnection();stmta=cona.createStatement();rsa=stmta.executeQuery(sql);if(rsa.next()){String count=rsa.getString("1");res=Integer.valueOf(count);}} catch (Exception e) {System.out.println(e.getMessage());}finally{try{if(rsa!=null){rsa.close();}if(stmta!=null){stmta.close();}if(cona!=null){cona.close();}rsa=null;stmta=null;cona=null;}catch(Exception ee2){ee2.printStackTrace();}}return res;}}