Use Java to write utility classes that control the connection execution and closure of JDBC
- 2020-05-05 11:19:29
- OfStack
simple Java database connection and close utility class
People who write JDBC apps often have a headache shutting down resources. The code is so boring, how can you close it with simple code
/**
* Close all closed resources
*
* @param objs Closable resource objects are Connection , Statement , ResultSet , 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, with the "..." Parameter, this is actually the variable-parameter method in Java5. You can simply close the resource object you want to close, regardless of the order or number. For example:
catch (SQLException e) {
e.printStackTrace();
} finally {
DBTools.closeAll(stmt, pstmt1, pstmt2, conn);
}
Here's how to write the class in its entirety:
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 closing utility classes
*
* @author leizhimin 11-12-20 In the afternoon 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();
}
}
/**
* To create a JDBC The connection
*
* @return a JDBC The 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 Closable resource objects are Connection , Statement , ResultSet , 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 you are writing the tool and the connection is used sparingly, jdbc mode is used instead of connection pooling. The closing method is a great way to use, reducing the amount of code, and increasing the reliability and quality of the program.
A simple JDBC general utility
Support a variety of databases, unified way to generate connections, optimization, the simplest way to release resources.
Welcome to clap brick!
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 tools to provide database connection acquisition, SQL Execution, resource closure and other functions, support the database as 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(" Error loading database driver! ");
e.printStackTrace();
}
}
/**
* Create a database connection
*
* @param url Database connection URL string
* @param properties An arbitrary string token as a connection parameter / A list of value pairs; Usually at least "user" and "password" attribute
* @return a JDBC Database connection to
* @throws SQLException Thrown when the fetch connection fails
*/
public static Connection makeConnection(String url, Properties properties) throws SQLException {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, properties);
} catch (SQLException e) {
log.error(" An exception occurred to obtain a database connection ", e);
throw e;
}
return conn;
}
/**
* Execute a static on a database connection SQL Statement query
*
* @param conn Database connection
* @param staticSql static SQL Statement string
* @return Returns the query result set ResultSet object
* @throws SQLException Thrown when an exception is executed
*/
public static ResultSet executeQuery(Connection conn, String staticSql) throws SQLException {
ResultSet rs = null;
try {
// Please create SQL The object of
Statement stmt = conn.createStatement();
// perform SQL , and get the returned result
rs = stmt.executeQuery(staticSql);
} catch (SQLException e) {
log.error(" perform SQL Statement error, please check! \n" + staticSql);
throw e;
}
return rs;
}
/**
* Execute a static on a database connection SQL statements
*
* @param conn Database connection
* @param staticSql static SQL Statement string
* @throws SQLException Thrown when an exception is executed
*/
public static void executeSQL(Connection conn, String staticSql) throws SQLException {
Statement stmt = null;
try {
// Please create SQL The object of
stmt = conn.createStatement();
// perform SQL , and get the returned result
stmt.execute(staticSql);
} catch (SQLException e) {
log.error(" perform SQL Statement error, please check! \n" + staticSql);
throw e;
} finally {
close(stmt);
}
}
/**
* Perform a batch of static executions on a database connection SQL statements
*
* @param conn Database connection
* @param sqlList static SQL Statement string collection
* @throws SQLException Thrown when an exception is executed
*/
public static void executeBatchSQL(Connection conn, List<String> sqlList) throws SQLException {
try {
// Please create SQL The object of
Statement stmt = conn.createStatement();
for (String sql : sqlList) {
stmt.addBatch(sql);
}
// perform SQL , and get the returned result
stmt.executeBatch();
} catch (SQLException e) {
log.error(" Perform batch SQL Statement error, please check! ");
throw e;
}
}
/**
* To obtain Oracle Data a specified Sequence The next value
*
* @param conn Database connection
* @param seq_name Sequence The name of the
* @return Sequence The next value
*/
public static long sequenceNextval(Connection conn, String seq_name) {
long val = -1L;
Statement stmt = null;
ResultSet rs = null;
try {
// Please create SQL The object of
stmt = conn.createStatement();
// perform SQL , and get the returned result
rs = stmt.executeQuery("select " + seq_name + ".nextval from dual");
if (rs.next()) val = rs.getLong(1);
} catch (SQLException e) {
log.error("#ERROR# : To obtain Sequence Error value, please check! \n" + seq_name);
e.printStackTrace();
throw new RuntimeException(e);
} finally {
close(rs);
close(stmt);
}
return val;
}
/**
* Close all closable JDBC Resources, regardless of their order, can always be executed in the right order
*
* @param objs Closable resource objects are Connection , Statement , ResultSet , 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(" Closed database connection abnormal! ");
}
}
private static void close(ResultSet rs) {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
log.error(" Closed result set abnormal! ");
}
}
private static void close(Statement stmt) {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
log.error(" Shut down SQL Statement exception! ");
}
}
/**
* Test code, no use
*
* @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();
}
}