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

Answer 1

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.