Java ResultSet returning all records after the first correct record is found from MySQL Query

Question

Ok, so for example my table would look like:

┌──────┬────────┬─────────────┬───────────┐
│UserID│Username│CurrentLeague│TotalPoints│
├──────┼────────┼─────────────┼───────────┤
│1     │Elliot  │randomLeague │15         │
├──────┼────────┼─────────────┼───────────┤
│2     │Callum  │randomLeague │20         │
├──────┼────────┼─────────────┼───────────┤
│3     │Rory    │testLeague   │17         │
├──────┼────────┼─────────────┼───────────┤
│4     │Admin   │NULL         │0          │
├──────┼────────┼─────────────┼───────────┤
│5     │Steve   │randomLeague │21         │
└──────┴────────┴─────────────┴───────────┘

And here is my code in my Java project for the class that I'm using here.

public int getLeaguePosition(String username)
{
    try
    {
        int leaguePosition = 0;
        String leagueName = getLeague(username);
        System.out.println("League Name: " + leagueName);
        ArrayList<SortingUser> sortingUser = new ArrayList<SortingUser>();
        String query = "SELECT * FROM Users WHERE CurrentLeague = ?";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, leagueName);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next())
        {
            String retrievedUsername = resultSet.getString("Username");
            System.out.println(retrievedUsername);
            SortingUser retrievedUser = new SortingUser(retrievedUsername);
            sortingUser.add(retrievedUser);
        }
        Collections.sort(sortingUser);
        for(int i = 0; i < sortingUser.size(); i++)
        {
            SortingUser retrievedSortingUser = sortingUser.get(i);
            String retrievedUsername = retrievedSortingUser.getUsername();
            if(retrievedUsername.contains(username) && username.contains(retrievedUsername))
            {
                leaguePosition = i + 1;
                System.out.println("League Position for " + username.toUpperCase() + " is " + leaguePosition);
                return leaguePosition;
            }
        }
    }
    catch(Exception e)
    {
        System.out.println("Couldn't get league position for: " + username);
        e.printStackTrace();
    }
    return 0;
}

and if I gave it "Rory" as the username it would return the records with ID 3, 4 and 5 rather than just 3 when calculating the position.

Why does it do this? I'm fairly sure my code is correct because when I copy that exact SQL query into phpMyAdmin it works perfectly.

Answer 1

I am not sure what you were trying to do there with the SortingUser, but I'd go with much simpler code, and let SQL do its own sorting. It's usually very efficient at that, especially if you have the proper indexes on the table.

public int getLeaguePosition(String username)
{
    try
    {
        String leagueName = getLeague(username);
        System.out.println("League Name: " + leagueName);

        // This is returning all the users in the same league sorted by descending points.

        String query = "SELECT * FROM Users WHERE CurrentLeague = ? ORDER BY TotalPoints DESC";
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, leagueName);
        resultSet = preparedStatement.executeQuery();

        int leaguePosition = 0;
        while(resultSet.next())
        {
            // Since the result set is already sorted, the first player has the most points, so his
            // leaguePosition is one. The second has the next best number of points, so his position
            // is two, and so on. So we keep the leaguePosition var based on the number of the row.

            leaguePosition++;

            // And if the user name retrieved actually matches the one that we passed, then this is
            // his league position.

            String retrievedUsername = resultSet.getString("Username");
            if ( retrievedUsername.equals( username ) ) {
                break;
            }

        }
        resultSet.close();

        return leaguePosition;

    }
    catch(Exception e)
    {
        System.out.println("Couldn't get league position for: " + username);
        e.printStackTrace();
    }
    return 0;
}