SQL connection closes without prompted

Issue

When trying to run two SQL queries, one after another (as seen in dBtest2), the system returns an error saying that the SQL connection has closed even though it has not been prompted to do so.

So far, I have tried moving the sqlConn connection to different places as well as opening and closing the connection individually per method. If only one SQL query is called, the system works as intended. 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"));
        }
    }
}

The error message I end up getting despite all efforts is:

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 would be the output to be just the lists

Solution

That's because you're using the try with resources. See https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html for more detail.

This means that as you're putting the connection within the try-block. After the try block execution the connection is closed:

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

Just try to move the connection outside the try block as follows:

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

Don't forget to close the connection after all the queries are executed.

Note that another option will be to open a new connection each time.

Answered By - Joan