SQL connection closed without prompt

php editor Yuzai brings you the latest Java Q&A: SQL connection is closed without prompting. During the development process, we often encounter the problem that the SQL connection is not closed without prompting, which may lead to resource leaks and performance problems. This article will answer this question in detail and provide solutions to help you better handle situations related to SQL connection closing. Let’s find out together!

Question content

When trying to run two sql queries one after the other (as shown in dbtest2), the system returns an error stating that the sql connection has been closed, even though it has not been prompted to do so.

So far I have tried moving the sqlconn connection to a different location, as well as opening and closing the connection individually by method. If I just call a sql query, the system works as expected. These are the classes:

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"));
        }
    }
}

Despite all my efforts, the error message I finally received was:

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

The desired result is that the output is just a list

Workaround

That's because you are using try resources. Please see https://www.php.cn/link/533a7de111ee3af214eee5e09e3fa1bc for more details.

This means when you put the connection in a try block. The connection is closed after the try block is executed:

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

Just try moving the connection outside the try block like this:

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

After executing all queries, don’t forget to close the connection.

Please note that another option is to open a new connection each time.

The above is the detailed content of SQL connection closed without prompt. For more information, please follow other related articles on the PHP Chinese website!