Simple Java database connection and closing tool class
People who write JDBC applications often have headaches about closing resources. These codes are boring. How can you close them with simple code? Below I have written a method to relieve your pain:
/** * Close all closed resources* * @param objs The closed resource objects are Connection, Statement, and ResultSet, and other types of resources are automatically ignored*/ 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); } } This method has the "..." parameter, which is actually a variable parameter method in Java 5. You can directly close the resource object you want to close when calling regardless of the order or number. For example:
catch (SQLException e) { e.printStackTrace(); } finally { DBTools.closeAll(stmt, pstmt1, pstmt2, conn); }
The complete writing method of this class is given below:
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; /** * Simple Java database connection and close tool class * * @author leizhimin 11-12-20 4:32 pm */ 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(); } } /** * Create a JDBC connection* * @return A JDBC connection*/ 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(); } } /** * Close all closed resources* * @param objs The closed resource objects are Connection, Statement, and ResultSet, and other types of resources are automatically ignored*/ 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); } } }
Because it is a writing tool, the connection is used very few times, so the jdbc mode is used here, and the connection pool is not used. The closing method is very good to use, reducing the amount of code and improving the reliability and quality of the program.
A simple JDBC universal tool
Supports multiple databases, generates connections in a unified way, and releases resources in the optimal and simplest way.
Welcome to take pictures!
import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import java.sql.*; import java.util.List; import java.util.Properties; /** * General database operation tool, providing database connection acquisition, SQL execution, resource closing and other functions. The supported databases are Oracle10g and 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("An error occurred loading the database driver!"); e.printStackTrace(); } } /** * Create a database connection * * @param url Database connection URL string * @param properties List of any string tag/value pairs for the connection parameters; usually at least "user" and "password" properties should be included * @return A JDBC database connection * @throws SQLException Throws when the connection fails */ public static Connection makesConnection(String url, Properties properties) throws SQLException { Connection conn = null; try { conn = DriverManager.getConnection(url, properties); } catch (SQLException e) { log.error("Exception occurred when obtaining the database connection", e); throw e; } return conn; } /** * Execute a static SQL statement query on a database connection* * @param conn Database connection* @param staticSql static SQL statement string* @return Return query result set ResultSet object* @throws SQLException Throws when executing exception*/ public static ResultSet executeQuery(Connection conn, String staticSql) throws SQLException { ResultSet rs = null; try { //Create an object that executes SQL Statement stmt = conn.createStatement(); //Execute SQL and get the return result rs = stmt.executeQuery(staticSql); } catch (SQLException e) { log.error("An error occurred when executing the SQL statement, please check! /n" + staticSql); throw e; } return rs; } /** * Execute a static SQL statement on a database connection* * @param conn Database connection* @param staticSql Static SQL statement string* @throws SQLException Throws when executing exception */ public static void executeSQL(Connection conn, String staticSql) throws SQLException { Statement stmt = null; try { //Create an object that executes SQL stmt = conn.createStatement(); //Execute SQL and get the result returned stmt.execute(staticSql); } catch (SQLException e) { log.error("An error occurred when executing SQL statements, please check! /n" + staticSql); throw e; } finally { close(stmt); } } /** * Execute a batch of static SQL statements on a database connection* * @param conn Database connection* @param sqlList Static SQL statement string collection* @throws SQLException Throws when executing exception*/ public static void executeBatchSQL(Connection conn, List<String> sqlList) throws SQLException { try { //Create an object that executes SQL Statement stmt = conn.createStatement(); for (String sql : sqlList) { stmt.addBatch(sql); } //Execute SQL and get the return result stmt.executeBatch(); } catch (SQLException e) { log.error("An error occurred when executing a batch SQL statement, please check!"); throw e; } } /** * Get Oracle data a specified Sequence next value* * @param conn Database connection* @param seq_name Sequence name* @return Sequence next value*/ public static long sequenceNextval(Connection conn, String seq_name) { long val = -1L; Statement stmt = null; ResultSet rs = null; try { //Create an object that executes SQL stmt = conn.createStatement(); //Execute SQL and get the return result rs = stmt.executeQuery("select " + seq_name + ".nextval from dual"); if (rs.next()) val = rs.getLong(1); } catch (SQLException e) { log.error("#ERROR#: There was an error in getting the Sequence value, please check! /n" + seq_name); e.printStackTrace(); throw new RuntimeException(e); } finally { close(rs); close(stmt); } return val; } /** * Close all closed JDBC resources, regardless of order, can always be executed in the correct order* * @param objs The closed resource objects are Connection, Statement, and ResultSet, and other types of resources are automatically ignored*/ 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("Exception occurred when closing the database connection!"); } } private static void close(ResultSet rs) { if (rs != null) try { rs.close(); } catch (SQLException e) { log.error("Exception occurred when closing the result set!"); } } private static void close(Statement stmt) { if (stmt != null) try { stmt.close(); } catch (SQLException e) { log.error("Exception occurred when closing the SQL statement!"); } } /** * Test code, useless* * @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" + "/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(); } }