Java控制JDBC连接、执行、关闭 使用Java编写控制JDBC连接、执行及关闭的工具类
leizhimin 人气:0想了解使用Java编写控制JDBC连接、执行及关闭的工具类的相关内容吗,leizhimin在本文为您仔细讲解Java控制JDBC连接、执行、关闭的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:Java,JDBC,数据库,SQL,下面大家一起来学习吧。
简单的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(); } }
加载全部内容