使用Java编写控制JDBC连接、执行及关闭的工具类
程序员文章站
2024-03-09 16:52:53
简单的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(); } }
上一篇: PHP中phar包的使用教程
下一篇: 全面解析PHP面向对象的三大特征