Mysql

import com.google.common.collect.Lists;
import com.mysql.jdbc.Connection;
import org.apollodevs.smashheroes.SmashHeroes;
import org.bukkit.configuration.ConfigurationSection;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * MySQL Database management utility
 */
public class MySQLDatabase {
    private static ExecutorService executeThread = Executors.newSingleThreadExecutor(), prepareThread = Executors.newSingleThreadExecutor(), runnableThread = Executors.newSingleThreadExecutor();
    public String table;
    private String host;
    private String username;
    private String password;
    private String database;
    private int timeout = 10000;
    private Connection con;

    /**
     * Initialize and load MySQL settings from the config
     */
    public MySQLDatabase() {
        ConfigurationSection c = SmashHeroes.kf.getConfigurationSection("mysql");
        host = c.getString("host");
        username = c.getString("username");
        password = c.getString("password");
        database = c.getString("database");
        table = c.getString("table");
        timeout = c.getInt("timeout");
        prepareThread.submit(new MySQLPrepare(Lists.newArrayList("CREATE TABLE IF NOT EXISTS `" + table + "` (`uuid` TINYTEXT, `key` TEXT, `value` TEXT)"), null));
    }

    public static String escape(String in) {
        StringBuilder out = new StringBuilder();
        for (char c : in.toCharArray()) {
            switch (c) {
                case '\u0000':
                    out.append("\\0");
                    break;
                case '\u001a':
                    out.append("\\Z");
                    break;
                case '\n':
                    out.append("\\n");
                    break;
                case '\r':
                    out.append("\\r");
                    break;
                case '\'':
                    out.append("\\'");
                    break;
                case '"':
                    out.append("\\\"");
                    break;
                case '\\':
                    out.append("\\\\");
                    break;
                default:
                    out.append(c);
            }
        }
        return out.toString();
    }

    /**
     * Execute a MySQL command from the current thread
     *
     * @param cmd - The MySQL command
     * @return The success of the command execution
     */
    public boolean command(String cmd) {
        PreparedStatement st;
        try {
            st = getConnection().prepareStatement(cmd);
            return st.execute();
        } catch (Throwable ex) {
            ex.printStackTrace();
        }
        return false;
    }

    /**
     * Get the data of a player from the database.
     * This process will took long time, so use an
     * async thread for calling this method
     *
     * @param uid - The UUID of the player
     * @return The map with the player data loaded from the database
     */
    public HashMap<String, String> get(UUID uid) {
        HashMap<String, String> out = new HashMap<>();
        try {
            ResultSet rs = querry("SELECT `key`,`value` FROM `" + table + "` WHERE `uuid` = \"" + escape(uid.toString()) + "\" LIMIT 1");
            while (rs.next()) {
                out.put(rs.getString(1), rs.getString(2));
            }
        } catch (Throwable e) {
            e.printStackTrace();
        }
        return out;
    }

    private Connection getConnection() {
        try {
            if (this.con == null || !this.con.isValid(timeout)) {
                this.openConnection();
            }
        } catch (Throwable e) {
            e.printStackTrace();
        }
        return this.con;
    }

    /**
     * Opens connection to the MySQL
     *
     * @return The success of the connection opening
     */
    public boolean openConnection() {
        try {
            System.out.println("[Smash Heroes] Connecting to server database!");
            this.con = (Connection) DriverManager.getConnection("jdbc:mysql://" + this.host + "/" + this.database + "?autoReconnect=true", this.username, this.password);
            this.con.setAutoReconnect(true);
            this.con.setConnectTimeout(timeout);
            System.out.println("[Smash Heroes] Connected to mySQL!");
        } catch (Throwable e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * Executes a querry request from the current thread
     *
     * @param cmd - The querry command
     * @return The result of the querry
     */
    public ResultSet querry(String cmd) {
        ResultSet rs;
        PreparedStatement st;
        try {
            st = getConnection().prepareStatement(cmd);
            rs = st.executeQuery();
            return rs;
        } catch (Throwable ex) {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * Remove a player from the database, feel free to use this method
     * from any kind of thread, because it executes fast
     *
     * @param uid       - The UUID of the removable player
     * @param afterDone - An optional runnable which will be executed after the
     *                  remove process is done
     */
    public void remove(UUID uid, Runnable afterDone) {
        prepareThread.submit(new MySQLPrepare(Lists.newArrayList("\"DELETE FROM `\"+ table+\" WHERE `uuid` = \"" + escape(uid.toString()) + "\""), afterDone));
    }

    /**
     * Set the data of the given player in the MySQL, feel free to use this method
     * from any kind of thread, because it executes fast
     *
     * @param uid       - The UUID of the setable player
     * @param data      - The saveable data
     * @param afterDone - An optional runnable which will be executed after the
     *                  remove process is done
     */
    public void set(UUID uid, HashMap<String, String> data, Runnable afterDone) {
        ArrayList<String> cmds = new ArrayList<>();
        String uis = escape(uid.toString());
        cmds.add("DELETE FROM `" + table + "` WHERE `uuid`=\"" + uis + "\"");
        for (Map.Entry<String, String> e : data.entrySet()) {
            cmds.add("INSERT INTO `" + table + "` VALUES (\"" + uis + "\", \"" + escape(e.getKey()) + "\", \"" + escape(e.getValue()) + "\")");
        }
        prepareThread.submit(new MySQLPrepare(cmds, afterDone));
    }

    /**
     * Executes an update request from the current thread
     *
     * @param cmd - The update command
     * @return The number of updated lines
     */
    public int update(String cmd) {
        PreparedStatement st;
        try {
            st = getConnection().prepareStatement(cmd);
            int out = st.executeUpdate();
            return out;
        } catch (Throwable ex) {
            ex.printStackTrace();
            return -1;
        }
    }

    /**
     * The runnable for thread used for executing MySQL statements
     */
    public class MySQLExecute implements Runnable {
        private final Statement st;
        private final Runnable r;

        public MySQLExecute(Statement st, Runnable r) {
            this.st = st;
            this.r = r;
        }

        public void run() {
            try {
                st.executeBatch();
                if (r != null)
                    runnableThread.submit(r);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * The runnable for thread used to prepare MySQL statements from String commands
     */
    public class MySQLPrepare implements Runnable {
        private final Iterable<String> ps;
        private final Runnable r;

        public MySQLPrepare(Iterable<String> cmds, Runnable r) {
            this.ps = cmds;
            this.r = r;
        }

        public void run() {
            try {
                Statement st = getConnection().createStatement();
                for (String s : ps) {
                    st.addBatch(s);
                }
                executeThread.submit(new MySQLExecute(st, r));
            } catch (Throwable e) {
                e.printStackTrace();
            }
        }
    }
}