MySQL - Update Exisiting Row

Discussion in 'Plugin Development' started by thomasjcf21, Oct 28, 2013.

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

    thomasjcf21

    Hello I would like to make it so that when a user types in /voting thomasjcf21. It will post my name with 1 next to it into a MySQL database. (1 being the number of times I have voted). I would then like to so that if they do it again, it will check if my username exists and if it does it will add 1. How do I do that?

    My code is:
    Code:java
    1. package me.thomasjcf21.PEWI;
    2.  
    3. import java.sql.*;
    4.  
    5. import org.bukkit.Bukkit;
    6. import org.bukkit.ChatColor;
    7. import org.bukkit.command.Command;
    8. import org.bukkit.command.CommandSender;
    9. import org.bukkit.entity.Player;
    10. import org.bukkit.event.EventHandler;
    11. import org.bukkit.event.Listener;
    12. import org.bukkit.event.player.PlayerJoinEvent;
    13. import org.bukkit.event.player.PlayerQuitEvent;
    14. import org.bukkit.plugin.java.JavaPlugin;
    15.  
    16. public class PEWI extends JavaPlugin implements Listener{
    17.  
    18. public void onEnable(){
    19. System.out.println("PEWI Enabled!");
    20. Bukkit.getPluginManager().registerEvents(this, this);
    21. String url = "jdbc:mysql://localhost:3307/";
    22. String dbName = "PEWI";
    23. String driver = "com.mysql.jdbc.Driver";
    24. String userName = "root";
    25. String password = "";
    26. try {
    27. Class.forName(driver).newInstance();
    28. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    29. Statement st = conn.createStatement();
    30. st.executeUpdate("TRUNCATE online");
    31. st.executeUpdate("TRUNCATE players");
    32. int val = st.executeUpdate("INSERT into online VALUE(online)");
    33. if(val==1){
    34. conn.close();
    35. }
    36. } catch (Exception e) {
    37. e.printStackTrace();
    38. }
    39. }
    40.  
    41. public void onDisable(){
    42. System.out.println("PEWI Disabled!");
    43. String url = "jdbc:mysql://localhost:3307/";
    44. String dbName = "PEWI";
    45. String driver = "com.mysql.jdbc.Driver";
    46. String userName = "root";
    47. String password = "";
    48. try {
    49. Class.forName(driver).newInstance();
    50. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    51. Statement st = conn.createStatement();
    52. int val = st.executeUpdate("TRUNCATE online");
    53. int val1 = st.executeUpdate("TRUNCATE players");
    54. if(val==1 && val1==1){
    55. conn.close();
    56. }
    57. } catch (Exception e) {
    58. e.printStackTrace();
    59. }
    60. }
    61.  
    62. @EventHandler
    63. public void onPlayerJoin(PlayerJoinEvent event){
    64. String player = event.getPlayer().getDisplayName();
    65. String url = "jdbc:mysql://localhost:3307/";
    66. String dbName = "PEWI";
    67. String driver = "com.mysql.jdbc.Driver";
    68. String userName = "root";
    69. String password = "";
    70. try {
    71. Class.forName(driver).newInstance();
    72. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    73. Statement st = conn.createStatement();
    74. int val = st.executeUpdate("INSERT into players (player) VALUE('"+player+"')");
    75. if(val==1)
    76. conn.close();
    77. } catch (Exception e) {
    78. e.printStackTrace();
    79. }
    80. }
    81.  
    82. @EventHandler
    83. public void onPlayerQuit(PlayerQuitEvent event) {
    84. String player = event.getPlayer().getDisplayName();
    85. String url = "jdbc:mysql://localhost:3307/";
    86. String dbName = "PEWI";
    87. String driver = "com.mysql.jdbc.Driver";
    88. String userName = "root";
    89. String password = "";
    90. try {
    91. Class.forName(driver).newInstance();
    92. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    93. Statement st = conn.createStatement();
    94. int val = st.executeUpdate("DELETE FROM players WHERE player = '"+player+"' ");
    95. if(val==1){
    96. conn.close();
    97. }
    98. }catch (Exception e) {
    99. e.printStackTrace();
    100. }
    101. }
    102.  
    103. public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args){
    104. if(cmd.getName().equalsIgnoreCase("donator")){
    105. final Player player = (Player) sender;
    106. if(args.length !=2){
    107. player.sendMessage(ChatColor.BLUE + "[Donator] " + ChatColor.RED + "You have not used the correct arguments. /donator [name] [amount]");
    108. return true;
    109. }
    110. else{
    111. if(player.hasPermission("donator.add")){
    112. String url = "jdbc:mysql://localhost:3307/";
    113. String dbName = "PEWI";
    114. String driver = "com.mysql.jdbc.Driver";
    115. String userName = "root";
    116. String password = "";
    117. try {
    118. Class.forName(driver).newInstance();
    119. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    120. Statement st = conn.createStatement();
    121. st.executeUpdate("INSERT into donators (donator, price) VALUE('"+args[0]+"','"+args[1]+"')");
    122. conn.close();
    123. }catch (Exception e) {
    124. e.printStackTrace();
    125. }
    126. }
    127. else{
    128. player.sendMessage(ChatColor.BLUE + "[Donator] " + ChatColor.RED + "You do not have the correct permissions!");
    129. return true;
    130. }
    131. }
    132. }
    133. if(cmd.getName().equalsIgnoreCase("voting")){
    134. final Player player = (Player) sender;
    135. if(args.length !=1){
    136. player.sendMessage(ChatColor.BLUE + "[Voting] " + ChatColor.RED + "You have not used the correct arguments. /voting [name]");
    137. return true;
    138. }
    139. else{
    140. if(player.hasPermission("voting.add")){
    141. String url = "jdbc:mysql://localhost:3307/";
    142. String dbName = "PEWI";
    143. String driver = "com.mysql.jdbc.Driver";
    144. String userName = "root";
    145. String password = "";
    146. try {
    147. Class.forName(driver).newInstance();
    148. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    149. Statement st = conn.createStatement();
    150. ResultSet thomasjcf21 = st.executeQuery("SELECT * from voting where voter = '"+args[0]+"'");
    151. boolean var = thomasjcf21.next();
    152. if(var==false){
    153. st.executeUpdate("INSERT into voting (voter, no) VALUE('"+args[0]+"','1')");
    154. }else if(var==true){
    155. player.sendMessage("hello");
    156. }
    157. conn.close();
    158. }catch (Exception e) {
    159. e.printStackTrace();
    160. }
    161. }
    162. else{
    163. player.sendMessage(ChatColor.BLUE + "[Voting] " + ChatColor.RED + "You do not have the correct permissions!");
    164. return true;
    165. }
    166. }
    167. }
    168. return false;
    169. }
    170. }
    171.  
     
  2. Offline

    1Rogue

    Code:sql
    1. INSERT INTO `online` (`username`)
    2. VALUES ('example_user')
    3. ON DUPLICATE KEY UPDATE `online`.`online`=`online`.`online`+1;


    Also don't name a table and a column the same thing.
    This would also require that your username column has a unique key index (assuming there is a username column):
    Code:sql
    1. CREATE TABLE `online` (`id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(32) NOT NULL, `online` INT NOT NULL DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY (`username`));
     
  3. Offline

    thomasjcf21

    1Rogue

    It now says an error on the console the second time I run it.

    Code:java
    1. if(cmd.getName().equalsIgnoreCase("voting")){
    2. final Player player = (Player) sender;
    3. if(args.length !=1){
    4. player.sendMessage(ChatColor.BLUE + "[Voting] " + ChatColor.RED + "You have not used the correct arguments. /voting [name]");
    5. return true;
    6. }
    7. else{
    8. if(player.hasPermission("voting.add")){
    9. String url = "jdbc:mysql://localhost:3307/";
    10. String dbName = "PEWI";
    11. String driver = "com.mysql.jdbc.Driver";
    12. String userName = "root";
    13. String password = "";
    14. try {
    15. Class.forName(driver).newInstance();
    16. Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    17. Statement st = conn.createStatement();
    18. ResultSet thomasjcf21 = st.executeQuery("SELECT * from voting where voter = '"+args[0]+"'");
    19. boolean var = thomasjcf21.next();
    20. if(var==false){
    21. st.executeUpdate("INSERT into voting (voter, no) VALUE('"+args[0]+"','1')");
    22. }else if(var==true){
    23. st.executeUpdate("ON DUPLICATE KEY UPDATE `no`.`no`=`no`.`no`+1");
    24. player.sendMessage("hello");
    25. }
    26. conn.close();
    27. }catch (Exception e) {
    28. e.printStackTrace();
    29. }
    30. }
    31. else{
    32. player.sendMessage(ChatColor.BLUE + "[Voting] " + ChatColor.RED + "You do not have the correct permissions!");
    33. return true;
    34. }
    35. }
    36. }


    Error =

    2013-10-28 22:10:41 [INFO] thomasjcf21 issued server command: /voting thomasjcf21
    2013-10-28 22:10:50 [INFO] thomasjcf21 issued server command: /voting thomasjcf21
    2013-10-28 22:10:50 [SEVERE] 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 'ON DUPLICATE KEY UPDATE `no`.`no`=`no`.`no`+1' at line 1
    2013-10-28 22:10:50 [SEVERE]at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    2013-10-28 22:10:50 [SEVERE]at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    2013-10-28 22:10:50 [SEVERE]at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    2013-10-28 22:10:50 [SEVERE]at java.lang.reflect.Constructor.newInstance(Unknown Source)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.Util.getInstance(Util.java:382)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
    2013-10-28 22:10:50 [SEVERE]at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
    2013-10-28 22:10:50 [SEVERE]at me.thomasjcf21.PEWI.PEWI.onCommand(PEWI.java:155)
    2013-10-28 22:10:50 [SEVERE]at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
    2013-10-28 22:10:50 [SEVERE]at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:192)
    2013-10-28 22:10:50 [SEVERE]at org.bukkit.craftbukkit.v1_6_R3.CraftServer.dispatchCommand(CraftServer.java:523)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.PlayerConnection.handleCommand(PlayerConnection.java:959)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.PlayerConnection.chat(PlayerConnection.java:877)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.PlayerConnection.a(PlayerConnection.java:834)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.Packet3Chat.handle(SourceFile:49)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.NetworkManager.b(NetworkManager.java:296)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.PlayerConnection.e(PlayerConnection.java:116)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.ServerConnection.b(SourceFile:37)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.DedicatedServerConnection.b(SourceFile:30)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.MinecraftServer.t(MinecraftServer.java:592)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.DedicatedServer.t(DedicatedServer.java:227)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.MinecraftServer.s(MinecraftServer.java:488)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.MinecraftServer.run(MinecraftServer.java:421)
    2013-10-28 22:10:50 [SEVERE]at net.minecraft.server.v1_6_R3.ThreadServerApplication.run(SourceFile:583)
     
  4. Offline

    1Rogue

    What I wrote above is all one query, not 3.
     
Thread Status:
Not open for further replies.

Share This Page