Java 数据库的Util

 import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import com.paic.is.dispatch.TMPEntry;import javapasswordsdk.PSDKPassword;import javapasswordsdk.PSDKPasswordRequest;import javapasswordsdk.exceptions.PSDKException;public class DBUtil {public final static String SELECT = " select ";public final static String FROM = " from ";public final static String WHERE = " where ";public final static String ON = " on ";public final static String LEFT_JOIN = " left join ";public final static String RIGHT_JOIN = " right join ";public final static String INNER_JOIN = " inner join ";public final static String COLUMN_SEPARATOR = " , ";public final static String INSERT = " insert ";public final static String INTO = " into ";public final static String UPDATE = " update ";public final static String SET = " set ";public final static String LEFT_PARENTHESES = " ( ";public final static String RIGHT_PARENTHESES = " ) ";public final static String SINGLE_QUOTATION = "'";public final static String TO_DATE = "to_date";public final static String COMMA = " , ";public final static String VALUES = " values ";public final static String CONCATENATE_SING = "&";public final static String CONCATENATE_STRING = "||";public final static String CONCATENEATE_REPLACEMENT = "||'&'||";public static String packColumnName(String columnName){return " " + columnName + " ";}public static void executeSqlMapBatch(HashMap<String, ArrayList<String>> sqlMap){if(null == sqlMap || sqlMap.size() <=0){return;}Connection conn = DBConnection.getConnection();Statement stmt = null;try{conn.setAutoCommit(false);stmt = conn.createStatement();for(Iterator<String> iterator = sqlMap.keySet().iterator(); iterator.hasNext(); ){String symbol = iterator.next();ArrayList<String> sqlList = sqlMap.get(symbol);for(Iterator<String> sqlIterator = sqlList.iterator(); sqlIterator.hasNext();){String sql= sqlIterator.next();if(!StringUtil.IsEmpty(sql)){stmt.addBatch(sql);}}}stmt.executeBatch();conn.commit();}catch(SQLException ex){ExceptionDefaultHandler.handle(ex,TMPEntry.getCurrentTMPFileName() + ".log");try{conn.rollback();for(Iterator<String> iterator = sqlMap.keySet().iterator(); iterator.hasNext(); ){String symbol = iterator.next();ArrayList<String> sqlList = sqlMap.get(symbol);for(Iterator<String> sqlIterator = sqlList.iterator(); sqlIterator.hasNext();){String sql= sqlIterator.next();executeSingleSql(sql, symbol);}}}catch (SQLException sqlex){ExceptionDefaultHandler.handle(sqlex,TMPEntry.getCurrentTMPFileName() + ".log");}}finally{close(stmt);}}public static void executeSqlBatch(HashMap<String, String> sqlList){Connection conn = DBConnection.getConnection();Statement stmt = null;try{conn.setAutoCommit(false);stmt = conn.createStatement();for(Iterator<String> iterator = sqlList.keySet().iterator(); iterator.hasNext();){String keyStr = iterator.next();if(!StringUtil.IsEmpty(keyStr)){stmt.addBatch(sqlList.get(keyStr));}}stmt.executeBatch();conn.commit();}catch(SQLException ex){try{conn.rollback();for(Iterator<String> iterator = sqlList.keySet().iterator(); iterator.hasNext();){String keyStr = iterator.next();executeSingleSql(sqlList.get(keyStr), keyStr);}}catch (SQLException sqlex){ExceptionDefaultHandler.handle(sqlex,TMPEntry.getCurrentTMPFileName() + ".log");}}finally{close(stmt);}}public static void executeSingleSql(String sql, String keyName){if(StringUtil.IsEmpty(sql)){return;}Connection conn = DBConnection.getConnection();Statement stmt = null;try{conn.setAutoCommit(true);stmt = conn.createStatement();stmt.executeUpdate(sql);}catch(SQLException ex){//ExceptionDefaultHandler.handle(ex);ThreadLog.MakeLog("We can not execute (" +keyName+") " + sql, TMPEntry.getCurrentTMPFileName() + ".log");}finally{close(stmt);}}public static boolean existSql(String sql){if(StringUtil.IsEmpty(sql)){return false;}Connection conn = DBConnection.getConnection();Statement stmt = null;ResultSet RS = null;boolean existence = false;try{stmt = conn.createStatement();RS = stmt.executeQuery(sql);while(RS.next()){int count = RS.getInt("count");if(count > 0){existence = true;}}}catch(SQLException ex){ExceptionDefaultHandler.handle(ex);ThreadLog.MakeLog("We can not execute :" + sql, TMPEntry.getCurrentTMPFileName() + ".log");}finally{try{RS.close();}catch(Exception ex){ExceptionDefaultHandler.handle(ex);}close(stmt);}return existence;}public static boolean callSPWithParameter(String fullName, String logFileName){boolean OKFlag = false;if(StringUtil.IsEmpty(fullName)){ThreadLog.MakeLog("The name of SP is empty", logFileName);return OKFlag;}String SPName = "{ call "+fullName+" }";Connection conn = DBConnection.getConnection();if(null == conn){ThreadLog.MakeLog("We can not get the DB connection during executing the SP : " +SystemProperties.newInstance().getLineSeparator() + SPName, logFileName);return OKFlag;}CallableStatement proc = null;try{ThreadLog.MakeLog("Start to execute the SP: " + SPName, logFileName);proc = conn.prepareCall(SPName);proc.execute();OKFlag = true;}catch(SQLException ex){ThreadLog.MakeLog("Fail in executing the SP: " + SPName, logFileName);ExceptionDefaultHandler.handle(ex, logFileName);}catch(Exception ex){ThreadLog.MakeLog("Fail in executing the SP: " + SPName,logFileName);ExceptionDefaultHandler.handle(ex, logFileName);}finally{close(proc);}if(OKFlag){ThreadLog.MakeLog("Success in executing the SP: " + SPName,logFileName);}return OKFlag;}public static ReturnResult updateTimeStampByTableName(String tableName, long step, String logFileName){ReturnResult RR = new ReturnResult();Connection conn = DBConnection.getConnection();CallableStatement proc = null;if(null == conn){return RR;}String SPName = "smdb_publish.publish_update_timestamp";String SPFullName = constructSPFormalName(SPName, 5);try{proc = conn.prepareCall(SPFullName);proc.setString(1, tableName);proc.setLong(2, step);proc.setString(3, "java");proc.registerOutParameter(4, java.sql.Types.NUMERIC, 0);proc.registerOutParameter(5, java.sql.Types.NUMERIC, 0);proc.execute();RR.setFinalResult(proc.getLong(4));RR.setOKFlag( proc.getLong(5) == 1 ? true : false );}catch(SQLException ex){ExceptionDefaultHandler.handle(ex,logFileName);}catch(Exception ex){ExceptionDefaultHandler.handle(ex, logFileName);}finally{close(proc);}return RR;}//public static void updateTableTimeStamp(String tableName, long currentTimeStamp, String logFileName)//{//String hexStr = Long.toHexString(currentTimeStamp);//String sqlTMP = "update smdb_table_timestamp "+//"set time_stamp = hextoraw('"+hexStr+"') "+//"where table_name='"+tableName+"'"; //Connection conn = DBConnection.getConnection();//Statement stmt = null;//try//{//stmt = conn.createStatement();//stmt.executeUpdate(sqlTMP);//}//catch(SQLException ex)//{//ExceptionDefaultHandler.handle(ex);//ThreadLog.MakeLog("We can not execute : " + sqlTMP, //logFileName);//}//finally//{//close(stmt);//}//}public static long getTableTimeStamp(String tableName){String sqlTMP = "select to_char(to_number(rawtohex(time_stamp), 'xxxxxxxxxxxxxxxxxxxx')) "+" as time_stamp from smdb_table_timestamp "+"where table_name='"+tableName+"'";long currentTimeStamp = -1L;Connection conn = DBConnection.getConnection();ResultSet RS = null;Statement stmt = null;try{stmt = conn.createStatement();RS = stmt.executeQuery(sqlTMP);while(RS.next()){ currentTimeStamp = Long.parseLong(RS.getString("time_stamp"));}}catch(SQLException ex){ExceptionDefaultHandler.handle(ex,TMPEntry.getCurrentTMPFileName() + ".log");ThreadLog.MakeLog("We can not execute :" + sqlTMP, TMPEntry.getCurrentTMPFileName() + ".log");}catch(Exception ex){ExceptionDefaultHandler.handle(ex,TMPEntry.getCurrentTMPFileName() + ".log");}finally{try{RS.close();}catch(Exception ex){ExceptionDefaultHandler.handle(ex,TMPEntry.getCurrentTMPFileName() + ".log");}close(stmt);}return currentTimeStamp;}public static void close(){close(DBConnection.getConnection());}public static void close(Connection conn){close(conn, null);}public static void close(Statement stmt){close(null, stmt);}public static void close(Connection conn, Statement stmt){try{if(null != stmt){stmt.close();}if(null != conn){conn.close();}}catch(SQLException ex){ExceptionDefaultHandler.handle(ex,TMPEntry.getCurrentTMPFileName() + ".log");}}public static String getPassword(String credFilePath, String safeID, String folder, String userName){String password = null;String newLine = SystemProperties.newInstance().getLineSeparator();try{ThreadLog.MakeLog("We start getting the password for :" + newLine +"credFilePath : " + credFilePath + newLine +"safeID : " + safeID + newLine +"folder : " + folder + newLine +"userName : " + userName, "password.log");PSDKPasswordRequest passRequest = new PSDKPasswordRequest();PSDKPassword psdkPassword = null;passRequest.setCredFilePath(credFilePath);passRequest.setSafe(safeID);passRequest.setFolder(folder);passRequest.setObject(userName);psdkPassword = javapasswordsdk.PasswordSDK.getPassword(passRequest);password = psdkPassword.getContent();ThreadLog.MakeLog("We have got the password : " + password, "password.log");}catch(PSDKException ex){ThreadLog.MakeLog("We can not get the password ","password.log");ExceptionDefaultHandler.handle(ex, "password.log");}return password;}public static String constructSPFormalName(String SPName, int parameterCount){String questionMark = "";for(int i=0; i < parameterCount; i++){if(i == parameterCount -1){questionMark += "?";}else{questionMark += "?, ";}}return "{ call "+SPName+"("+questionMark+") }";}public static void main(String[] args) {ProjectConfig.initialize();updateTimeStampByTableName("gics_tbl_s", 500, "Test.log");DBUtil.close(); //updateTableTimeStamp("tab_tmp", 511L);//System.out.println(currentTimeStamp);}}