获取java.sql.SQLException：ResultSet关闭后不允许操作 [英] Getting java.sql.SQLException: Operation not allowed after ResultSet closed

问题描述

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

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?

    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

推荐答案

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

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.

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

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

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

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.

更糟糕的是来自调用代码的 rs 。它也是从语句字段派生的，但它没有关闭。

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.

底线：你有几个 ResultSet 属于同一个语句对象同时打开。

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

这篇关于获取java.sql.SQLException：ResultSet关闭后不允许操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！