Solved [MySQL] Getting 'Top Players'

I am kind of new to MySQL and wonder how i can get the top 3 scores and then the players name.

Her is my code so far

Code:java

 public static String[] getTopPlayers() throws SQLException
{
String[] result = new String[3];
Statement s = c.createStatement();
if(s != null)
{
ResultSet rs = s.executeQuery("SELECT PlayerName FROM `SkyWars_UserData` ORDER BY `Credits` LIMIT 3;");
for(int i = 0; i < 3; i++)
{
if(rs.next())
{
result[i ] = rs.getString("PlayerName");
}
else // Set it to ""
{
Bukkit.getServer().getConsoleSender().sendMessage("[SkyWars]" + ChatColor.RED + "ELSE");
result[i ] = "";
}
}
}
return result;
}
 

 

minelazz
You'll want to loop through the ResultSet however many times you want to. If you want the top three, you'll need to loop through three times.

EDIT: Sorry, misread the code because the format is off. What is wrong with what you have? It looks like it should work.

 

Look at this snippet:

Code:

    public static HashMap<String, Double> getMaxCredits() {
       HashMap<String, Double> highest = new HashMap<String, Double>();
        MySQL sql = Main.sql;
        ResultSet res = sql.querySQL("SELECT * FROM Credits MAX");
        try {
            if (res.next()) {
                String playername = res.getString("Player");
                double balance = res.getDouble("Balance");
                highest.put(playername, balance);
                res.close();
            } else {
                res.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return highest;
    }
}

 

All of that looks alright, but you should probably close the ResultSet in a finally block. You won't have to put it there twice, you can get ride of the else statement, and the ResultSet will close even if an exception is thrown in your try block.

 

amhokies and calebbfmv thanks alot! Tried the code, but i still can't get it to work. The code return string "java.lang.String;@c7c08f"

 

Where are you trying to print the results? Looks like you're trying to print the entire array at once instead of each individual String object.

 

It was just a snipet, must be run in a for loop.

 

Yes that was that i did, but tried also "getMaxCredits().get(0)" and "getMaxCredits().size()" it just return the same. This is the code i used

Code:java

//The table
statement.execute("CREATE TABLE SkyWars_UserData (PlayerName VARCHAR(40), Credits int, SW_Score int, Snowballs int, Enderpearls int, SW_Kit1 int, SW_Kit2 int, SW_Kit3 int, SW_Kit4 int);");
 
//Top score method
public static HashMap<String, Double> getMaxCredits() throws SQLException {
     HashMap<String, Double> highest = new HashMap<String, Double>();
     try {
             MySQL MySQL = new MySQL(plugin, "mysqleu.envioushost.com", "3306", "xxxx", "xxxxx",                      "xxxxxxx");
 
             ResultSet res = MySQL.querySQL("SELECT * FROM Credits MAX");
 
              if (res.next()) {
             String playername = res.getString("PlayerName");
             double balance = res.getDouble("SW_Score");
             highest.put(playername, balance);
             res.close();
      }
      res.close();
       } catch (SQLException e) {
              e.printStackTrace();
       }
 return highest;
}
 
//used this onEnable()
Bukkit.getServer().getConsoleSender().sendMessage("[SkyWars]" + ChatColor.RED + getMaxCredits.get(0));
 

EDIT; Got it finally to work! Using list instead of HashMap now
Thanks a lot for the help guys!

Here is my new code;

Code:java

 
public static List<String> getTopPlayers() throws SQLException{
        List<String> result = new ArrayList<String>();
        Statement s = c.createStatement();   
        if(s != null){
            ResultSet rs = s.executeQuery("SELECT PlayerName FROM `SkyWars_UserData` ORDER BY `Credits`");
            rs.findColumn("PlayerName"); 
            if(rs.last()){
                result.add(rs.getString("PlayerName"));
            }if(rs.previous()){
                result.add(rs.getString("PlayerName"));
            }if(rs.previous()){
                result.add(rs.getString("PlayerName"));
            }                     
        }
        return result;
    }
 

 

What mine does, when properly executed, is out print the names AND their values