Solved MySQL Command Issues

Discussion in 'Plugin Development' started by ShadowX90, Jul 24, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    ShadowX90

    Hello I am currently developing a plugin that uses MySQL to store information. Until recently I have been getting the following error:

    Code:
    [21:10:42] [Server thread/ERROR]: Could not pass event PlayerJoinEvent to PS2J v0.0.1
    org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:320) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:486) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:471) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.PlayerList.c(PlayerList.java:225) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.PlayerList.a(PlayerList.java:116) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.LoginListener.c(LoginListener.java:78) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.LoginListener.a(LoginListener.java:42) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:149) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:655) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:250) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:545) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:457) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617) [craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
    Caused by: java.lang.NullPointerException
        at com.ps2j.mysql.MySQL.command(MySQL.java:19) ~[?:?]
        at com.ps2j.listener.PS2JPlayerListener.onFirstJoin(PS2JPlayerListener.java:29) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_25]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.7.0_25]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.7.0_25]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.7.0_25]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:318) ~[craftbukkit.jar:git-Bukkit-1.7.2-R0.3-b3020jnks]
        ... 14 more
    The two pieces of code it is pointing to are.

    In the code below it is pointing to the mysql.command(...); line
    Code:java
    1. public void onFirstJoin(PlayerJoinEvent event) throws SQLException{
    2. Player player = event.getPlayer();
    3. String playeruuid = player.getUniqueId().toString();
    4. String playername = player.getDisplayName();
    5. mysql.command("INSERT INTO `ps2j`.`players` VALUES (`" + playeruuid + "`,`" + playername +"`,`0`)");
    6. }


    And in this code it is pointing to the Connection conn = ....; line
    Code:java
    1. public void command(String s) throws SQLException {
    2. Connection conn = DriverManager.getConnection("jdbc:mysql://" + plugin.getConfig().getString("mysql.host") + ":" + plugin.getConfig().getString("mysql.port") + "/", plugin.getConfig().getString("mysql.user"), plugin.getConfig().getString("mysql.pass"));
    3. PreparedStatement QueryStatement = conn.prepareStatement(s);
    4. QueryStatement.executeUpdate();
    5. QueryStatement.close();
    6. conn.close();
    7. }


    Thanks ahead of time.
     
  2. Offline

    macboinc

    Don't need port.
     
  3. Offline

    DannyDog

    ShadowX90
    getConnection(); is wrong.
    It should all be as String url.
    So remove the commas.
     
  4. Offline

    LCastr0

    Don't make your void throw the Exception, first of all.
    Do a try/catch inside of it.
    Second, add spaces after the commas in your SQL command line.
    Also, when you get errors, provide the full class code, not only the methods, because sometimes the errors are not inside the methods but somewhere related to the methods (Also it's easier to find the line that the error log shows)
     
  5. Offline

    fireblast709

    *configures MySQL to use port 3307*

    DannyDog MySQL uses those two for the username and password, his code should be fine there.
    http://docs.oracle.com/javase/7/doc...g.String, java.lang.String, java.lang.String)

    LCastr0 whitespace hardly matters (at least not around commas and parenthesis, it could add a bit of readability at most - nice nonetheless)

    ShadowX90 plugin could be null and you might need to add the database name you want to use tables from after the /. To be sure u would need the full class (or at least all relevant code would be nice)
     
  6. Offline

    DannyDog

  7. Offline

    fireblast709

  8. Offline

    ShadowX90

    fireblast709

    Alright here is my new Listener class

    Code:java
    1. package com.ps2j.listener;
    2.  
    3. import java.sql.SQLException;
    4.  
    5. import org.bukkit.entity.Player;
    6. import org.bukkit.event.EventHandler;
    7. import org.bukkit.event.Listener;
    8. import org.bukkit.event.player.PlayerJoinEvent;
    9.  
    10. import com.ps2j.PluginMain;
    11. import com.ps2j.mysql.MySQL;
    12.  
    13. public class PS2JPlayerListener implements Listener{
    14. PluginMain plugin;
    15. public PS2JPlayerListener(PluginMain instance) {
    16. instance.getServer().getPluginManager().registerEvents(this, instance);
    17. plugin = instance;
    18. }
    19.  
    20. @EventHandler
    21. public void onFirstJoin(PlayerJoinEvent event){
    22. MySQL mysql = new MySQL(plugin);
    23. Player player = event.getPlayer();
    24. String playeruuid = player.getUniqueId().toString();
    25. String playername = player.getDisplayName();
    26. try {
    27. mysql.command("INSERT INTO ps2j.players VALUES (`" + playeruuid + "`, `" + playername +"`, `0`)");
    28. } catch (SQLException e) {
    29. e.printStackTrace();
    30. }
    31. }
    32. }
    33.  


    And here is the mysql class
    Code:java
    1. package com.ps2j.mysql;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.PreparedStatement;
    6. import java.sql.ResultSet;
    7. import java.sql.SQLException;
    8. import java.sql.Statement;
    9.  
    10. import com.ps2j.PluginMain;
    11.  
    12. public class MySQL {
    13. PluginMain plugin;
    14.  
    15. public MySQL(PluginMain instance) {
    16. plugin = instance;
    17. }
    18. public void command(String s) throws SQLException {
    19. Connection conn = DriverManager.getConnection("jdbc:mysql://" + plugin.getConfig().getString("mysql.host") + ":" + plugin.getConfig().getString("mysql.port") + "/", plugin.getConfig().getString("mysql.user"), plugin.getConfig().getString("mysql.pass"));
    20. PreparedStatement QueryStatement = conn.prepareStatement(s);
    21. QueryStatement.executeUpdate();
    22. QueryStatement.close();
    23. conn.close();
    24. }
    25. public int queryint(String s, int b) throws SQLException {
    26. int data;
    27. Connection conn = DriverManager.getConnection("jdbc:mysql://" + plugin.getConfig().getString("mysql.host") + ":" + plugin.getConfig().getString("mysql.port") + "/", plugin.getConfig().getString("mysql.user"), plugin.getConfig().getString("mysql.pass"));
    28. Statement stmt = conn.createStatement();
    29. ResultSet result = stmt.executeQuery(s);
    30. result.first();
    31. data = result.getInt(b);
    32. stmt.close();
    33. conn.close();
    34. return data;
    35. }
    36. public String querystring(String s, int b) throws SQLException {
    37. String data;
    38. Connection conn = DriverManager.getConnection("jdbc:mysql://" + plugin.getConfig().getString("mysql.host") + ":" + plugin.getConfig().getString("mysql.port") + "/", plugin.getConfig().getString("mysql.user"), plugin.getConfig().getString("mysql.pass"));
    39. Statement stmt = conn.createStatement();
    40. ResultSet result = stmt.executeQuery(s);
    41. result.first();
    42. data = result.getString(b);
    43. stmt.close();
    44. conn.close();
    45. return data;
    46. }
    47. }


    and the error I am now getting is:

    Code:
    [10:11:26] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'a69cbc24-4908-44e8-9f01-e87f201471b0' in 'field list'
    [10:11:26] [Server thread/WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [10:11:26] [Server thread/WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.Util.getInstance(Util.java:382)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2407)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2325)
    [10:11:26] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2310)
    [10:11:26] [Server thread/WARN]:    at com.ps2j.mysql.MySQL.command(MySQL.java:21)
    [10:11:26] [Server thread/WARN]:    at com.ps2j.listener.PS2JPlayerListener.onFirstJoin(PS2JPlayerListener.java:28)
    [10:11:26] [Server thread/WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [10:11:26] [Server thread/WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at java.lang.reflect.Method.invoke(Unknown Source)
    [10:11:26] [Server thread/WARN]:    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:318)
    [10:11:26] [Server thread/WARN]:    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    [10:11:26] [Server thread/WARN]:    at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:486)
    [10:11:26] [Server thread/WARN]:    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:471)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.PlayerList.c(PlayerList.java:225)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.PlayerList.a(PlayerList.java:116)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.LoginListener.c(LoginListener.java:78)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.LoginListener.a(LoginListener.java:42)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:149)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:655)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:250)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:545)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:457)
    [10:11:26] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617)
     
  9. Offline

    DannyDog

    ShadowX90
    The INSERT INTO statement is wrong.
    It should be INSERT INTO table (fields) VALUES (values)
     
  10. Offline

    ShadowX90

  11. Offline

    macboinc

    By default, yes.
     
  12. Offline

    DannyDog

    Post what you changed in your code.
     
  13. Offline

    ShadowX90

    DannyDog
    Code:java
    1. package com.ps2j.listener;
    2.  
    3. import java.sql.SQLException;
    4.  
    5. import org.bukkit.entity.Player;
    6. import org.bukkit.event.EventHandler;
    7. import org.bukkit.event.Listener;
    8. import org.bukkit.event.player.PlayerJoinEvent;
    9.  
    10. import com.ps2j.PluginMain;
    11. import com.ps2j.mysql.MySQL;
    12.  
    13. public class PS2JPlayerListener implements Listener{
    14. PluginMain plugin;
    15. public PS2JPlayerListener(PluginMain instance) {
    16. instance.getServer().getPluginManager().registerEvents(this, instance);
    17. plugin = instance;
    18. }
    19.  
    20. @EventHandler
    21. public void onFirstJoin(PlayerJoinEvent event){
    22. MySQL mysql = new MySQL(plugin);
    23. Player player = event.getPlayer();
    24. String playeruuid = player.getUniqueId().toString();
    25. String playername = player.getDisplayName();
    26. try {
    27. mysql.command("INSERT INTO ps2j.players (`uuid`, `name`, `race`) VALUES (`" + playeruuid + "`, `" + playername +"`, `0`)");
    28. } catch (SQLException e) {
    29. e.printStackTrace();
    30. }
    31. }
    32. }
    33.  
     
  14. Offline

    DannyDog

    ShadowX90
    You dont need the ` for (uuid, name, race)
     
  15. Offline

    fireblast709

  16. Offline

    PandazNWafflez

    ShadowX90 While what you are doing can work, you aren't making proper use of the PreparedStatement class. As it stands, your method is vulnerable to SQL injection.

    You should change your command method to look like this:
    Code:java
    1. public void command(String s, String... args) throws SQLException {
    2. Connection conn = DriverManager.getConnection("jdbc:mysql://" + plugin.getConfig().getString("mysql.host") + ":" + plugin.getConfig().getString("mysql.port") + "/", plugin.getConfig().getString("mysql.user"), plugin.getConfig().getString("mysql.pass"));
    3. PreparedStatement QueryStatement = conn.prepareStatement(s);
    4. for (int i = 0; i < args.length; i++) {
    5. QueryStatement.setString(i, args);
    6. }
    7. QueryStatement.executeUpdate();
    8. QueryStatement.close();
    9. conn.close();
    10. }

    Then call it like this:
    Code:java
    1. mysql.command("INSERT INTO ps2j.players (`uuid`, `name`, `race`) VALUES (?, ?, `0`)", playeruuid, playername);

    This also goes for your other database methods.
     
  17. Offline

    ShadowX90

    PandazNWafflez
    Your code at QueryStatement.setString(i, args);
    is giving this error in eclipse:

    The method setString(int, String) in the type PreparedStatement is not applicable for the arguments (int, String[])
     
  18. Offline

    PandazNWafflez

    ShadowX90 Sorry, should be args[ i ] , not just args. Forum recognised it as an italics tag :p
     
  19. Offline

    ShadowX90

    PandazNWafflez
    Now I am getting
    Code:
    [13:05:20] [Server thread/WARN]: java.sql.SQLException: Parameter index out of range (0 < 1 ).
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3704)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3693)
    [13:05:20] [Server thread/WARN]:    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4544)
    [13:05:20] [Server thread/WARN]:    at com.ps2j.mysql.MySQL.command(MySQL.java:22)
    [13:05:20] [Server thread/WARN]:    at com.ps2j.listener.PS2JPlayerListener.onFirstJoin(PS2JPlayerListener.java:27)
    [13:05:20] [Server thread/WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [13:05:20] [Server thread/WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    [13:05:20] [Server thread/WARN]:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    [13:05:20] [Server thread/WARN]:    at java.lang.reflect.Method.invoke(Unknown Source)
    [13:05:20] [Server thread/WARN]:    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:318)
    [13:05:20] [Server thread/WARN]:    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    [13:05:20] [Server thread/WARN]:    at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:486)
    [13:05:20] [Server thread/WARN]:    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:471)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.PlayerList.c(PlayerList.java:225)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.PlayerList.a(PlayerList.java:116)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.LoginListener.c(LoginListener.java:78)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.LoginListener.a(LoginListener.java:42)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:149)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:655)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:250)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:545)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:457)
    [13:05:20] [Server thread/WARN]:    at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617)
     
  20. Offline

    PandazNWafflez

    ShadowX90 Whoops I messed that up :p

    QueryStatement.setString(i, args);

    Should be

    QueryStatement.setString(i + 1, args[ i ]);
     
  21. Offline

    ShadowX90

    PandazNWafflez It is pretty much working although it is entering my username into both fields and not entering uuid at all.
     
Thread Status:
Not open for further replies.

Share This Page