Java ResultSet working with postgresql

Question

I have a table on my database where it holds 2 columns: uid1 - someone's id, uid2 - his friend.

I want to create a list where I someone's friend - till 5 depth connection.

So I built the following recursive method:

    private void recursive(int theUid,ResultSet rs,ArrayList<Integer> friends,int count,int next,PreparedStatement pstmt) throws SQLException{
        if(count>=1 && next==theUid){
            return;
        }
        else if(count>=DEPTH_OF_CONNECTION){
            return;
        }

        else{
            pstmt.setInt(1,next);
            rs=pstmt.executeQuery();
            count++;
            while(rs.next()) {
                friends.add(rs.getInt(1));
                recursive(theUid,rs,friends,count,rs.getInt(1),pstmt);
            }
        }
    }
}

And I got the following error:

Exception in thread "main" org.postgresql.util.PSQLException: This ResultSet is closed. at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkClosed(AbstractJdbc2ResultSet.java:2654) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.next(AbstractJdbc2ResultSet.java:1786)

Can you help me find what is the problem?

You know you can do that with just a single statement in PostgreSQL? — user330315
– user330315, Commented Dec 12, 2011 at 16:59
Talking to the server for every single step is very expensive. A single query with a recursive CTE would be the way to go. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 12, 2011 at 17:08

soulcheck · Accepted Answer · 2011-12-12 17:11:32Z

9

Javadoc for jdbc Statement says

By default, only one ResultSet object per Statement object can be open at the same time.

So my guess is you are trying to open too many resultsets for the same PreparedStatement at the same time.

edit:

Postgres jdbc driver doc seems to confirm it:

You can use a single Statement instance as many times as you want. You could create one as soon as you open the connection and use it for the connection's lifetime. But you have to remember that only one ResultSet can exist per Statement or PreparedStatement at a given time.

edited Dec 12, 2011 at 17:11

answered Dec 12, 2011 at 17:06

soulcheck

36.9k6 gold badges95 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jorgeu Over a year ago

just to complete your [right] answer. The code should take all friends from the resultset, close it and then recurse over the list it got.

soulcheck Over a year ago

...or use common table expression :)

Mr. Will · Accepted Answer · 2011-12-12 17:05:49Z

1

From what I can tell, you are using the ResultSet object to store your statement results and then passing it as a parameter to the recursive function call. So basically you are overwriting the variable and losing the reference to it. You should either write your sql statement to retrieve the data you need or not pass the same ResultSet object to the recursive call.

answered Dec 12, 2011 at 17:05

Mr. Will

2,3083 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

Java ResultSet working with postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related