Return ordered key value pairs from database - Java

Question

I am trying to create a league table from key value pairs in a derby database - the rows in the database have only two columns, TeamName & Goals.

I need to get these to my GUI class so I can set the keys & values as JLabels in the league table. Ordered top down descending in terms of total goals.

From what I have read LinkedHashMap & TreeSet should both be able to assist me.

Code I have so far:

public TreeMap viewTeams(){
    TreeMap teamData = new TreeMap();
    String viewTeams = "SELECT * FROM HUI.TEAM";
    connectToDatabase(dbName);
    try {
        stmt = dbConnection.createStatement();
        rs = stmt.executeQuery(viewTeams);
    } catch (SQLException error) {
        System.err.println("Error querying database for teams: " + error.toString());
    } 
    try {
        while (rs.next()){
            teamData.put((rs.getString("TEAMNAME")), (rs.getInt("GOALSSCORED")));
        }
    } catch (SQLException error) {
        System.err.println("Error adding players to HasMap: " + error.toString());
    }
    return teamData;
}

In the TeamDB class

public void updateLeagueTable(){
    TeamDB tdb = new TeamDB("FootManDatabase");
    TreeMap teamData = tdb.viewTeams(); // Do I need this new TreeMap?
    // How do I Iterate through the pairs in descending order?
}

In the GUI class

Also: https://stackoverflow.com/q/2770321/3788176. Use `TreeMap`. — Andy Turner, Dec 28 '17 at 12:49

score 0 · Accepted Answer · answered Dec 28 '17 at 13:29

I would just let the SQL database to the sorting, as that's one thing it can do well.

Also, you can't use a TreeMap<String, Integer> as that would iterate by team name instead of goals. And you can't use TreeMap<Integer, String> either because it doesn't allow duplicates so you won't be able to have two teams with the same goals scored. Instead, I would use a list. Something like the following (but remember to close your resources properly, which I've left out for clarity).

class TeamScore {
    private final String name;
    private final int numGoals;
    public TeamScore(String name, int numGoals) {
        this.name = name;
        this.numGoals = numGoals;
    }
    // getters...
}


public List<TeamScore> viewTeams() throws SQLException {
    List<TeamScore> teamData = new ArrayList<>();
    String viewTeams = "SELECT * FROM HUI.TEAM ORDER BY GOALSSCORED DESC";
    connectToDatabase(dbName);
    stmt = dbConnection.createStatement();
    rs = stmt.executeQuery(viewTeams);
    while (rs.next()) {
        teamData.add(new TeamScore(rs.getString("TEAMNAME"), rs.getInt("GOALSSCORED"));
    }
    return teamData;
}

Return ordered key value pairs from database - Java

1 Answers1