Java（九）数据库JDBC：2.Operation not allowed after ResultSet closed结果集关闭异常

---

一、报错原因

Operation not allowed after ResultSet closed结果集关闭异常
这是因为用到的con、stmt、pstmt、rs在这里关闭任意一个后，都不能rs.next()查询结果。

二、示例

1.报错的示例

/* 报错 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCUtil {
    private static final String JDBC_DRIVER_8UP = "com.mysql.cj.jdbc.Driver";
    private static final String DB_URL_8UP = "jdbc:mysql://localhost:3306/ordermanagement?useSSL=false&serverTimezone=UTC";
    private static final String user="root";
    private static final String password="1234";
       
    public static Connection getConnection() throws Exception {
    	Class.forName(JDBC_DRIVER_8UP);
        return DriverManager.getConnection(DB_URL_8UP, user, password);
    }
    
    public static void closeAll(Connection con,Statement stmt,PreparedStatement pstmt,ResultSet rs) throws Exception {
        if(con!=null) {
		con.close();
		}
		if(stmt!=null) {
			stmt.close();
		}
		if(pstmt!=null) {
			pstmt.close();
		}
		if(rs!=null) {
			rs.close();
		}
    }

	// 报错
	public ResultSet executeQuery(Connection con, String sql, Object[] param){
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstmt.setObject(i + 1, param[i]);
                }
            }
            rs = pstmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
            	// 这里用到的con、pstmt、rs在这里关闭任意一个后，都不能rs.next()
            	 JDBCUtil.closeAll(null ,null, null, rs);
            	// JDBCUtil.closeAll(null ,null, pstmt, null);
            	// JDBCUtil.closeAll(con ,null, null, null);
            } catch (Exception e) {    
                e.printStackTrace();
            }
        }
        return rs;
    }
	
	public static void main(String[] args) {
		JDBCUtil jdbcUtil = new JDBCUtil();
		ResultSet rs = null;
		Connection con = null;
		try {
			con = JDBCUtil.getConnection();
			System.out.println("connection success");
			String sql = "select * from	customer where account = ? and password = ?";
			Object[] param = new Object[2];
			param[0] = 110001;
			param[1] = "123456";
			rs = jdbcUtil.executeQuery(con, sql, param);
			while(rs.next()) {
				System.out.println(rs.getInt("account"));
				System.out.println(rs.getString("password"));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				JDBCUtil.closeAll(con, null, null, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

2.成功的示例

/* 成功 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCUtil {
    private static final String JDBC_DRIVER_8UP = "com.mysql.cj.jdbc.Driver";
    private static final String DB_URL_8UP = "jdbc:mysql://localhost:3306/ordermanagement?useSSL=false&serverTimezone=UTC";
    private static final String user="root";
    private static final String password="1234";
       
    public static Connection getConnection() throws Exception {
    	Class.forName(JDBC_DRIVER_8UP);
        return DriverManager.getConnection(DB_URL_8UP, user, password);
    }
    
    public static void closeAll(Connection con,Statement stmt,PreparedStatement pstmt,ResultSet rs) throws Exception {
        if(con!=null) {
		con.close();
		}
		if(stmt!=null) {
			stmt.close();
		}
		if(pstmt!=null) {
			pstmt.close();
		}
		if(rs!=null) {
			rs.close();
		}
    }

	// 报错
	public ResultSet executeQuery(Connection con, String sql, Object[] param){
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstmt.setObject(i + 1, param[i]);
                }
            }
            rs = pstmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
            	// 这里用到的con、pstmt、rs在这里关闭任意一个后，都不能rs.next()
            	// JDBCUtil.closeAll(null ,null, null, rs);
            	// JDBCUtil.closeAll(null ,null, pstmt, null);
            	// JDBCUtil.closeAll(con ,null, null, null);
            } catch (Exception e) {    
                e.printStackTrace();
            }
        }
        return rs;
    }
	
	public static void main(String[] args) {
		JDBCUtil jdbcUtil = new JDBCUtil();
		ResultSet rs = null;
		Connection con = null;
		try {
			con = JDBCUtil.getConnection();
			System.out.println("connection success");
			String sql = "select * from	customer where account = ? and password = ?";
			Object[] param = new Object[2];
			param[0] = 110001;
			param[1] = "123456";
			rs = jdbcUtil.executeQuery(con, sql, param);
			while(rs.next()) {
				System.out.println(rs.getInt("account"));
				System.out.println(rs.getString("password"));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				JDBCUtil.closeAll(con, null, null, rs);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
/*
connection success
110001
123456
*/

三、四个东西之间的关系

• 所有的Statement的查询对应的结果集是一个
• 当查询新的Statement时，以前的Statement的ResultSet会自动关闭，供新的Statement使用
• 虽然会自动关闭，即自动导致ResultSet对象无效。但只是ResultSet对象无效，ResultSet所占用的资源可能还没有释放，所以还是每次用完都关闭rs.close()。

JDBC,ResultSet对像多次使用后再关闭的问题

---

参考：
Operation not allowed after ResultSet closed 结果集关闭异常