To get an idea of what the basic structure looks like, I downloaded a money system including MySQL from Spigot and looked at the code.

public static boolean playerExists(String uuid) {
    try {
      ResultSet rs = Simplecoinsystem.mysql.query("SELECT * FROM CoinData WHERE UUID= '"   uuid   "'");
      if (rs.next())
        return (rs.getString("UUID") != null); 
      return false;
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    } 
}
  
public static void createPlayer(String uuid) {
    if (!playerExists(uuid))
      Simplecoinsystem.mysql.update("INSERT INTO CoinData (UUID, COINS) VALUES ('"   uuid   
          "', '"   Simplecoinsystem.getInstance().getConfig().getInt("startcoins")   "');"); 
}
  
public static Integer getCoins(String uuid) {
    Integer i = Integer.valueOf(0);
    if (playerExists(uuid)) {
      try {
        ResultSet rs = Simplecoinsystem.mysql.query("SELECT * FROM CoinData WHERE UUID= '"   uuid   "'");
        if (rs.next())
          Integer.valueOf(rs.getInt("COINS")); 
        i = Integer.valueOf(rs.getInt("COINS"));
      } catch (SQLException e) {
        e.printStackTrace();
      } 
    } else {
      createPlayer(uuid);
    } 
    return i;
}
  
public static void setCoins(String uuid, Integer coins) {
    if (playerExists(uuid)) {
      Simplecoinsystem.mysql.update("UPDATE CoinData SET COINS= '"   coins   "' WHERE UUID= '"   uuid   "';");
    } else {
      createPlayer(uuid);
    } 
}

Am I correct that it is actually impractical to create a new entry with the uuid of the non-existent player after each query of the coins if the player does not exist?

Wouldn't this make it possible to flood the database with thousands of unnecessary entries by issuing, for example, a "/money (player)" command as an evil player/admin?

Couldn't I just ask when entering the server if the uuid is already stored and if not, just enter it? This way there would only be entries from players who have already been on the server before. Whether this needs great server performance, I'm not sure.

This is my first own MySQL class.

public class MySQL {
    
    private String host, database, user, password;
    private int port;
    
    private Connection con;
    
    
    public MySQL(String host, int port, String database, String user, String password) {
        this.host = host;
        this.port = port;
        this.database = database;
        this.user = user;
        this.password = password;
        
        connect();
    }
    
    public void connect() {
        try {
            con = DriverManager.getConnection("jdbc:mysql://"   host   ":"   port   "/"   database   "?autoReconnect=true", user, password);
            System.out.println("&cDie MySQL Verbindung wurde erfolgreich aufgebaut!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public void disconnect() {
        try {
            if(this.con != null) {
                this.con.close();
                System.out.println("§cDie MySQL Verbindung wurde erfolgreich beendet!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void update(String query) {
        try {
            Statement st = con.createStatement();
            st.executeUpdate(query);
            st.close();
        } catch (Exception e) {
            e.printStackTrace();
            connect();
        }
    }
    
    public ResultSet qry(String query) {
        ResultSet rs = null;
        
        try {
            Statement st = con.createStatement();
            rs = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
            connect();
        }
        return rs;
    }
    
    public Connection getConnection() {
        return this.con;
    }

}

Except for this part, both MySQL classes are built relatively the same. This is the part that is in the MySQL class of the Spigot plugin.

CodePudding user response：

Your code have multiple issues.

1. When the connection will be closed, next time you will have an error. In your Mysql class, I suggest you to do:

public Connection getConnection() {
   if(con == null || con.isClosed())
      connect();
   return con;
}

Then, use it in all method like getConnection().prepareStatement().

2. You can be attacked with SQL Injection. To fix this, try to do something like:

PreparedStatement st = con.prepareStatement("SELECT * FROM CoinData WHERE UUID = ?");
st.setString(1, uuid.toString()); // Yes it start at 1 !!
st.executeUpdate();

With this, even with all values, you can't be attacked with injections.

3. You will have an error while getting coins:

if (rs.next()) // go to good line
   Integer.valueOf(rs.getInt("COINS")); // useless convertion
i = Integer.valueOf(rs.getInt("COINS")); // error if no line.

You can just do:

if(rs.next())
   i = rs.getInt("COINS");

4. If the column "UUID" is unique, you will not have duplicated lines.
5. Finally, about performance, it's better to do it one time: at login, instead of all time. You can also create an object stored in an hashmap to easier access to it, without using SQL, like that:

public static HashMap<UUID, Integer> coinsByPlayer = new HashMap<>();

OR:

public static HashMap<UUID, MyObject> coinsByPlayer = new HashMap<>();

public class MyObject {

   private int coins = 0;

   public MyObject(UUID uuid) {
       // make SQL request to get data

   }

   public int getCoins() {
       return coins;
   }

   public void setCoins(int next){
       coins = next;
       // here make "UPDATE" sql query
   }
}

CodePudding user response：

What do you say? Is it ok with the try/catch function? @Elikill58

public Connection getConnection() {
    try {
        if(con == null || con.isClosed()) {
            connect();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return con;
}