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

问题描述

当我执行以下代码时，出现异常.我认为这是因为我正在准备与他相同的连接对象的新语句.我应该如何重写它以便我可以创建一个准备好的语句并开始使用 rs2?即使连接到同一个数据库，我是否必须创建一个新的连接对象?

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 对象的读取与另一个的读取交错，则每个对象都必须由不同的 Statement 对象生成.如果存在打开的对象，则 Statement 接口中的所有执行方法都会隐式关闭语句的当前 ResultSet 对象.

让事情变得更糟的是调用代码中的 rs.它也是从 statement 字段派生出来的，但它没有关闭.

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 属于同时打开的同一个 Statement 对象.

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

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