Cannot create ArrayList with ResultSet

Question

I'm trying to create an ArrayList based on the ResultSet. Using the while loop to iterate through all the rows I can get only the first row (With rs.next() and rs.first()) as well as the last one (rs.last()).

public ArrayList retrieveFromDb(Statement myStmt){    

    try{
        ArrayList<String> inner = new ArrayList<>();
        String query = "SELECT * FROM product";
        ResultSet rs = myStmt.executeQuery(query);

        while(rs.next()){
            System.out.println(rs.getString("barcode"));
            inner.add(rs.getString("barcode"));
            System.out.println("done");

        return inner;
        }

    } catch (SQLException ex) {
        Logger.getLogger(Product.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
}

What I need is for the ArrayList to get the information from all the rows.

UPDATE: It looks like I failed to properly return the list by returning it too early (as pointed out in the answer below.)

Possible duplicate of [Resultset To List](https://stackoverflow.com/questions/1966836/resultset-to-list) — Cisco, May 26 '19 at 15:02
You have placed the return inner statement inside the while loop. Place the return statement outside the while loop and try. — Manjunath H M, May 26 '19 at 15:09
Hello @FranciscoMateo, thank you for taking the time to read my question. Unfortunately the possible duplicate didn't help me out. If I missed something please let me know. — Volpym, May 26 '19 at 15:09
*the possible duplicate didn't help me out.* then explain why that is. — GhostCat, May 26 '19 at 15:13

Basil Bourque · Accepted Answer · 2019-05-26T15:35:23.027

You are returning your list too early. Move your return statement to the end.

Reread the Oracle Tutorial, Retrieving and Modifying Values from Result Sets.

Also, close your result set when finished using it.

We can make your returned list a more general List rather than ArrayList, and more specifically of type String.

public List< String > retrieveFromDb( Statement myStmt ){    
    ArrayList<String> list = new ArrayList<>() ;

    try{
        String query = "SELECT * FROM product ;" ;
        ResultSet rs = myStmt.executeQuery( query ) ;

        while( rs.next() ){  // Looping the rows of the result set: first row, second row, third, fourth, and so on.
            String barcode = rs.getString( "barcode" ) ;
            list.add( rs.getString( "barcode" ) );
        }

    } catch (SQLException ex) {
        Logger.getLogger(Product.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if( Objects.nonNull( rs ) ) {
            rs.close() ;  // <--- Best to close your result set when done.
        }
    }

    return list ;
}

Even better, use try-with-resources to automatically close your result set.

public List< String > retrieveFromDb( Statement myStmt ){    
    ArrayList<String> list = new ArrayList<>() ;
    String query = "SELECT * FROM product ;" ;

    try (
        ResultSet rs = myStmt.executeQuery( query ) ;
    )
    {
        while( rs.next() ){  // Looping the rows of the result set: first row, second row, third, fourth, and so on.
            String barcode = rs.getString( "barcode" ) ;
            list.add( rs.getString( "barcode" ) );
        }

    } catch (SQLException ex) {
        Logger.getLogger(Product.class.getName()).log(Level.SEVERE, null, ex);
    }

    return list ;
}

Tip: I recommend always terminating your SQL statements properly, with a semicolon. You may get away without it, but making it a habit can prevent problems in the long run.

Cannot create ArrayList with ResultSet

1 Answers1