Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test","root","fendou");
stmt = conn.prepareStatement("select 1 from dual");
rs = stmt.executeQuery();
while(rs.next()){
System.out.println("OK");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
上面是一段很常见的jdbc代码.通常,我们都是在finally里释放资源,经常可以看到有人或者为了美观,或者为了省事,将rs.close(),stmt.close(),conn.close()放到同一个try,catch块中.事实上,这样子关闭是不够严谨是.如果rs.close()或者stmt.close()执行的时候抛出了异常,那么就不会执行conn.close(),也就没法正确关闭connection了.
为了保证connection可以正常关闭,我们稍微调整一下代码.如下:
finally{
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
//handle the exception
}
try{
if(stmt != null){
stmt.close();
}
}catch(SQLException e){
//handle the exception
}
try{
if(conn!= null){
conn.close();
}
}catch(SQLException e){
//handle the exception
}
这样即使出了异常,也可以保证代码向下执行.这种情况下,通常我们会在catch块中通常忽略掉异常,不去做处理,或者做简单的打印一下,但是不能将异常转化成运行时异常抛出。因为一旦在catch块中抛出异常,程序就无法向下执行了。
当然,最正确的写法应该是这样:
try {
if(rs != null){
rs.close();
}
}catch(SQLException e){
// do something
} finally{
try{
if(stmt != null){
stmt.close();
}
}catch(Exception e){
// do something
}finally{
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
// do something
}
}
}
}
这样的写法虽然正确,但是看起来十分的丑陋,于是采取了一个折中的办法.在方法上声明throws SQLException,方法体中这样关闭,代码相对简洁一些.
try {
if(rs != null){
rs.close();//(1)
}
} finally{
try{
if(stmt != null){
stmt.close();//(2)
}
}finally{
if(conn != null){
conn.close();//(3)
}
}
}
这时候我会有这样一个疑问,如果(1)(2)(3)处都抛出了异常,那么方法会抛出哪儿个异常.事实证明,最后产生是异常会被抛出.前面的两个异常会被丢弃掉.
一般我会定义两个方法来专门处理关闭,根据不能的需求,一个会抛出SQLException,一个会忽略掉异常(ignore).相对来说,这样的代码最简洁,省去了重复的try,catch.
// close(conn,stmt,rs)
// close(conn,stmt,null)
// close(conn,null,null)
public static void close(Connection conn, PreparedStatement stmt,
ResultSet rs) throws SQLException {
try {
if(rs != null){
rs.close();
}
} finally{
try{
if(stmt != null){
stmt.close();
}
}finally{
if(conn != null){
conn.close();
}
}
}
}
public static void closeQuietly(Connection conn, PreparedStatement stmt,
ResultSet rs){
try{
close(conn, stmt,rs);
}catch(SQLException e){
//quietly
}
}
很多人在关闭的时候喜欢这么写:
//略去stmt和rs
connection.close();
connection = null;
这里讨论下connection = null这句是否有必要.有人说connection =null有助于垃圾回收.
个人认为connection = null是没有必要的.
从原理上来说,connection对象的回收,与它的状态是否是isClosed没有关系,而是取决于这个对象是否被引用.换句话说,即使connection没有close,也是可以被回收的.close()方法的作用是通知数据库端及时的释放资源.
经过下面程序的验证,即使不写connection=null,connection也可以很好的被垃圾回收.没有看出connection=null对垃圾回收有什么积极的影响;
另外从打印结果来看,垃圾收集是并行的.
package me.qiu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CloseDemo {
public static void main(String[] args){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
for (int i = 0; i < 100; i++) {
jdbc();
}
}
private static void jdbc() {
MyConnection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int b = 0;
try {
conn = new MyConnection(DriverManager.getConnection("jdbc:mysql://127.0.0.1/test","root","fendou"));
stmt = conn.prepareStatement("select 1 from dual");
rs = stmt.executeQuery();
while(rs.next()){
b = rs.getInt(1);
System.out.println(b);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeQuietly(conn, stmt, rs);
}
}
// close(conn,stmt,rs)
// close(conn,stmt,null)
// close(conn,null,null)
public static void close(MyConnection conn, PreparedStatement stmt,
ResultSet rs) throws SQLException {
try {
if(rs != null){
rs.close();
rs = null;
}
} finally{
try{
if(stmt != null){
stmt.close();
stmt = null;
}
}finally{
if(conn != null){
conn.close();
//conn = null;
}
}
}
}
public static void closeQuietly(MyConnection conn, PreparedStatement stmt,
ResultSet rs){
try{
close(conn, stmt,rs);
}catch(SQLException e){
//quietly
}
}
}
class MyConnection{
Connection conn;
MyConnection(Connection conn){
this.conn = conn;
}
public PreparedStatement prepareStatement(String sql) throws SQLException {
return conn.prepareStatement(sql);
}
public void close() throws SQLException{
conn.close();
}
@Override
protected void finalize() throws Throwable {
System.out.println("gc ...");
}
}