连续执行JDBC查询时MySQL连接意外关闭问题求助

数据老陈

2026-7-5

连续执行SQL查询时连接被关闭的问题解决

问题描述

连续执行两个SQL查询（如dBtest2示例）时，系统返回“SQL连接已关闭”错误，但未主动触发关闭操作。单独执行单个查询正常，已尝试调整连接位置、为每个方法单独开闭连接，问题仍存在。

相关代码

MySqlConnect类

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

sqlQuery类

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();

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

测试类dBtest2

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

解决方案

问题根源

sqlSearch方法的try-with-resources语句中，Connection connection = sqlConn会在代码块执行完毕后自动关闭该连接实例。而MySqlConnect中的连接是静态单例，第一次查询后连接被关闭，第二次查询时调用已关闭的连接就会触发错误。

修复后的代码

修改MySqlConnect类

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 = getNewConnection();
            System.out.println("MySQL Db Connection is successful");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    // 创建新连接的工具方法
    private static Connection getNewConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, USER, PASSWORD);
    }

    public static Connection getMysqlConnection() throws SQLException {
        // 检查连接是否关闭或无效，自动重建连接
        if (mysqlConn == null || mysqlConn.isClosed()) {
            mysqlConn = getNewConnection();
            System.out.println("Reconnected to MySQL Db");
        }
        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();
            }
        }
    }
}

修改sqlQuery类

import java.sql.*;
import java.util.ArrayList;

public class sqlQuery {
    private int userID;
    private String password;
    private boolean activated;
    private String userType;

    public String sqlSearch(String tBName) {
        // 优化空值判断，避免空指针
        if (tBName == null || tBName.isEmpty()) {
            throw new IllegalArgumentException("Criteria cannot be empty");
        }

        String sqlQuery = "SELECT * FROM " + tBName + ";";
        ArrayList<String> resultList = new ArrayList<>();

        // 仅将PreparedStatement和ResultSet放入try-with-resources，避免关闭全局连接
        try (PreparedStatement ps = MySqlConnect.getMysqlConnection().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();
        }
        return String.join(", ", resultList);
    }
}

修改说明

MySqlConnect优化：新增连接有效性检查，确保每次获取的连接都是可用状态，避免因连接超时或意外关闭导致报错。
资源管理调整：不再将全局连接放入try-with-resources，仅管理PreparedStatement和ResultSet——这两个资源会自动关闭，不会影响底层的数据库连接。
逻辑优化：完善空值判断，避免潜在的空指针异常。

修改后，连续执行多次查询即可正常返回结果列表。

内容的提问来源于stack exchange，提问作者sparrowe

连续执行JDBC查询时MySQL连接意外关闭问题求助

问题描述 #

相关代码 #

MySqlConnect类 #

sqlQuery类 #

测试类dBtest2 #

报错信息 #

解决方案 #

问题根源 #

修复后的代码 #