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