获取java.sql.SQLException：ResultSet关闭后不允许操作

When I execute the following code, I get an exception. I think it is because I'm preparing in new statement with he same connection object. How should I rewrite this so that I can create a prepared statement AND get to use rs2? Do I have to create a new connection object even if the connection is to the same DB?

当我执行以下代码时，我得到一个例外。我认为这是因为我正在准备新的声明与他相同的连接对象。我应该如何重写这个以便我可以创建一个准备好的语句并使用rs2？即使连接到同一个DB，我是否必须创建新的连接对象？

    try 
    {
        //Get some stuff
        String name = "";
        String sql = "SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;";
        ResultSet rs = statement.executeQuery(sql);
        if(rs.next())
        {
            name = rs.getString("name");
        }

        String sql2 = "SELECT `id` FROM  `profiles` WHERE `id` =" + profId + ";";
        ResultSet rs2 = statement.executeQuery(sql2);
        String updateSql = "INSERT INTO `blah`............"; 
        PreparedStatement pst = (PreparedStatement)connection.prepareStatement(updateSql);    

        while(rs2.next()) 
        { 
            int id = rs2.getInt("id");
            int stuff = getStuff(id);

            pst.setInt(1, stuff);
            pst.addBatch();

        }

        pst.executeBatch();

    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }

private int getStuff(int id)
{

    try
    {   

            String sql = "SELECT ......;";
            ResultSet rs = statement.executeQuery(sql);

            if(rs.next())
            {
                return rs.getInt("something");

            }
            return -1;
    }//code continues

2 个解决方案

#1

The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don't close it.

问题在于你在getStuff（）中获取数据的方式。每次访问getStuff（）时，都会获得一个新的ResultSet，但不要关闭它。

This violates the expectation of the Statement class (see here - http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html):

这违反了Statement类的期望（请参阅此处 - http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html）：

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

默认情况下，每个Statement对象只能同时打开一个ResultSet对象。因此，如果读取一个ResultSet对象与另一个ResultSet对象的读取交错，则每个ResultSet对象必须由不同的Statement对象生成。如果存在打开的对象，则Statement接口中的所有执行方法都会隐式关闭语句的当前ResultSet对象。

What makes things even worse is the rs from the calling code. It is also derived off-of the statement field but it is not closed.

让事情变得更糟的是来自调用代码的rs。它也是在语句字段之外派生的，但它没有关闭。

Bottom line: you have several ResultSet pertaining to the same Statement object concurrently opened.

结论：您有几个与同时开启的Statement对象相关的ResultSet。

#2

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

当生成它的Statement对象关闭，重新执行或用于从多个结果序列中检索下一个结果时，ResultSet对象将自动关闭。

I guess after while(rs2.next()) you are trying to access something from rs1. But it's already closed since you reexecuted statement to get rs2 from it. Since you didn't close it, I beleive it's used again below.

我想在while（rs2.next（））之后你试图从rs1访问一些东西。但它已经关闭，因为你重新执行语句以从中获取rs2。既然你没有关闭它，我相信它在下面再次使用。

#1