import?org.apache.commons.logging.Log;?
import?org.apache.commons.logging.LogFactory;?

import?java.sql.*;?
import?java.util.List;?
import?java.util.Properties;?

/**?
* 通用數據庫操作工具，提供數據庫連接獲取、SQL執行、資源關閉等功能，支持的數據庫為Oracle10g、MySQL5.x。</P>?
*?
* @author leizhimin 2012-03-05 11:22?
*/?
public?class?DBToolkit {?
????????private?static?Log log = LogFactory.getLog(DBToolkit.class);?

????????static?{?
????????????????try?{?
????????????????????????Class.forName("oracle.jdbc.driver.OracleDriver");?
????????????????????????Class.forName("com.mysql.jdbc.Driver");?
????????????????}?catch?(ClassNotFoundException e) {?
????????????????????????log.error("加載數據庫驅動發生錯誤！");?
????????????????????????e.printStackTrace();?
????????????????}?
????????}?

????????/**?
???????? * 創建一個數據庫連接?
???????? *?
???????? * @param url????????????????數據庫連接URL串?
???????? * @param properties 作為連接參數的任意字符串標記/值對的列表；通常至少應該包括 "user" 和 "password" 屬性?
???????? * @return 一個JDBC的數據庫連接?
???????? * @throws SQLException 獲取連接失敗時候拋出?
???????? */?
????????public?static?Connection makeConnection(String url, Properties properties)?throws?SQLException {?
????????????????Connection conn =?null;?
????????????????try?{?
????????????????????????conn = DriverManager.getConnection(url, properties);?
????????????????}?catch?(SQLException e) {?
????????????????????????log.error("獲取數據庫連接發生異常", e);?
????????????????????????throw?e;?
????????????????}?
????????????????return?conn;?
????????}?

????????/**?
???????? * 在一個數據庫連接上執行一個靜態SQL語句查詢?
???????? *?
???????? * @param conn????????????數據庫連接?
???????? * @param staticSql 靜態SQL語句字符串?
???????? * @return 返回查詢結果集ResultSet對象?
???????? * @throws SQLException 執行異常時候拋出?
???????? */?
????????public?static?ResultSet executeQuery(Connection conn, String staticSql)?throws?SQLException {?
????????????????ResultSet rs =?null;?
????????????????try?{?
????????????????????????//創建執行SQL的對象?
????????????????????????Statement stmt = conn.createStatement();?
????????????????????????//執行SQL，并獲取返回結果?
????????????????????????rs = stmt.executeQuery(staticSql);?
????????????????}?catch?(SQLException e) {?
????????????????????????log.error("執行SQL語句出錯，請檢查！\n"?+ staticSql);?
????????????????????????throw?e;?
????????????????}?
????????????????return?rs;?
????????}?

????????/**?
???????? * 在一個數據庫連接上執行一個靜態SQL語句?
???????? *?
???????? * @param conn????????????數據庫連接?
???????? * @param staticSql 靜態SQL語句字符串?
???????? * @throws SQLException 執行異常時候拋出?
???????? */?
????????public?static?void?executeSQL(Connection conn, String staticSql)?throws?SQLException {?
????????????????Statement stmt =?null;?
????????????????try?{?
????????????????????????//創建執行SQL的對象?
????????????????????????stmt = conn.createStatement();?
????????????????????????//執行SQL，并獲取返回結果?
????????????????????????stmt.execute(staticSql);?
????????????????}?catch?(SQLException e) {?
????????????????????????log.error("執行SQL語句出錯，請檢查！\n"?+ staticSql);?
????????????????????????throw?e;?
????????????????}?finally?{?
????????????????????????close(stmt);?
????????????????}?
????????}?

????????/**?
???????? * 在一個數據庫連接上執行一批靜態SQL語句?
???????? *?
???????? * @param conn????????數據庫連接?
???????? * @param sqlList 靜態SQL語句字符串集合?
???????? * @throws SQLException 執行異常時候拋出?
???????? */?
????????public?static?void?executeBatchSQL(Connection conn, List<String> sqlList)?throws?SQLException {?
????????????????try?{?
????????????????????????//創建執行SQL的對象?
????????????????????????Statement stmt = conn.createStatement();?
????????????????????????for?(String sql : sqlList) {?
????????????????????????????????stmt.addBatch(sql);?
????????????????????????}?
????????????????????????//執行SQL，并獲取返回結果?
????????????????????????stmt.executeBatch();?
????????????????}?catch?(SQLException e) {?
????????????????????????log.error("執行批量SQL語句出錯，請檢查！");?
????????????????????????throw?e;?
????????????????}?
????????}?

????????/**?
???????? * 獲取Oracle數據一個指定的Sequence下一個值?
???????? *?
???????? * @param conn???????? 數據庫連接?
???????? * @param seq_name Sequence名稱?
???????? * @return Sequence下一個值?
???????? */?
????????public?static?long?sequenceNextval(Connection conn, String seq_name) {?
????????????????long?val = -1L;?
????????????????Statement stmt =?null;?
????????????????ResultSet rs =?null;?
????????????????try?{?
????????????????????????//創建執行SQL的對象?
????????????????????????stmt = conn.createStatement();?
????????????????????????//執行SQL，并獲取返回結果?
????????????????????????rs = stmt.executeQuery("select "?+ seq_name +?".nextval from dual");?
????????????????????????if?(rs.next()) val = rs.getLong(1);?
????????????????}?catch?(SQLException e) {?
????????????????????????log.error("#ERROR# :獲取Sequence值出錯，請檢查！\n"?+ seq_name);?
????????????????????????e.printStackTrace();?
????????????????????????throw?new?RuntimeException(e);?
????????????????}?finally?{?
????????????????????????close(rs);?
????????????????????????close(stmt);?
????????????????}?
????????????????return?val;?
????????}?

????????/**?
???????? * 關閉所有可關閉的JDBC資源，不論先后順序，總能以正確的順序執行?
???????? *?
???????? * @param objs 可關閉的資源對象有Connection、Statement、ResultSet，別的類型資源自動忽略?
???????? */?
????????public?static?void?closeAll(Object... objs) {?
????????????????for?(Object obj : objs)?
????????????????????????if?(obj?instanceof?ResultSet) close((ResultSet) obj);?
????????????????for?(Object obj : objs)?
????????????????????????if?(obj?instanceof?Statement) close((Statement) obj);?
????????????????for?(Object obj : objs)?
????????????????????????if?(obj?instanceof?Connection) close((Connection) obj);?
????????}?

????????private?static?void?close(Connection conn) {?
????????????????if?(conn !=?null)?
????????????????????????try?{?
????????????????????????????????conn.close();?
????????????????????????}?catch?(SQLException e) {?
????????????????????????????????log.error("關閉數據庫連接發生異常！");?
????????????????????????}?
????????}?

????????private?static?void?close(ResultSet rs) {?
????????????????if?(rs !=?null)?
????????????????????????try?{?
????????????????????????????????rs.close();?
????????????????????????}?catch?(SQLException e) {?
????????????????????????????????log.error("關閉結果集發生異常！");?
????????????????????????}?
????????}?

????????private?static?void?close(Statement stmt) {?
????????????????if?(stmt !=?null)?
????????????????????????try?{?
????????????????????????????????stmt.close();?
????????????????????????}?catch?(SQLException e) {?
????????????????????????????????log.error("關閉SQL語句發生異常！");?
????????????????????????}?
????????}?

????????/**?
???????? * 測試代碼，沒用?
???????? *?
???????? * @param args?
???????? * @throws SQLException?
???????? */?
????????public?static?void?main(String[] args)?throws?SQLException {?
????????????????String tns =?"jdbc:oracle:thin:@\n"?+?
????????????????????????????????"(description= \n"?+?
????????????????????????????????"\t(ADDRESS_LIST =\n"?+?
????????????????????????????????"\t\t(address=(protocol=tcp)(host=10.87.30.44)(port=1521))\n"?+?
????????????????????????????????"\t\t(address=(protocol=tcp)(host=10.87.30.45)(port=1521))\n"?+?
????????????????????????????????"\t\t(address=(protocol=tcp)(host=10.87.30.46)(port=1521))\n"?+?
????????????????????????????????"\t\t(load_balance=yes)\n"?+?
????????????????????????????????"\t)\n"?+?
????????????????????????????????"\t(connect_data =\n"?+?
????????????????????????????????"\t\t(service_name=KFCS)\n"?+?
????????????????????????????????"\t\t(failover_mode =\n"?+?
????????????????????????????????"\t\t\t(type=session)\n"?+?
????????????????????????????????"\t\t\t(method=basic)\n"?+?
????????????????????????????????"\t\t\t(retries=5)\n"?+?
????????????????????????????????"\t\t\t(delay=15)\n"?+?
????????????????????????????????"\t\t)\n"?+?
????????????????????????????????"\t)\n"?+?
????????????????????????????????")";?
????????????????Properties p_ora =?new?Properties();?
????????????????p_ora.put("user",?"base");?
????????????????p_ora.put("password",?"1qaz!QAZ");?
????????????????p_ora.put("internal_logon",?"normal");?

????????????????Connection ora_conn = makeConnection(tns, p_ora);?
????????????????ResultSet rs1 = ora_conn.createStatement().executeQuery("select count(1) from base.cfg_static_data");?
????????????????rs1.next();?
????????????????System.out.println(rs1.getInt(1));?
????????????????rs1.close();?
????????????????ora_conn.close();?

????????????????Properties p_mysql =?new?Properties();?
????????????????p_mysql.put("user",?"root");?
????????????????p_mysql.put("password",?"leizm");?
????????????????String url =?"jdbc:mysql://localhost:3306/tdmc";?
????????????????Connection mysql_conn = makeConnection(url, p_mysql);?
????????????????ResultSet rs2 = mysql_conn.createStatement().executeQuery("select count(1) from cfg_code");?
????????????????rs2.next();?
????????????????System.out.println(rs2.getInt(1));?
????????????????rs2.close();?
????????????????mysql_conn.close();?
????????}?
}