使用Java編寫控制JDBC連接、執(zhí)行及關(guān)閉的工具類

更新時間：2016年03月08日 08:48:13 作者：leizhimin

這篇文章主要介紹了如何使用Java來編寫控制JDBC連接、執(zhí)行及關(guān)閉的程序,包括一個針對各種數(shù)據(jù)庫通用的釋放資源的工具類的寫法,需要的朋友可以參考下

簡單的Java數(shù)據(jù)庫連接和關(guān)閉工具類

寫JDBC應(yīng)用的人常常為關(guān)閉資源而頭痛不已，這些代碼枯燥無味，如何才能用簡單的代碼進(jìn)行關(guān)閉呢，下面我寫了一個方法，可以解除你的痛苦：

 
  /** 
   * 關(guān)閉所有可關(guān)閉資源 
   * 
   * @param objs 可關(guān)閉的資源對象有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); 
    } 
  }

這個方法，帶了“...”參數(shù)，這個實(shí)際上是Java5中的可變參數(shù)方法?？梢圆徽擁樞?，不論個數(shù)，調(diào)用時候直接關(guān)閉想要關(guān)閉的資源對象就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數(shù)據(jù)庫連接和關(guān)閉工具類 
* 
* @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(); 
    } 
  } 

  /** 
   * 創(chuàng)建一個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(); 
      } 
  } 

  /** 
   * 關(guān)閉所有可關(guān)閉資源 
   * 
   * @param objs 可關(guān)閉的資源對象有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); 
    } 
  } 
}

因?yàn)槭窃趯懝ぞ?，連接用到的次數(shù)很少，所以這里采用jdbc模式創(chuàng)建，而沒有用到連接池。關(guān)閉方法用起來很爽，減少了代碼量，也提高了程序的可靠性和質(zhì)量。

一個簡單的JDBC通用工具

支持多種數(shù)據(jù)庫，統(tǒng)一方式產(chǎn)生連接，最優(yōu)化、最簡單方式釋放資源。

歡迎拍磚！

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

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

/** 
* 通用數(shù)據(jù)庫操作工具，提供數(shù)據(jù)庫連接獲取、SQL執(zhí)行、資源關(guān)閉等功能，支持的數(shù)據(jù)庫為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("加載數(shù)據(jù)庫驅(qū)動發(fā)生錯誤！"); 
      e.printStackTrace(); 
    } 
  } 

  /** 
   * 創(chuàng)建一個數(shù)據(jù)庫連接 
   * 
   * @param url    數(shù)據(jù)庫連接URL串 
   * @param properties 作為連接參數(shù)的任意字符串標(biāo)記/值對的列表；通常至少應(yīng)該包括 "user" 和 "password" 屬性 
   * @return 一個JDBC的數(shù)據(jù)庫連接 
   * @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("獲取數(shù)據(jù)庫連接發(fā)生異常", e); 
      throw e; 
    } 
    return conn; 
  } 

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

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

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

  /** 
   * 獲取Oracle數(shù)據(jù)一個指定的Sequence下一個值 
   * 
   * @param conn   數(shù)據(jù)庫連接 
   * @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 { 
      //創(chuàng)建執(zhí)行SQL的對象 
      stmt = conn.createStatement(); 
      //執(zhí)行SQL，并獲取返回結(jié)果 
      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; 
  } 

  /** 
   * 關(guān)閉所有可關(guān)閉的JDBC資源，不論先后順序，總能以正確的順序執(zhí)行 
   * 
   * @param objs 可關(guān)閉的資源對象有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("關(guān)閉數(shù)據(jù)庫連接發(fā)生異常！"); 
      } 
  } 

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

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

  /** 
   * 測試代碼，沒用 
   * 
   * @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(); 
  } 
}

您可能感興趣的文章: