bukkit mysql,在java中使用mysql-variable（用于排名查询）

I'd like to create a ranking in a MySQL-Table for Users, sorted by their score. I already found a nice Query for that, but this Query is only working with phpmyadmin, not in java...

I got the following MySQL-Query which works well in phpmyadmin to create a Ranking:

SET @r=0; UPDATE `hg_stats` SET `stats_rank`= @r:= (@r+1) ORDER BY `stats_score` DESC;

In Java:

try {

preparedStatement = connection.prepareStatement("SET @r=0; UPDATE `hg_stats` SET `stats_rank`= @r:= (@r+1) ORDER BY `stats_score` DESC;");

preparedStatement.executeQuery();

} catch (SQLException e) {

e.printStackTrace();

}

I am getting the following Error and the Query is not working:

[00:09:50] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `hg_stats` SET `stats_rank`= @r:= (@r+1) ORDER BY `stats_score` DESC' at line 1

[00:09:50] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

[00:09:50] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

[00:09:50] [Server thread/WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

[00:09:50] [Server thread/WARN]: at java.lang.reflect.Constructor.newInstance(Unknown Source)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:382)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)

[00:09:50] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)

[00:09:50] [Server thread/WARN]: at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174)

[00:09:50] [Server thread/WARN]: at de.hg_pvp.game.stats.StatsManager.executeQuery(StatsManager.java:340)

[00:09:50] [Server thread/WARN]: at de.hg_pvp.game.stats.StatsManager.win(StatsManager.java:292)

[00:09:50] [Server thread/WARN]: at de.hg_pvp.game.HungerGamesPlugin.onDisable(HungerGamesPlugin.java:149)

[00:09:50] [Server thread/WARN]: at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:252)

[00:09:50] [Server thread/WARN]: at org.bukkit.plugin.java.JavaPluginLoader.disablePlugin(JavaPluginLoader.java:348)

[00:09:50] [Server thread/WARN]: at org.bukkit.plugin.SimplePluginManager.disablePlugin(SimplePluginManager.java:424)

[00:09:50] [Server thread/WARN]: at org.bukkit.plugin.SimplePluginManager.disablePlugins(SimplePluginManager.java:417)

[00:09:50] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_7_R2.CraftServer.disablePlugins(CraftServer.java:398)

[00:09:50] [Server thread/WARN]: at net.minecraft.server.v1_7_R2.MinecraftServer.stop(MinecraftServer.java:384)

[00:09:50] [Server thread/WARN]: at net.minecraft.server.v1_7_R2.MinecraftServer.run(MinecraftServer.java:507)

[00:09:50] [Server thread/WARN]: at net.minecraft.server.v1_7_R2.ThreadServerApplication.run(SourceFile:618)

解决方案

The thing you call a query is in fact a sequence of queries, separated by semicolons.

But you can't run more than one query at a time through JDBC.

You need something like this:

PreparedStatement ps1 = connection.prepareStatement

("SET @r=0");

ps1.ExecuteQuery();

PreparedStatement ps2 = connection.prepareStatement

("UPDATE `hg_stats` SET `stats_rank`= @r:= (@r+1) ORDER BY `stats_score` DESC");

ps2.ExecuteQuery();