MySQL Manager v4.java

/*
 * Copyright (C) 2014 Nicholas Badger
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.ferusgrim.grimlist.manager;

import com.ferusgrim.grimlist.GrimList;
import com.ferusgrim.grimlist.manager.factory.ManagerBase;

import java.sql.*;
import java.util.List;
import java.util.UUID;

public class MySQLManager extends ManagerBase {

    private final GrimList plugin;

    private String host;
    private String database;
    private String username;
    private String password;
    private int port;
    private boolean usingPassword;

    private String TABLE;
    private String INSERT;
    private String DELETE;
    private String QUERY;
    private String WHITELIST;

    public MySQLManager(GrimList plugin) {
        super("MySQL",
            plugin.getConfig().getInt("MySQL.Update-Interval"),
            plugin.getConfig().getBoolean("General.Use-Whitelist"));
        this.plugin = plugin;
    }

    @Override
    public void start() {
        host = plugin.getConfig().getString("MySQL.Host");
        database = plugin.getConfig().getString("MySQL.Database");
        username = plugin.getConfig().getString("MySQL.Username");
        password = plugin.getConfig().getString("MySQL.Password");
        port = plugin.getConfig().getInt("MySQL.Port");
        usingPassword = password != null && !password.isEmpty();

        TABLE = plugin.getConfig().getString("MySQL.TABLE");
        INSERT = plugin.getConfig().getString("MySQL.INSERT");
        DELETE = plugin.getConfig().getString("MySQL.DELETE");
        QUERY = plugin.getConfig().getString("MySQL.QUERY");
        WHITELIST = plugin.getConfig().getString("MySQL.WHITELIST");

        setupTable();
    }

    @Override
    public void stop() {
        serverSync();
    }

    @Override
    public void serverSync() {
        processQueue(INSERT, getAddList());
        processQueue(DELETE, getRemoveList());
        clearQueues();
        pullFromServer();
    }

    @Override
    public boolean isWhitelistedInSource(UUID uuid) {
        Connection c = null;
        PreparedStatement s = null;
        ResultSet r = null;
        try {
            c = getConn();
            s = c.prepareStatement(WHITELIST);
            s.setString(1, uuid.toString());
            r = s.executeQuery();
            return r.isBeforeFirst() && r.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(c, s, r);
        }
        return false;
    }

    private void setupTable() {
        Connection c = null;
        PreparedStatement s = null;
        try {
            c = getConn();
            s = c.prepareStatement(TABLE);
            s.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(c, s);
        }
    }

    private void processQueue(String sql, List<UUID> queue) {
        Connection c = null;
        PreparedStatement s = null;
        try {
            c = getConn();
            s = c.prepareStatement(sql);
            for (UUID uuid : queue) {
                s.setString(1, uuid.toString());
                s.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(c, s);
        }
    }

    private void pullFromServer() {
        Connection c = null;
        PreparedStatement s = null;
        ResultSet r = null;
        try {
            c = getConn();
            s = c.prepareStatement(QUERY);
            r = s.executeQuery();
            if (r.isBeforeFirst()) {
                while (r.next()) {
                    final UUID uuid = UUID.fromString(r.getString(1));
                    if (!isWhitelisted(uuid)) {
                        add(uuid);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(c, s, r);
        }
    }

    private Connection getConn() {
        try {
            return DriverManager.getConnection
                (
                    "jdbc:mysql://" + host + ":" + port + "/" + database +
                        "?autoReconnect=true&user=" + username +
                        (
                            usingPassword ? "&password=" + password : ""
                        )
                );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void close(Connection c, Statement s, ResultSet r) {
        try {
            if (c != null) c.close();
            if (s != null) s.close();
            if (r != null) r.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void close(Connection c, Statement s) {
        close(c, s, null);
    }
}