簡單的Java數(shù)據(jù)庫連接和關(guān)閉工具類
寫JDBC應(yīng)用的人常常為關(guān)閉資源而頭痛不已，這些代碼枯燥無味，如何才能用簡單的代碼進(jìn)行關(guān)閉呢，下面我寫了一個(gè)方法，可以解除你的痛苦：
/** * 關(guān)閉所有可關(guān)閉資源 * * @param objs 可關(guān)閉的資源對象有Connection、Statement、ResultSet，別的類型資源自動(dòng)忽略 */ 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); } } 這個(gè)方法，帶了“...”參數(shù)，這個(gè)實(shí)際上是Java5中的可變參數(shù)方法。可以不論順序，不論個(gè)數(shù)，調(diào)用時(shí)候直接關(guān)閉想要關(guān)閉的資源對象就ok了。例如：
catch (SQLException e) { e.printStackTrace(); } finally { DBTools.closeAll(stmt, pstmt1, pstmt2, conn); }
下面給出這個(gè)類完整的寫法：
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)建一個(gè)JDBC連接 * * @return 一個(gè)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，別的類型資源自動(dòng)忽略 */ 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)槭窃趯懝ぞ撸B接用到的次數(shù)很少，所以這里采用jdbc模式創(chuàng)建，而沒有用到連接池。關(guān)閉方法用起來很爽，減少了代碼量，也提高了程序的可靠性和質(zhì)量。
一個(gè)簡單的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ū)動(dòng)發(fā)生錯(cuò)誤！"); e.printStackTrace(); } } /** * 創(chuàng)建一個(gè)數(shù)據(jù)庫連接 * * @param url 數(shù)據(jù)庫連接URL串 * @param properties 作為連接參數(shù)的任意字符串標(biāo)記/值對的列表；通常至少應(yīng)該包括 "user" 和 "password" 屬性 * @return 一個(gè)JDBC的數(shù)據(jù)庫連接 * @throws SQLException 獲取連接失敗時(shí)候拋出 */ 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; } /** * 在一個(gè)數(shù)據(jù)庫連接上執(zhí)行一個(gè)靜態(tài)SQL語句查詢 * * @param conn 數(shù)據(jù)庫連接 * @param staticSql 靜態(tài)SQL語句字符串 * @return 返回查詢結(jié)果集ResultSet對象 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ 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語句出錯(cuò)，請檢查！/n" + staticSql); throw e; } return rs; } /** * 在一個(gè)數(shù)據(jù)庫連接上執(zhí)行一個(gè)靜態(tài)SQL語句 * * @param conn 數(shù)據(jù)庫連接 * @param staticSql 靜態(tài)SQL語句字符串 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ 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語句出錯(cuò)，請檢查！/n" + staticSql); throw e; } finally { close(stmt); } } /** * 在一個(gè)數(shù)據(jù)庫連接上執(zhí)行一批靜態(tài)SQL語句 * * @param conn 數(shù)據(jù)庫連接 * @param sqlList 靜態(tài)SQL語句字符串集合 * @throws SQLException 執(zhí)行異常時(shí)候拋出 */ 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語句出錯(cuò)，請檢查！"); throw e; } } /** * 獲取Oracle數(shù)據(jù)一個(gè)指定的Sequence下一個(gè)值 * * @param conn 數(shù)據(jù)庫連接 * @param seq_name Sequence名稱 * @return Sequence下一個(gè)值 */ 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值出錯(cuò)，請檢查！/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，別的類型資源自動(dòng)忽略 */ 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(); } }新聞熱點(diǎn)
疑難解答
圖片精選
