Operation not allowed after resultset closed ошибка

1. Java Error java.sql.SQLException: Operation not allowed after ResultSet closed
2. Fix Java Error java.sql.SQLException: Operation not allowed after ResultSet closed

This tutorial demonstrates the java.sql.SQLException: Operation not allowed after ResultSet closed error in Java.

Java Error java.sql.SQLException: Operation not allowed after ResultSet closed

The error Operation Not Allowed After Resultset Closed is an SQL exception when we try to access a closed result set. As the Java Doc mentions, whenever a statement object is closed, If its Resultset object exists, it will also be closed.

The problem with the error is that the Resultset instance will also save the underlying statement. So when the underlying statement is closed, the Resultset will also be closed, which throws the error.

Here’s a snippet of code that will throw the same error.

Code:

        ResultSet Result_Set; // class variable

        Statement Demo_Statement = null;
        try{
            Demo_Statement = DB_Connection.createStatement();
            Result_Set = Demo_Statement.getGeneratedKeys();
           }
        catch (Exception e){
            throw e;
           }
        finally{
        try{
            if (Demo_Statement != null)
                Demo_Statement.close();
            } catch (SQLException e){
            throw e;
           }
       }

        System.out.println(Result_Set.next());

The code above with a database connection will throw the following error.

java.sql.SQLException: Operation not allowed after ResultSet closed

Fix Java Error java.sql.SQLException: Operation not allowed after ResultSet closed

The problem in the code is that we cannot close the statement instance before we are done with Resultset. Once we are done with the Resultset, we can close both Resultset and Statement Instance.

As we can see, we are trying to print the Boolean from Result_Set.next(), but we are using the Resultset after closing the statement instance, which is also closing the Resultset.

In the code above, the fix will be not to close the statement at this place or not to use the Resultset after closing the statement instance. It is based on your application either we can remove the Demo_Statement.close() or the System.out.println(Result_Set.next()); statement.

Code:

        ResultSet Result_Set; // class variable

        Statement Demo_Statement = null;
        try{
            Demo_Statement = DB_Connection.createStatement();
            Result_Set = Demo_Statement.getGeneratedKeys();
           }
        catch (Exception e){
            throw e;
           }
        finally{
        try{
            if (Demo_Statement != null)
                Demo_Statement.close();
           } catch (SQLException e){
            throw e;
           }
       }

We just removed the part where we are trying the use the Resultset after the statement instance is closed. Once all the operations are done with the Resultset, we can close both the statement instance and Resultset.

Вы не должны передавать ResultSet через общедоступные методы. Это подвержено утечке ресурсов, потому что вы вынуждены сохранять отчет и соединение открыто. Закрытие их неявно закрывает набор результатов. Но держать их открытыми приведет к тому, что они будут болтаться и заставить БД исчерпывать ресурсы, когда их слишком много.

Сопоставьте его с коллекцией Javabeans, как это, и верните его вместо:

public List<Biler> list() throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Biler> bilers = new ArrayList<Biler>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            Biler biler = new Biler();
            biler.setId(resultSet.getLong("id"));
            biler.setName(resultSet.getString("name"));
            biler.setValue(resultSet.getInt("value"));
            bilers.add(biler);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    return bilers;
}

Или, если вы уже на Java 7, просто используйте try-with-resources выражение, которое автоматически закрывает эти ресурсы

public List<Biler> list() throws SQLException {
    List<Biler> bilers = new ArrayList<Biler>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            Biler biler = new Biler();
            biler.setId(resultSet.getLong("id"));
            biler.setName(resultSet.getString("name"));
            biler.setValue(resultSet.getInt("value"));
            bilers.add(biler);
        }
    }

    return bilers;
}

Кстати, вы не должны объявлять Connection, Statement и ResultSet как переменные экземпляра вообще (основная проблема безопасности потоков!) и не проглатывать SQLException в этой точке вообще ( вызывающий не будет знать, что возникла проблема), а также не закрывать ресурсы в том же try (если, например, закрытие результата закрывает исключение, то инструкция и соединение все еще открыты). Все эти проблемы исправлены в приведенных выше фрагментах кода.

Answer by Brielle Ponce

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. ,

Cool. I created a new Statement in getStuff() and that solved the problem.

– samxli

Apr 30 ’11 at 11:59

,

Can we tell which way AC power is going through a cable without cutting the cable?

,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.

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()

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()

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.

ResultSet

Answer by Raelyn Burns

Page generated in 0.009 sec. using MySQL 8.0.26-commercial ,The file you have requested (bug.php?id=620)
does not exist.,  © 2021, Oracle Corporation and/or its affiliates

The file you have requested (bug.php?id=620)
does not exist.

bug.php?id=620

Answer by Sky Pollard

Detect Loop in Linked List And Identify the Start …,Count Number of Words in the given input String,Shortest Distance in m x n Matrix from Source to D…,HackerRank Problem: Birthday Chocolate

  ResultSet resultSet; // class variable

  ...
  ...
  Statement st = null;
  try {
    st = conn.createStatement();
    resultSet = st.getGeneratedKeys();
  } catch (Exception e) {
    throw e;
  } finally {
    try {
      if (st != null) st.close();
    } catch (SQLException e) {
      throw e;
    }
  }

      

Answer by Amias Singh

Getting java.sql.SQLException: Operation not allowed after ResultSet closed ,Copyright © 2021 SemicolonWorld. All Rights Reserved. ,Bottom line: you have several ResultSet pertaining to the same Statement object concurrently opened.,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.

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