从 MySQL 查询中找到第一个正确记录后，Java ResultSet 返回所有记录

Question

好的，例如我的表格如下所示:

┌──────┬────────┬─────────────┬───────────┐│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;
}

如果我给它“Rory”作为用户名，它在计算位置时会返回 ID 为 3、4 和 5 的记录，而不仅仅是 3。

为什么要这样做？我相当确定我的代码是正确的，因为当我将确切的 SQL 查询复制到 phpMyAdmin 中时，它工作得很好。

Answer 1

我不确定你想用 SortingUser 做什么，但我会使用更简单的代码，并让 SQL 自己进行排序。这通常非常有效，特别是如果表上有适当的索引。

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;
}