Closed Connection: next in java

Question

I have ResultSet Methods which I am closing the Connection in a finallly Block:

 public static ResultSet countdrcountcr(String vforacid) throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        try {

            conn = db.getDbConnection();
            String sql = "SELECT NVL (SUM (DECODE (part_tran_type, 'D', 1, 0)), 0), "
                    + " NVL (SUM (DECODE (part_tran_type, 'C', 1, 0)), 0) "
                    + " FROM tbaadm.htd WHERE acid IN (SELECT acid "
                    + " FROM tbaadm.gam WHERE foracid = '" + vforacid + "') "
                    + " AND tran_date >= '22-NOV-2013'  AND tran_date <= '30-NOV-2013' "
                    + " AND pstd_flg = 'Y' AND del_flg != 'Y'";
            PreparedStatement ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            return rs;
        } finally {
            conn.close();
        }
    }

But I am getting the error :

edit The whole ErrorTrace

Exception in thread "main" java.sql.SQLException: Closed Connection: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181)
at statement.Statement.main(Statement.java:34)
Java Result: 1

What am I not doing right?

Can you point the line that the error occurs and put your stacktrace on your question please. — Jorge Campos, Dec 10 '13 at 15:01
You're closing the connection but still using the resultset. — Luiggi Mendoza, Dec 10 '13 at 15:02
Yes I returned the value before Closing. The error is occuring When I try to call this ResultSet `while(rs1.next())` — Stanley Mungai, Dec 10 '13 at 15:05

score 11 · Accepted Answer · edited May 23 '17 at 12:32

You're returning a ResultSet for future use but after using it you're closing the connection, so you have no way to retrieve the data since the resource is already closed. Note that finally is always called, even if you return something in the try or catch code block, refer to Does finally always execute in Java?

In detail, this is the problem:

Open the connection
Prepare a statement
Get the result set
Return the result set
Close the connection (that may close the associated resources i.e. it may close the PreparedStatement and the ResultSet associated with the current Connection) because, as noted in the link before, finally block is always executed at least that the JVM crashes or you manually finish the application using System.exit.
Using a closed ResultSet. It is closed due to the previous step.

A possible solution would be that your countdrcountcr method and all other methods that return a ResultSet receive the Connection as parameter, so the method that calls it will handle the connection opening and closing. Also, take note that you should not use static methods to handle your database operations if you're working in a multi threaded environment e.g. a web application.

Could you please explain why so, if a return value is set before the con.close(); I mean the control flow Execute all the code=>rs is already refereed to the set of rows=> return this value => and close the connection . why do con need the connection still if the value is already refered? — Yehia Awad, Dec 10 '13 at 15:10

score 1 · Answer 2 · edited Dec 26 '19 at 06:15

1

I think your query is taking a long time to execute and getting terminated by the driver/tomcat level.

Check you application context xml file for parameter removeAbandonedTimeout value.

removeAbandonedTimeout=300

means, if any query running for more than 300 seconds will be close by the JDBC driver. This is done to avoid connection pool "leak". To fix this you can set the value with some higher number.

More info about this param and other related parameters can be found here

edited Dec 26 '19 at 06:15

Ajay Kr Choudhary

1,304
1
14
23

answered Mar 04 '15 at 16:43

lrathod

1,094
1
9
17

This answer solved my issue. We were using tomcat pool and the driver was closing the connection if it exceeded the removeAbondonedTimeout – Gautam Tadigoppula Mar 09 '19 at 17:11
Glad that it helped. – lrathod Mar 11 '19 at 16:14

score 0 · Answer 3 · answered Dec 10 '13 at 15:03

You're closing the underlying Connection in your finally block... You're not closing the PreparedStatement (and you should, but you need to close that after you use your ResultSet too). use the finally block of the caller (where you open the Connection). Also, you might want to consider using setFetchSize().

score 0 · Answer 4 · answered Dec 10 '13 at 15:04

You cannot close a Connection then use the ResultSet. You have to finish using the ResultSet first, then close the Connection sometime after. The normal pattern is to finish your work with the ResultSet first, usually in a "Data Access Object", and return some encapsulated representation of the data as an object.

Nathan Hughes · Answer 5 · 2020-04-01T13:48:56.477

Luiggi's answer is correct but it seems like what the OP didn't understand was why closing the connection prevented the ResultSet from working, since the code got the ResultSet before the connection closed.

There's a popular misunderstanding that a ResultSet must be some kind of data-holding object that you can use to pass stuff around in. It isn't. it's just a reference to a database cursor, it hasn't actually fetched the data for a row until you call next() on it. It needs a live connection in order to work. You need to unpack your results from the query into a collection (usually a list) before you close the connection.

BTW, don't add parameters to your SQL with string concatenation, it opens you up to SQL injection (and also handles quoting the parameters is a pain). You can add ? to your SQL and add values for the parameters by calling methods on the preparedStatement.

If you use Spring JDBC it will handle all the tedious JDBC stuff for you (including closing everything that needs to be closed), and all you have to handle is implementing a RowMapper to describe how to move data from the ResultSet into the collection.

score 0 · Answer 6 · answered Mar 27 '15 at 06:35

If u tryied to close the connection inside the while block that time also u can get this kind of exception...so close the connection after the while block

package com.literals;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataBaseDemo {

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("driver is loading...........");
            Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:mytest","SYSTEM","murali");
            System.out.println("connection is established");
            Statement st=con.createStatement();
            System.out.println("statement is created");

            ResultSet rs=st.executeQuery("select * from student");
            while(rs.next()){
                System.out.println(rs.getString(1)+"          "+rs.getInt(2)+"          "+rs.getString(3)+"");
                con.close();

            }


        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }





    }

}

score 0 · Answer 7 · answered Dec 02 '15 at 09:38

0

Like say above: Also, take note that you should not use static methods to handle your database operations if you're working in a multi threaded environment e.g. a web application.

That really help.

answered Dec 02 '15 at 09:38

Jaime Gabriel Valerio

1

Testilla · Answer 8 · 2020-07-19T13:37:07.997

0

I had a similar problem where my connection was being closed inside a while loop, so the condition could not be checked in the next round. To fix, I placed con.close(); outside the loop and this resolved the issue. Like this:

    while (rs.next()) {
    String name = rs.getString("NAME");
    }
    con.close(); //placed outside the loop
}

edited Jul 19 '20 at 13:37

answered Jul 19 '20 at 13:28

Testilla

602
8
21

Closed Connection: next in java

8 Answers8

Linked