MySQL Manager

package com.bimmr.mcinfected.MySQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.UUID;
import java.util.logging.Level;

import org.bukkit.plugin.Plugin;

import com.bimmr.mcinfected.SettingsManager;


public class MySQLManager {

    private Connection  connection;
    private MySQL               mysql;
    private Statement       statement;

    /**
     * Create a mysql connection
     *
     * @param plugin
     * @param host
     * @param port
     * @param database
     * @param username
     * @param password
     */
    public MySQLManager(Plugin plugin, String host, String port, String database, String username, String password)
    {
        this.mysql = new MySQL(plugin, host, port, database, username, password);
        openConnection();
    }

    /**
     * Closes the connection
     *
     * @throws SQLException
     */
    public void closeConnection() {
        if (SettingsManager.getDebugMode())
            System.out.println("Closing MySQL Connection");
        if (this.connection != null)
            try
            {
                this.connection.close();
            }
            catch (SQLException e)
            {

            }
        this.connection = null;
    }

    /**
     * Closes the statement
     *
     * @throws SQLException
     */
    public void closeStatement() {
        if (SettingsManager.getDebugMode())
            System.out.println("Closing MySQL Statement");
        if (this.statement != null)
            try
            {
                this.statement.close();
            }
            catch (SQLException e)
            {

            }
        this.statement = null;
    }

    /**
     * Execute an update
     *
     * @param string
     * @throws SQLException
     */
    public void execute(String string) throws SQLException {

        openStatement();

        this.statement.execute(string);

        closeStatement();

    }

    /**
     * First columns is the player's UUID, so you don't need that For example:
     * <p>
     * "Kills INT(10), Deaths INT(10), Points INT(10), Score INT(10), PlayingTime INT(15), HighestKillStreak INT(10)"
     * <p>
     * ^ Would check if a table with all that exists, and if not it creates it
     *
     * @param table
     * @param mySqlColoumData
     * @throws SQLException
     */
    public void createTableIfDoesntExist(String tableName, String mySqlColoumData) throws SQLException {

        openStatement();

        this.statement.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " (UUID VARCHAR(40), " + mySqlColoumData + ");");

        closeStatement();

    }

    /**
     * Assumes all players are in columns 1
     *
     * @param tableName
     *          - The tables name
     * @return All the players in the table
     */
    public ArrayList<UUID> getAllPlayers(String tableName) {

        try
        {
            if (SettingsManager.getDebugMode())
                System.out.println("Getting all UUIDs");
            openStatement();
            ResultSet set = this.statement.executeQuery("SELECT * FROM `" + tableName + "` ");
            ArrayList<UUID> players = new ArrayList<UUID>();
            while (true)
            {
                set.next();
                players.add(UUID.fromString(set.getString("UUID")));
                if (set.isLast())
                    break;
            }
            set.close();
            closeStatement();
            return players;
        }
        catch (SQLException e)
        {
            closeStatement();
            ArrayList<UUID> nope = new ArrayList<UUID>();
            return nope;
        }

    }

    /**
     * Get the value of the column
     *
     * @param tableName
     *          - The tables name
     * @param columnName
     *          - The stats name
     * @param uuid
     * @return the players stats
     * @throws SQLException
     */
    public int getInt(String tableName, String columnName, UUID uuid) {

        try
        {
            if (SettingsManager.getDebugMode())
                System.out.println("Getting value from " + tableName + " - " + uuid + "- " + columnName);

            openStatement();
            ResultSet set = this.statement.executeQuery("SELECT " + columnName + " FROM " + tableName + " WHERE UUID = '" + uuid.toString() + "';");

            set.next();

            int i = set.getInt(columnName);
            set.close();
            closeStatement();
            return i;
        }
        catch (SQLException e)
        {
            closeStatement();
            return 0;
        }
    }

    public String getString(String tableName, String columnName, UUID uuid) {

        try
        {
            if (SettingsManager.getDebugMode())
                System.out.println("Getting value from " + tableName + " - " + uuid + "- " + columnName);

            openStatement();
            ResultSet set = this.statement.executeQuery("SELECT " + columnName + " FROM " + tableName + " WHERE UUID = '" + uuid.toString() + "';");

            set.next();

            String s = set.getString(columnName);
            set.close();
            closeStatement();
            return s;
        }
        catch (SQLException e)
        {
            closeStatement();
            return "";
        }
    }

    /**
     * Gets if it has a open connection
     *
     * @return
     */
    public boolean hasOpenConnection() {
        return this.connection != null;
    }

    /**
     * Gets if it has an open statement
     *
     * @return
     */
    public boolean hasOpenStatement() {
        return this.statement != null;
    }

    /**
     * Opens a new connection
     */
    public void openConnection() {
        if (SettingsManager.getDebugMode())
            System.out.println("Opening MySQL Connection");
        this.connection = this.mysql.openConnection();
    }

    /**
     * Opens a new statement
     *
     * @throws SQLException
     */
    public void openStatement() {
        if (SettingsManager.getDebugMode())
            System.out.println("Opening MySQL Statement");
        try
        {
            this.statement = this.connection.createStatement();
        }
        catch (SQLException e)
        {

        }
    }

    private void setInt(String tableName, String columnName, int value, UUID uuid) {

        try
        {
            if (SettingsManager.getDebugMode())
                System.out.println("Setting value into " + tableName + " - " + uuid + "- " + columnName + " - " + value);
            openStatement();
            this.statement.execute("INSERT INTO " + tableName + " (`UUID`, `" + columnName + "`) VALUES ('" + uuid + "', '" + value + "');");
            closeStatement();
        }
        catch (SQLException e)
        {
            closeStatement();
        }
    }

    private void setString(String tableName, String columnName, String value, UUID uuid) {

        try
        {
            if (SettingsManager.getDebugMode())
                System.out.println("Setting value into " + tableName + " - " + uuid + "- " + columnName + " - " + value);
            openStatement();
            this.statement.execute("INSERT INTO " + tableName + " (`UUID`, `" + columnName + "`) VALUES ('" + uuid + "', '" + value + "');");
            closeStatement();
        }
        catch (SQLException e)
        {
            closeStatement();
        }
    }

    public void unload() throws SQLException {
        closeStatement();
        closeConnection();
    }

    /**
     * Safely update/set the value Will set the value only if the table doesn't
     * have the player already, otherwise it'll just update the players values
     *
     * @param tableName
     * @param columnName
     * @param value
     * @param uuid
     */
    public void updateInt(String tableName, String columnName, int value, UUID uuid) {

        if (getAllPlayers(tableName).contains(uuid))
        {
            try
            {
                if (SettingsManager.getDebugMode())
                    System.out.println("Updating value into " + tableName + " - " + uuid + "- " + columnName + " - " + value);
                openStatement();
                this.statement.execute("UPDATE " + tableName + " SET " + columnName + "=" + value + " WHERE UUID ='" + uuid + "';");
                closeStatement();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                closeStatement();
                setInt(tableName, columnName, value, uuid);
            }
        }
        else
        {
            setInt(tableName, columnName, value, uuid);
        }

    }

    public void updateString(String tableName, String columnName, String value, UUID uuid) {

        if (getAllPlayers(tableName).contains(uuid))
        {
            try
            {
                if (SettingsManager.getDebugMode())
                    System.out.println("Updating value into " + tableName + " - " + uuid + "- " + columnName + " - " + value);
                openStatement();
                this.statement.execute("UPDATE " + tableName + " SET " + columnName + "=" + value + " WHERE UUID ='" + uuid + "';");
                closeStatement();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                closeStatement();
                setString(tableName, columnName, value, uuid);
            }
        }
        else
        {
            setString(tableName, columnName, value, uuid);
        }

    }

    public class MySQL {

        private Connection      connection;
        private final String    database;
        private final String    hostname;
        private final String    password;
        private Plugin              plugin;

        private final String    port;

        private final String    user;

        /**
         * Creates a new MySQL instance
         *
         * @param plugin
         *          Plugin instance
         * @param hostname
         *          Name of the host
         * @param port
         *          Port number
         * @param database
         *          Database name
         * @param username
         *          Username
         * @param password
         *          Password
         */
        public MySQL(Plugin plugin, String hostname, String port, String database, String username, String password)
        {
            this.plugin = plugin;
            this.hostname = hostname;
            this.port = port;
            this.database = database;
            this.user = username;
            this.password = password;
            this.connection = null;
        }

        public boolean checkConnection() {
            return this.connection != null;
        }

        public void closeConnection() {
            if (this.connection != null)
                try
                {
                    this.connection.close();
                }
                catch (SQLException e)
                {
                    this.plugin.getLogger().log(Level.SEVERE, "Error closing the MySQL Connection!");
                    e.printStackTrace();
                }
        }

        public Connection getConnection() {
            return this.connection;
        }

        public Connection openConnection() {
            try
            {
                Class.forName("com.mysql.jdbc.Driver");
                this.connection = DriverManager.getConnection("jdbc:mysql://" + this.hostname + ":" + this.port + "/" + this.database, this.user, this.password);
            }
            catch (SQLException e)
            {
                this.plugin.getLogger().log(Level.SEVERE, "Could not connect to MySQL server! because: " + e.getMessage());
            }
            catch (ClassNotFoundException e)
            {
                this.plugin.getLogger().log(Level.SEVERE, "JDBC Driver not found!");
            }
            return this.connection;
        }

        public ResultSet querySQL(String query) {
            Connection c = null;

            if (checkConnection())
                c = getConnection();
            else
                c = openConnection();

            Statement s = null;

            try
            {
                s = c.createStatement();
            }
            catch (SQLException e1)
            {
                e1.printStackTrace();
            }

            ResultSet ret = null;

            try
            {
                ret = s.executeQuery(query);
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }

            closeConnection();

            return ret;
        }

        public void updateSQL(String update) {

            Connection c = null;

            if (checkConnection())
                c = getConnection();
            else
                c = openConnection();

            Statement s = null;

            try
            {
                s = c.createStatement();
                s.executeUpdate(update);
            }
            catch (SQLException e1)
            {
                e1.printStackTrace();
            }

            closeConnection();

        }
    }
}