Why does rs.next() return only one result?

Question

I wrote codes like this. In the table (SocialLevelPerUser), there are two rows satifying the "WHERE-clause". However, the "while-statement" is run only one time. I don't understand why this happens. I am using PostgreSQL database. Any thoughts / comments appreciated. Thanks.

ArrayList<Friend> getFriendList(String userId){

    ArrayList<Friend> friendList = new ArrayList<Friend>();
    Friend eachFriend = new Friend();
    try {
        st = connection.createStatement();
        rs = st.executeQuery("SELECT \"SourceName\", \"DestinationName\", \"Portion\" "
                            + "FROM public.\"SocialLevelPerUser\" "
                            + "WHERE \"SourceName\" = '" + userId + "'");

        while(rs.next()){
            eachFriend = new Friend();

            String friendId = rs.getString("DestinationName");
            double followingPortion = Double.parseDouble(rs.getString("Portion"));

            eachFriend.setUserId(friendId);
            eachFriend.setFollowingPortion(followingPortion);

            boolean isComplete = isCompleteUserId(friendId);
            boolean isFollow = isFollower(friendId, userId);
            double followerPortion = getFollowerPortion(friendId, userId);

            eachFriend.setIsCompleteUserId(isComplete);
            eachFriend.setIsFollower(isFollow);
            eachFriend.setFollowerPortion(followerPortion);

            friendList.add(eachFriend);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return friendList;
}

I tried to input query on the client of PostgreSQL resulting in like below figure.

Because it's **"next"**. Returns the following one from the sequence of results. — Nikolas Charalambidis, Nov 16 '16 at 14:58
Are you certain that your query is returning more than one record? You should check this first, to make sure there really is a result set beyond what you are currently seeing. — Tim Biegeleisen, Nov 16 '16 at 15:00
@NikolasCharalambidis No, in the api documentation: `Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.` — pleft, Nov 16 '16 at 15:01
@khelwood Yes I am sure that there are definitely two rows. I am seeing now. — Seung-hee Han, Nov 16 '16 at 15:04
@TimBiegeleisen Yes I am sure that there are two rows; [1] Han, A, 1 [2] Kim, B, 0.5 But returning only first row — Seung-hee Han, Nov 16 '16 at 15:06
Please [check the row count of this resultset](http://stackoverflow.com/questions/25351452/how-get-the-number-of-rows-count-by-resultset-in-java?noredirect=1&lq=1). I don't see any problem with your loop ... so this should be the query. — AxelH, Nov 16 '16 at 15:07
@Seung-heeHan - There can't be two rows matching that `WHERE` clause, otherwise you would have two rows returned, not just one. Check carefully for spaces in the `SourceName` of the row you don't see in the result set. Maybe your eyes that ID but don't see some space that makes your `WHERE` clause evaluate to `false`. — BackSlash, Nov 16 '16 at 15:08
How do you know that the while statement is called only once? By checking the `friendList` size or have you placed a breakpoint inside the while statement and debug it? — pleft, Nov 16 '16 at 15:08
@Seung-heeHan - Please [edit] your question to show your sample data. — Gord Thompson, Nov 16 '16 at 15:10
@pleft yes, I placed a breakpoint inside the while statement and did debug it. So I knew the while statement is called only once — Seung-hee Han, Nov 16 '16 at 15:20
@Seung-heeHan "Yes I am sure that there are two rows; [1] Han, A, 1 [2] Kim, B, 0.5" that is impossible since results you printed for `SourceName` are `Han` and `Kim`. But SQL query has `SELECT ... where SourceName = [userId]`, and `userId` can't be at the same time `Han` and `Kim`. Try to print query to console and see how it really looks like when if is filled with data. Then try to execute it in your database to see if such query is really returning you more than one results. — Pshemo, Nov 16 '16 at 15:20
@GordThompson I edited my question. I added a captured image. thank you — Seung-hee Han, Nov 16 '16 at 15:21
@Pshemo Sorry, "[1] Han, A, 1 [2] Kim, B, 0.5" was just example. that's my mistake. But, I edited my question, I uploaded captured image when I tried to print result on the client program of PostgreSQL — Seung-hee Han, Nov 16 '16 at 15:24
Can you place the following line at the top of your method, post us the result and then copy-paste it and execute it at PostgreSQL client? `System.out.println("SELECT \"SourceName\", \"DestinationName\", \"Portion\" " + "FROM public.\"SocialLevelPerUser\" " + "WHERE \"SourceName\" = '" + userId + "'");` — pleft, Nov 16 '16 at 15:26
Moreover have you checked your log files if any Exception rises? If an exception rises in the first run of the while statement the program will exit and no second run will occur. For example the line: `double followingPortion = Double.parseDouble(rs.getString("Portion"));` can throw a `NumberFormatException`. You also have some custom methods that might also throw excepions (`isCompleteUserId`, `isFollower`, `getFollowerPortion`) — pleft, Nov 16 '16 at 15:31
@pleft when I tried this "System.out.println("SELECT \"SourceName\", \"DestinationName\", \"Portion\" " + "FROM public.\"SocialLevelPerUser\" " + "WHERE \"SourceName\" = '" + userId + "'");", I could see "SELECT "SourceName", "DestinationName", "Portion" FROM public."SocialLevelPerUser" WHERE "SourceName" = 'HBananah2'" I did copy-paste it at PostgreSQL client resulting in same thing. there were two rows.. — Seung-hee Han, Nov 16 '16 at 15:32
@pleft In the custom methods, I also used those values of rs, st. that's the problem. I fixed it. thank you! — Seung-hee Han, Nov 16 '16 at 15:45

Why does rs.next() return only one result?

0 Answers0