應用Java編寫掌握JDBC銜接、履行及封閉的對象類

應用Java編寫掌握JDBC銜接、履行及封閉的對象類。本站提示廣大學習愛好者：（應用Java編寫掌握JDBC銜接、履行及封閉的對象類）文章只能為提供參考，不一定能成為您想要的結果。以下是應用Java編寫掌握JDBC銜接、履行及封閉的對象類正文

簡略的Java數據庫銜接和封閉對象類
 
寫JDBC運用的人經常為封閉資本而頭痛不已，這些代碼死板無味，若何能力用簡略的代碼停止封閉呢，上面我寫了一個辦法，可以消除你的苦楚：

 
  /** 
   * 封閉一切可封閉資本 
   * 
   * @param objs 可封閉的資本對象有Connection、Statement、ResultSet，其余類型資本主動疏忽 
   */ 
  public static void closeAll(Object... objs) { 
    for (Object obj : objs) { 
      if (obj instanceof Connection) close((Connection) obj); 
      if (obj instanceof Statement) close((Statement) obj); 
      if (obj instanceof ResultSet) close((ResultSet) obj); 
    } 
  }
 

這個辦法，帶了“...”參數，這個現實上是Java5中的可變參數辦法。可以豈論次序，豈論個數，挪用時刻直接封閉想要封閉的資本對象就ok了。例如：
 

catch (SQLException e) { 
      e.printStackTrace(); 
    } finally { 
      DBTools.closeAll(stmt, pstmt1, pstmt2, conn); 
    }

 
上面給出這個類完全的寫法：

package com.lavasoft.ibatistools.common; 

import com.lavasoft.ibatistools.bean.Table; 
import com.lavasoft.ibatistools.metadata.DataSourceMetaData; 
import com.lavasoft.ibatistools.metadata.MySQLDataSourceMetaData; 

import java.io.IOException; 
import java.io.InputStream; 
import java.sql.*; 
import java.util.List; 
import java.util.Properties; 

/** 
* 簡略的Java數據庫銜接和封閉對象類 
* 
* @author leizhimin 11-12-20 下晝4:32 
*/ 
public class DBTools { 
  private static String driverClassName, url, user, password; 

  static { 
    init(); 
  } 

  private static void init() { 
    InputStream in = DBTools.class.getResourceAsStream("/com/lavasoft/ibatistools/jdbc.properties"); 
    Properties preps = new Properties(); 
    try { 
      preps.load(in); 
      driverClassName = preps.getProperty("jdbc.driver"); 
      url = preps.getProperty("jdbc.url"); 
      user = preps.getProperty("jdbc.username"); 
      password = preps.getProperty("jdbc.password"); 
    } catch (IOException e) { 
      e.printStackTrace(); 
    } 
  } 

  /** 
   * 創立一個JDBC銜接 
   * 
   * @return 一個JDBC銜接 
   */ 
  public static Connection makeConnection() { 
    Connection conn = null; 
    try { 
      Class.forName(driverClassName); 
      conn = DriverManager.getConnection(url, user, password); 
    } catch (ClassNotFoundException e) { 
      e.printStackTrace(); 
    } catch (SQLException e) { 
      e.printStackTrace(); 
    } 
    return conn; 
  } 

  public static void close(Connection conn) { 
    if (conn != null) 
      try { 
        conn.close(); 
      } catch (SQLException e) { 
        e.printStackTrace(); 
      } 
  } 

  public static void close(ResultSet rs) { 
    if (rs != null) 
      try { 
        rs.close(); 
      } catch (SQLException e) { 
        e.printStackTrace(); 
      } 
  } 

  public static void close(Statement stmt) { 
    if (stmt != null) 
      try { 
        stmt.close(); 
      } catch (SQLException e) { 
        e.printStackTrace(); 
      } 
  } 

  /** 
   * 封閉一切可封閉資本 
   * 
   * @param objs 可封閉的資本對象有Connection、Statement、ResultSet，其余類型資本主動疏忽 
   */ 
  public static void closeAll(Object... objs) { 
    for (Object obj : objs) { 
      if (obj instanceof Connection) close((Connection) obj); 
      if (obj instanceof Statement) close((Statement) obj); 
      if (obj instanceof ResultSet) close((ResultSet) obj); 
    } 
  } 

  public static void main(String[] args) { 
    DataSourceMetaData dbmd = MySQLDataSourceMetaData.instatnce(); 
    List<Table> tableList = dbmd.getAllTableMetaData(DBTools.makeConnection()); 
    for (Table table : tableList) { 
      System.out.println(table); 
    } 
  } 
}

 
由於是在寫對象，銜接用到的次數很少，所以這裡采取jdbc形式創立，而沒有效到銜接池。封閉辦法用起來很爽，削減了代碼量，也進步了法式的靠得住性和質量。

一個簡略的JDBC通用對象
 
支撐多種數據庫，同一方法發生銜接，最優化、最簡略方法釋放資本。
 
迎接拍磚！
 

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(); 
  } 
}