JDBC执行多查询获三个结果集报错：Closed Resultset: getInt 如何解决？

算力阿峰

2026-7-21

问题：执行多SQL查询并发送邮件时遭遇ResultSet关闭错误

我需要开发一个程序，执行三个不同的SQL查询，将查询结果和用户名以表格形式通过邮件发送。以下是我写的代码：

public class SendNotification {
    public static void main(String[] args) throws SQLException, AddressException, MessagingException {
        // Database connection details
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String user = "localuser";
        String password = "xxxxxx";
        //InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("config.properties");
        
        //User Details
        String user1 = "abUser";
        String user2 = "xyUser";
        String user3 = "cvUser";
        
        // SQL queries for each user
        String query1 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user1+"' AND outdate >= SYSDATE - 7";
        String query2 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user2+"' AND outdate >= SYSDATE - 7";
        String query3 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user3+"' AND outdate >= SYSDATE - 7";
        
        // Create a connection to the database
        Connection connection = DriverManager.getConnection(url, user, password);
        
        // Create a statement and execute each query
        Statement statement = connection.createStatement();
        ResultSet resultSet1 = statement.executeQuery(query1);
        ResultSet resultSet2 = statement.executeQuery(query2);
        ResultSet resultSet3 = statement.executeQuery(query3);
        
        // Get the results and store them in a table
        Object[][] data = {
            {"User ID", "Document Count"},
            {user1, resultSet1.getInt(1)},
            {user2, resultSet2.getInt(1)},
            {user3, resultSet3.getInt(1)}
        };
        
     // Send an email with the results in tabular format
        String from = "devtest@ugc.local";
        String to = "PBxyz@gmail.com";
        
        String host = "mail.test.vb.xcv";
        
        Properties props = new Properties();
        props.put("mail.smtp.host", host);
        props.put("mail.smtp.port", "25");
        props.put("mail.debug", "true");
        
        Session session = Session.getDefaultInstance(props);
        MimeMessage message = new MimeMessage(session);
        message.setFrom(new InternetAddress(from));
        message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
        message.setSubject("Checked-In Document Counts");
        
        StringBuilder table = new StringBuilder();
        for (Object[] row : data) {
            table.append("<tr><td>").append(row[0]).append("</td><td>").append(row[1]).append("</td></tr>");
        }
        message.setText("<html><body><table>" + table.toString() + "</table></body></html>", "utf-8", "html");
        Transport.send(message);
        
        // Close the statement, result sets, and connection
        resultSet1.close();
        resultSet2.close();
        resultSet3.close();
        statement.close();
        connection.close();
    }
}

每次运行都会报错：

Exception in thread "main" java.sql.SQLException: Closed Resultset: getInt
at oracle.jdbc.driver.GeneratedScrollableResultSet.getInt(GeneratedScrollableResultSet.java:237)
at com.ura.SendNotification.main(SendTINNotification.java:48)

请问该如何修改代码实现预期功能？

解决方案

问题根源

同一个Statement对象执行多次查询时，之前的ResultSet会被自动关闭——JDBC规范中，Statement默认只能同时维护一个打开的ResultSet。当你执行statement.executeQuery(query2)时，resultSet1就已经被关闭，后续调用resultSet1.getInt(1)自然会抛出"Closed Resultset"错误。另外，你还漏了一个关键步骤：ResultSet初始指向第一行数据之前，必须先调用next()才能获取数据。

修改方案（三种可选）

方案1：立即取出每个查询的结果值，再执行下一个查询

这种方式无需创建多个Statement，先把每个查询的结果存到变量里，再处理后续逻辑，同时用PreparedStatement避免SQL注入：

public class SendNotification {
    public static void main(String[] args) throws SQLException, AddressException, MessagingException {
        // 数据库连接信息
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String dbUser = "localuser";
        String dbPassword = "xxxxxx";
        
        // 用户信息
        String user1 = "abUser";
        String user2 = "xyUser";
        String user3 = "cvUser";
        
        // 预编译查询模板
        String queryTemplate = "SELECT COUNT(*) FROM dbtest WHERE AUTH = ? AND outdate >= SYSDATE - 7";
        
        int count1 = 0, count2 = 0, count3 = 0;
        
        // 使用try-with-resources自动关闭资源，无需手动调用close
        try (Connection connection = DriverManager.getConnection(url, dbUser, dbPassword);
             PreparedStatement stmt = connection.prepareStatement(queryTemplate)) {
            
            // 查询user1
            stmt.setString(1, user1);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) { // 必须先移动到结果行
                    count1 = rs.getInt(1);
                }
            }
            
            // 查询user2
            stmt.setString(1, user2);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    count2 = rs.getInt(1);
                }
            }
            
            // 查询user3
            stmt.setString(1, user3);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    count3 = rs.getInt(1);
                }
            }
            
            // 组装表格数据
            Object[][] data = {
                {"User ID", "Document Count"},
                {user1, count1},
                {user2, count2},
                {user3, count3}
            };
            
            // 发送邮件逻辑
            String from = "devtest@ugc.local";
            String to = "PBxyz@gmail.com";
            String host = "mail.test.vb.xcv";
            
            Properties props = new Properties();
            props.put("mail.smtp.host", host);
            props.put("mail.smtp.port", "25");
            props.put("mail.debug", "true");
            
            Session session = Session.getDefaultInstance(props);
            try (MimeMessage message = new MimeMessage(session)) {
                message.setFrom(new InternetAddress(from));
                message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
                message.setSubject("Checked-In Document Counts");
                
                StringBuilder table = new StringBuilder();
                table.append("<table border='1'>"); // 添加边框让表格更清晰
                for (Object[] row : data) {
                    table.append("<tr><td>").append(row[0]).append("</td><td>").append(row[1]).append("</td></tr>");
                }
                table.append("</table>");
                // 用setContent更适合HTML格式邮件
                message.setContent("<html><body>" + table.toString() + "</body></html>", "text/html; charset=utf-8");
                Transport.send(message);
            }
        }
    }
}

方案2：为每个查询创建独立的Statement

如果坚持使用Statement，可以为每个查询单独创建Statement对象，避免ResultSet互相干扰：

// 替换原有的Statement创建和查询部分
try (Connection connection = DriverManager.getConnection(url, dbUser, dbPassword);
     Statement stmt1 = connection.createStatement();
     Statement stmt2 = connection.createStatement();
     Statement stmt3 = connection.createStatement()) {
     
    ResultSet rs1 = stmt1.executeQuery(query1);
    if (rs1.next()) count1 = rs1.getInt(1);
    
    ResultSet rs2 = stmt2.executeQuery(query2);
    if (rs2.next()) count2 = rs2.getInt(1);
    
    ResultSet rs3 = stmt3.executeQuery(query3);
    if (rs3.next()) count3 = rs3.getInt(1);
    
    // 后续邮件逻辑...
}

方案3：一次查询获取所有用户数据（最优）

用IN子句一次性查询三个用户的结果，减少数据库交互次数，效率更高：

// 替换查询逻辑部分
String query = "SELECT AUTH, COUNT(*) FROM dbtest WHERE AUTH IN (?, ?, ?) AND outdate >= SYSDATE - 7 GROUP BY AUTH";

Map<String, Integer> userCountMap = new HashMap<>();

try (Connection connection = DriverManager.getConnection(url, dbUser, dbPassword);
     PreparedStatement stmt = connection.prepareStatement(query)) {
     
    stmt.setString(1, user1);
    stmt.setString(2, user2);
    stmt.setString(3, user3);
    
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            userCountMap.put(rs.getString("AUTH"), rs.getInt(2));
        }
    }
    
    // 组装数据（处理用户无结果的情况，默认显示0）
    Object[][] data = {
        {"User ID", "Document Count"},
        {user1, userCountMap.getOrDefault(user1, 0)},
        {user2, userCountMap.getOrDefault(user2, 0)},
        {user3, userCountMap.getOrDefault(user3, 0)}
    };
    
    // 后续邮件逻辑...
}

额外优化建议

强制使用PreparedStatement：避免直接拼接SQL引发的SQL注入风险，同时数据库可预编译SQL，提升查询效率。
try-with-resources语法：自动关闭Connection、Statement、ResultSet等资源，防止资源泄漏，代码更简洁。
优化HTML表格：添加边框或内联CSS样式，让邮件中的表格更易读。
处理空结果：如果用户没有符合条件的数据，默认显示0，避免邮件中出现null值。

内容的提问来源于stack exchange，提问作者Cris-123

JDBC执行多查询获三个结果集报错：Closed Resultset: getInt 如何解决？

解决方案 #

问题根源 #

修改方案（三种可选） #