Solved [SQL] Replacing old UUID rather than making new column?

Discussion in 'Plugin Development' started by AppleBabies, Jan 7, 2016.

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

    AppleBabies

    Back with more SQL! This time, I have found something I am stumped on - I can add UUIDs and usernames to a table, but when the UUID is changed, it doesn't replace the old one. INSTEAD, it actually seems to create a new column. So, how could I go upon monitoring this process? Thank you!

    EDIT: Actually, it's adding a new column no matter what - even if the UUID DOESN'T change...

    Code:
    package me.applebabies.ah.sql;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import org.bukkit.Bukkit;
    import org.bukkit.entity.Player;
    import org.bukkit.event.EventHandler;
    import org.bukkit.event.Listener;
    import org.bukkit.event.player.PlayerLoginEvent;
    import org.bukkit.plugin.java.JavaPlugin;
    
    public class SQLUUID implements Listener {
    
        public Connection connection;
        int previousLogins = 0;
        int ammo = 0;
      
        public synchronized void openConnection () {
            try {
                connection = DriverManager.getConnection("jdbc:mysql://198.100.144.199:3306/mc_2", "mc_2", "6168c377c9");
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
      
        public synchronized void closeConnection () {
            try {
                connection.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
      
        public synchronized boolean playerDataContainsPlayer(Player player) {
            try{
                PreparedStatement sql = connection.prepareStatement("SELECT * FROM `lobby_data` WHERE player=? AND WHERE name=?;");
                sql.setString(1, player.getUniqueId().toString());
                sql.setString(2, player.getName());
                ResultSet resultSet = sql.executeQuery();
                boolean containsPlayer = resultSet.next();
                sql.close();
                resultSet.close();
              
                return containsPlayer;
            } catch(SQLException e){
                e.printStackTrace();
                return false;
            }
        }
      
        public int getAmmo(Player p){
            openConnection();
            try{
          
                if(playerDataContainsPlayer(p)) {
                    PreparedStatement sql = connection.prepareStatement("SELECT ammo FROM `lobby_data` WHERE player=? AND WHERE name=?;");
                    sql.setString(1, p.getUniqueId().toString());
                    sql.setString(2, p.getName());
                  
                    ResultSet result = sql.executeQuery();
                    result.next();
                  
                    ammo = result.getInt("ammo");
                    sql.close();
                    result.close();
                  
                }
            } catch(SQLException ex) {
                ex.printStackTrace();
            } finally {
                closeConnection();
            }
            return ammo;
        }
      
        public void addAmmo(Player p, int amount){
            openConnection();
            try{
          
                if(playerDataContainsPlayer(p)) {
                    PreparedStatement sql = connection.prepareStatement("SELECT ammo FROM `lobby_data` WHERE player=? AND WHERE name=?;");
                    sql.setString(1, p.getUniqueId().toString());
                    sql.setString(2, p.getName());
                  
                    ResultSet result = sql.executeQuery();
                    result.next();
                  
                    ammo = result.getInt("ammo");
                    PreparedStatement moneyUpdate = connection.prepareStatement("UPDATE `lobby_data` SET ammo=? WHERE player=? AND WHERE name=?;");
                    moneyUpdate.setInt(1, ammo + amount);
                    moneyUpdate.setString(2, p.getUniqueId().toString());
                    moneyUpdate.setString(3, p.getName());
                    moneyUpdate.executeUpdate();
                    moneyUpdate.close();
                    sql.close();
                    result.close();
                  
                }
            } catch(SQLException ex) {
                ex.printStackTrace();
            } finally {
                closeConnection();
            }
        }
      
        public void addNewPlayer(Player p){
            try {
                PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `lobby_data` values(?,?,0,0,0);");
                newPlayer.setString(1, p.getUniqueId().toString());
                newPlayer.setString(2, p.getName());
                newPlayer.execute();
                newPlayer.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
      
        @EventHandler
        public void onPlayerJoin(PlayerLoginEvent e) {
            openConnection();
            try{
              
                if(playerDataContainsPlayer(e.getPlayer())) {
                    PreparedStatement sql = connection.prepareStatement("SELECT logins FROM `lobby_data` WHERE player=? AND WHERE name=?;");
                    sql.setString(1, e.getPlayer().getUniqueId().toString());
                    sql.setString(2, e.getPlayer().getName());
                  
                    ResultSet result = sql.executeQuery();
                    result.next();
                  
                    previousLogins = result.getInt("logins");
                    PreparedStatement moneyUpdate = connection.prepareStatement("UPDATE `lobby_data` SET logins=? WHERE player=? AND WHERE name=?;");
                    moneyUpdate.setInt(1, previousLogins + 1);
                    moneyUpdate.setString(2, e.getPlayer().getUniqueId().toString());
                    moneyUpdate.setString(3, e.getPlayer().getName());
                    moneyUpdate.executeUpdate();
                    moneyUpdate.close();
                    sql.close();
                    result.close();
                  
                } else {
                    addNewPlayer(e.getPlayer());
                }
            } catch(SQLException ex) {
                ex.printStackTrace();
            } finally {
                closeConnection();
                e.getPlayer().sendMessage("You now have " + previousLogins + " logins.");
            }
          
        }
      
      
      
    }
    
     
    Last edited: Jan 7, 2016
  2. Offline

    ProSl3nderMan

    I don't use SQL and neither do I know it, but looking at your code I don't see you checking if the SQL file has the player?
    Isn't there something like if (getSQL.contains(player))?
     
  3. Offline

    AppleBabies

    @ProSl3nderMan

    I do check all of that.
     
  4. Offline

    mythbusterma

    @AppleBabies

    Why would a UUID change? The idea is that they're unchanging. Unless you're in offline mode, which isn't supported here.

    Also, you still haven't fixed any of the issues from last time.
     
  5. Offline

    AppleBabies

    @mythbusterma Please look at my edit :) I made a silly mistake :p There's a different problem.
     
  6. Offline

    mythbusterma

    @AppleBabies

    Another silly mistake was not checking the console of the server when you try to log in. There's an error in your SQL syntax.
     
  7. Offline

    AppleBabies

    @mythbusterma Well, here's the syntax, but it's saying that the "name" column is having problems. I see no problem in my code with the name column being assigned.
    Code:
    07.01 21:00:52 [Server] WARN at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:486)
    07.01 21:00:52 [Server] WARN at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:501)
    07.01 21:00:52 [Server] WARN at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    07.01 21:00:52 [Server] WARN at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:300)
    07.01 21:00:52 [Server] WARN at java.lang.reflect.Method.invoke(Method.java:497)
    07.01 21:00:52 [Server] WARN at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    07.01 21:00:52 [Server] WARN at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    07.01 21:00:52 [Server] WARN at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    07.01 21:00:52 [Server] WARN at me.applebabies.ah.sql.SQLUUID.onPlayerJoin(SQLUUID.java:127)
    07.01 21:00:52 [Server] WARN at me.applebabies.ah.sql.SQLUUID.playerDataContainsPlayer(SQLUUID.java:43)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.Util.getInstance(Util.java:382)
    07.01 21:00:52 [Server] WARN at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    07.01 21:00:52 [Server] WARN at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    07.01 21:00:52 [Server] WARN at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    07.01 21:00:52 [Server] WARN at sun.reflect.GeneratedConstructorAccessor394.newInstance(Unknown Source)
    07.01 21:00:52 [Server] 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 'WHERE name='AppleBabies'' at line 1
     
  8. Offline

    mythbusterma

    @AppleBabies

    When there is a syntax error, it starts at the first erroring token and then includes the rest of the line, in this care it is saying the error is with "WHERE," because you only get one WHERE clause per SQL query.
     
  9. Offline

    AppleBabies

    Thi
    s is what it is saying is the problem. But shouldn't this work? I am defining two clauses per entry, aren't I?
     
  10. Offline

    Go Hard

    Is there a reason you're selecting the UUID and the players name? I have only ever got the UUID. I set the players name in the table so I know the players username but I don't see a need to get the players name and UUID. Just get the UUID and set whatever you're trying to set in the SQL.
     
  11. Offline

    mythbusterma

    @AppleBabies

    It's saying the second 'WHERE' clause is the problem, because that's the first token in the error message. Because
    Also, @Go Hard is right, this wouldn't have expected behaviour when someone changes their name.
     
  12. Offline

    AppleBabies

    @Go Hard UUIDs are self explanatory in why I have them. The player name is more of an organization thing, that's really the only purpose it serves.

    @mythbusterma OH. Sorry, I see what you are saying now. So, how can I define multiple clauses? I thought AND WHERE would work. Guess I am very wrong.
     
  13. Offline

    Hawktasard

    @AppleBabies
    You can do something like this:
    Code:
    SELECT something FROM somewhere WHERE <a> AND <b> AND <c>
     
  14. @AppleBabies
    SELECT *whatever u wanna select* FROM `Database` WHERE `Column`='Value',`Column2`='Value2'
    http://www.w3schools.com/sql/
    @AppleBabies The reason mojang created UUIDs is so people could change names without their stats changing. By specifying the name ánd UUID, when the player changes their name their stats will still be removed.
     
  15. Offline

    Go Hard

    @AppleBabies I didn't ask why you're using UUID. I know what UUID's are and how they work. I'm just saying put the players name and UUID in the table. That way you can know who the player is if you ever need to get the players name. Say you want to add 1 to the players login in the SQL. On the player login event get the players UUID from the SQL and add 1. No need to get the players UUID and name. That's where you're getting your error.

    So instead of using this:
    Code:
    PreparedStatement sql = connection.prepareStatement("SELECT * FROM `lobby_data` WHERE player=? AND WHERE name=?;");
    
    sql.setString(1, player.getUniqueId().toString());
    sql.setString(2, player.getName());
    
    Use this:
    Code:
    PreparedStatement sql = connection.prepareStatement("SELECT * FROM `lobby_data` WHERE player=?;");
    
    sql.setString(1, player.getUniqueId().toString());
    
     
  16. Offline

    TheWolfBadger

    Could be wrong, but shouldn't you start from 0 in setting the strings? That's like a given usually. I could be wrong, haven't used SQL methods in a while.
     
  17. Offline

    Go Hard

    TheWolfBadger likes this.
  18. Offline

    AppleBabies

    @Go Hard Okay, that works. But now, when a person changes their name, it adds a new column, thus getting rid of ALL their data. How can I simply just update their username?

    The only thing I use for names is: INSERT INTO `lobby_data` values(?,?,0,0,0); (uuid, name, logins, ammo, money)
    And never again do I even mention the name column.

    EDIT: This may still be a problem, but I haven't noticed it at the moment. My friend actually just changed his name on a SECOND account. Marking as solved. Thanks all!
     
Thread Status:
Not open for further replies.

Share This Page