SQL 连接关闭而不提示

php小编鱼仔为您带来最新的Java问答：SQL连接关闭而不提示。在开发过程中经常会遇到SQL连接未关闭而不提示的问题，这可能会导致资源泄露和性能问题。本文将为您详细解答这一问题，并提供解决方案，帮助您更好地处理SQL连接关闭的相关情况。让我们一起来了解吧！

问题内容

当尝试依次运行两个 sql 查询时（如 dbtest2 中所示），系统会返回一条错误，指出 sql 连接已关闭，即使尚未提示这样做。

到目前为止，我已尝试将 sqlconn 连接移动到不同的位置，以及按方法单独打开和关闭连接。如果只调用一个 sql 查询，系统就会按预期工作。这些是类：

import java.sql.*;

public class mysqlconnect {
    private static final string db_url = "jdbc:mysql://dbconnectionurl";
    private static final string user = "username";
    private static final string password = "pass";
    private static connection mysqlconn;

    static {
        try {
            class.forname("com.mysql.cj.jdbc.driver");
            mysqlconn = drivermanager.getconnection(db_url, user, password);
            system.out.println("mysql db connection is successful");
        } catch (classnotfoundexception | sqlexception e) {
            e.printstacktrace(); // handle classnotfoundexception and sqlexception
        }
    }

    public static connection getmysqlconnection() {
        return mysqlconn;
    }

    public static void closeconnection() {
        if (mysqlconn != null) {
            try {
                mysqlconn.close();
                system.out.println("mysql db connection is closed");
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
    }
}

import java.sql.*;
import java.util.arraylist;

public class sqlquery {
    private int userid;
    private string password;
    private boolean activated;
    private string usertype;
    
    connection sqlconn = mysqlconnect.getmysqlconnection(); // i want to be able to try accept this but idk how

    public string sqlsearch(string tbname){ // all
        string sqlquery = "";

        if(tbname == ""){
            throw new illegalargumentexception("criteria cannot be empty");
        }

        sqlquery = "select * from " + tbname + ";";
        
        if(sqlquery == ""){
            throw new illegalargumentexception("sql query not set, something gone wrong");
        }

        arraylist<string> resultlist = new arraylist<string>();

        try (connection connection = sqlconn;
            preparedstatement ps = connection.preparestatement(sqlquery);
            resultset rs = ps.executequery()) {
    
            resultsetmetadata metadata = rs.getmetadata();
            int columncount = metadata.getcolumncount();
        
            if (rs.next()) {
                for (int i = 1; i <= columncount; i++) {
                    string columnvalue = rs.getstring(i);
                    resultlist.add(columnvalue);
                }     
            } else {
                system.out.println("no match for " + tbname);
            }
        
        } catch (sqlexception e) {
            system.err.println("error executing sql query: " + e.getmessage());
            e.printstacktrace(); 
        }
        string result = string.join(", ", resultlist);
        return result;     
    }
}

public class dbtest2 {
    public class main {
        public static void main(string[] args) {    
            person person = new person(2, "password123", true, "student");

            system.out.println(person.sqlsearch("course"));
            system.out.println(person.sqlsearch("student"));
        }
    }
}

尽管付出了一切努力，我最终收到的错误消息是：

MySQL Db Connection is successful
CS101, CSC 101, Intro to Computer Science, 3
Error executing SQL query: No operations allowed after connection closed.
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:111)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:74)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73)
        at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1610)
        at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1524)
        at Person.sqlSearch(Person.java:245)
        at dBtest2$Main.main(dBtest2.java:8)
Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
        at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:104)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149)
        at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:756)
        at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:556)
        at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1539)
        ... 3 more

期望的结果是输出只是列表

解决方法

那是因为您正在使用尝试资源。请参阅 https://www.php.cn/link/533a7de111ee3af214eee5e09e3fa1bc 了解更多详情。

这意味着当您将连接放入 try 块中时。 try 块执行后连接关闭：

try (connection connection = sqlconn;
     preparedstatement ps = connection.preparestatement(sqlquery);
     resultset rs = ps.executequery()) {

只需尝试将连接移到 try 块之外，如下所示：

Connection connection = sqlConn;
try (PreparedStatement ps = connection.prepareStatement(sqlQuery);
     ResultSet rs = ps.executeQuery()) {

执行完所有查询后，不要忘记关闭连接。

请注意，另一个选项是每次打开一个新连接。

以上是SQL 连接关闭而不提示的详细内容。更多信息请关注PHP中文网其他相关文章！