mysql中jdbc正确的关闭顺序,04 jdbc中关闭连接的方式

之前在02jdbc web项目使用jdbc示例及不同版本调用jdbc的差异https://blog.csdn.net/sinat_41132860/article/details/84981142

中调用jdbc的方式是有问题的,当时的代码如下

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class JdbcTest01 {

public static void main(String[]args) throws Exception {

//注册驱动,以反射方式加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//获取连接Connection

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/study1?useSSL=false&serverTimezone=UTC", "root", "root");

///得到执行sequel语句的对象Statement

Statement stmt=conn.createStatement();

//执行sql语句，并返回结果

ResultSet rs=stmt.executeQuery("select * from employee");

处理结果

while(rs.next()) {

System.out.println(rs.getObject("employeenumber"));

System.out.println(rs.getObject("employeename"));

System.out.println(rs.getObject("departmentnumber"));

System.out.println(rs.getObject("salary"));

System.out.println(rs.getObject("hiredate"));

System.out.println("-================");

}

//关闭Connection

rs.close();

stmt.close();

conn.close();

}

}

如果在关闭连接之前代码抛出异常,那么显然关闭连接的代码不会再执行

解决此问题的两种方式

1为了保证一定能关闭连接,可以讲关闭连接的代码放到finally代码块中,如下:

package jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcTest01 {

public static void main(String[]args) {

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

//注册驱动,以反射方式加载驱动

try {

Class.forName("com.mysql.cj.jdbc.Driver");

//获取连接Connection

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/study1?useSSL=false&serverTimezone=UTC", "root", "root");

///得到执行sequel语句的对象Statement

stmt=conn.createStatement();

//执行sql语句，并返回结果

rs=stmt.executeQuery("select * from employee");

处理结果

while(rs.next()) {

System.out.println(rs.getObject("employeenumber"));

System.out.println(rs.getObject("employeename"));

System.out.println(rs.getObject("departmentnumber"));

System.out.println(rs.getObject("salary"));

System.out.println(rs.getObject("hiredate"));

System.out.println("-================");

}

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally {

//关闭Connection

try {

if (rs!=null) {

rs.close();

}

if (stmt!=null) {

stmt.close();

}

if (conn!=null) {

conn.close();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

说明

1 try语句块内部声明的变量,try语句块访问不到,所以为了保证finally语句块中能访问到rs,stmt,conn要在try语句块外部声明

2 关闭资源前检查资源是否为空,若不为空才需要关闭,如果直接关闭资源,当上面某一步出现异常而资源为null时,会出现空指针异常

2 jdk7和 JDBC4.1之后的正确关闭资源

Connection、Statement、ResultSet都继承了AutoCloseable接口，因此可以使用try-with-resources的方式关闭这些资源,即之前IO流时用的jdk新写法,不用显示关闭资源：

package jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcTest01 {

public static void main(String[]args) {

//注册驱动,以反射方式加载驱动

try {

Class.forName("com.mysql.cj.jdbc.Driver");

} catch (ClassNotFoundException e1) {

e1.printStackTrace();

}

try (

//获取连接Connection

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/study1?useSSL=false&serverTimezone=UTC", "root", "root");

///得到执行sequel语句的对象Statement

Statement stmt=conn.createStatement();

//执行sql语句，并返回结果

ResultSet rs=stmt.executeQuery("select * from employee");

//处理结果

){

while(rs.next()) {

System.out.println(rs.getObject("employeenumber"));

System.out.println(rs.getObject("employeename"));

System.out.println(rs.getObject("departmentnumber"));

System.out.println(rs.getObject("salary"));

System.out.println(rs.getObject("hiredate"));

System.out.println("-================");

}

} catch (SQLException e1) {

e1.printStackTrace();

}

}

}

说明:try后面小括号中最后一行代码的分号加不加都可以