在尝试使用资源 java.sql.SQLException 中返回结果集:在结果集关闭后不允许操作

Question

>我有一个名为CustomerController的文件，将从MyConnectToMySQL获取ResultSet返回，然后添加到ArrayList中

我正在切换到试用资源，因为我想确保它已关闭，并且我不想每次运行某些内容时都关闭(顺便说一句，有时它被错误并且没有关闭会使我的应用程序变慢(。

旧的工作正常。但是当我切换到尝试资源时，出现了错误开始

java.sql.SQLException: Operation not allowed after ResultSet closed

AFAIK，这是因为 Try-with-resources 在我返回 ResultSet 时关闭了连接，它无法访问。 所以我现在的解决方案是在我的客户控制器中建立连接，当我得到结果集时，我会在关闭之前将其添加到 ArrayList 中。

对这个问题有什么想法吗？我不想将其合并到客户控制器(我认为会起作用(，因为我想在未来与其他控制器重用它。

public class CustomerController {
MyConnectToMySQL conn = null;
ResultSet rs = null;
ArrayList<nguoidungDTO> dsnd = null;

public CustomerController () {
if (conn == null) {
conn = new MyConnectUnit("localhost", "root", "", "thuvien");
}
}

public ArrayList<nguoidungDTO> docDSND() throws Exception {
dsnd = new ArrayList<nguoidungDTO>();
rs = conn.excuteQuery("SELECT * FROM nguoidung");
while (rs.next()) {
nguoidungDTO nguoidung = new nguoidungDTO();
nguoidung.setManv(rs.getString(1));
nguoidung.setMkhau(rs.getString(2));
nguoidung.setQuyen(rs.getString(3));
dsnd.add(nguoidung);
}
//Old, each time I have done used, I will close by hand
//conn.Close();
return dsnd;
}
}

MyConnectToMySQL

package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyConnectToMySQL {

String Host = "";
String UserName = "";
String Password = "";
String Database = "";

Connection connect = null;
Statement statement = null;
ResultSet rs = null;

public MyConnectToMySQL(String Host, String UserName, String Password, String Database) {
this.Host = Host;
this.UserName = UserName;
this.Password = Password;
this.Database = Database;
}

public void DriverTest() throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new Exception("MySQL Driver JDBC not found");
}
}
//old
protected Connection getConnection() throws Exception {
if (this.connect == null) {
DriverTest();
String url = "jdbc:mysql://" + this.Host + ":3306/" + this.Database
+ "?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8";
try {
this.connect = DriverManager.getConnection(url, this.UserName, this.Password);
} catch (SQLException e) {
throw new Exception("Can't coonect MySQL server " + e);
}
}
return this.connect;
}
//old
protected Statement getStatement() throws Exception {

if (this.statement == null ? true : this.statement.isClosed()) {

this.statement = this.getConnection().createStatement();
}
return this.statement;
}

//Old
/*public ResultSet excuteQuery(String query) throws Exception {
try {
this.rs = getStatement().executeQuery(query);
} catch (Exception e) {
throw new Exception("Error : " + e.getMessage());
}

return this.rs;
}*/

//Switching to Try-With-Resources
public ResultSet excuteQuery(String query) throws Exception  {
DriverTest();
String url = "jdbc:mysql://" + this.Host + ":3306/" + this.Database
+ "?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8";
ResultSet myResult = null;
try(Connection con = DriverManager.getConnection(url, this.UserName, this.Password);
Statement statement = con.createStatement();
) {
try (ResultSet res = statement.executeQuery(query)) {
myResult = res;

System.out.println(myResult);
return myResult;
}
} catch (SQLException e) {
e.printStackTrace();
}
//Must return here too
return myResult;
}

public void Close() throws Exception {
if (this.rs != null && !this.rs.isClosed()) {
this.rs.close();
}
if (this.statement != null && !this.statement.isClosed()) {
this.statement.closed();
}
if (this.connect != null && !this.connect.isClosed()) {
this.connect.closed();
}
}
}

Answer 1

当您使用试用资源时，一旦try块完成，资源就会关闭。之后，如果您尝试显式关闭它，则会出现错误。您正在执行：

this.rs.close();

如果调用此语句，这将导致问题。这是因为您使用try-with-resources创建了Connection、Statement和Resultset。

确保不调用该方法public void Close()或者最好从此方法中删除上述语句，以避免在出于其他目的使用/调用此方法时意外调用它。

我还可以在您的代码中看到以下行：

this.statement.closed();
this.connect.closed();

这将导致编译失败，因为java.sql.Connection和java.sql.Statement都没有closed()方法。可能，您想键入close().但是，请确保出于上述相同原因删除这些语句。