ResultSet reset when using recursion

Question

I have a question regarding ResultSet objects in Java and recursion.

I was working on some code for university and whenever I found a descendant I recursed on with that new node but when I came out of the recursion and tried to rs.next() the pointer had gone from pointing to row 1 back to row 0 and when it hit row 0 the rs.next() failed and it returned! I knew there was one thing in there that it hadn't read yet! What is it that causes this?

The only way I got round that problem was to go through the resultset and get every element and add it into an array list, then loop through the arraylist doing the recursion on each element in the array! Surely this must be a better way around this?

This is the new code I'm using

private Vector<String> getDescendents(String dogname, Vector<String> anc) {
    if (anc == null) anc = new Vector<String>();
    ArrayList<String> tempList = new ArrayList<String>(2);
    try {
        System.out.println("Inside ");
        childStmt.setString(1,dogname);
        childStmt.setString(2,dogname);
        ResultSet rs = childStmt.executeQuery();
        System.out.println("Before while "+rs.getRow());
        while (rs.next()){
            String col1 = rs.getString(1);
            tempList.add(col1);
            anc.add(col1);
        } 
        for (String s:tempList){
            getDescendents(s,anc);
        }

    }
    catch(Exception e) {
        doError(e, "Failed to execute ancestor query in getBreeding");
    }
    return anc;
}

However before this, I had the getDescendents call inside the while loop and thus no for loop and no arraylist either, but whenever it actually recursed it would loose track of the resultset when it returned out of the recursion.

Further details : When I used the debugger (nearly said gdb there lol far too much C) the ID of the result set was the same but the row pointer had returned to 0 and the rs.next call failed!

Once again any explanation is appreciated!

p.s it previously looked like

private Vector<String> getDescendents(String dogname, Vector<String> anc) {
    if (anc == null) anc = new Vector<String>();
    ArrayList<String> tempList = new ArrayList<String>(2);
    try {
        System.out.println("Inside ");
        childStmt.setString(1,dogname);
        childStmt.setString(2,dogname);
        ResultSet rs = childStmt.executeQuery();
        System.out.println("Before while "+rs.getRow());
        while (rs.next()){
            String col1 = rs.getString(1);
            anc.add(col1);
            getDescendendts(col1,anc);
        } 

    }
    catch(Exception e) {
        doError(e, "Failed to execute ancestor query in getBreeding");
    }
    return anc;
}

Answer 1

It looks like you're re-using childStmt; don't do this. From the Statement javadoc:

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.

You'll have to either save all the rows first, then do the recursive query, or create a new Statement for each ResultSet you want to fetch.