org.aroyomc.nexus.strikes.utils.SqlManager.java Source code

Introduction

Here is the source code for org.aroyomc.nexus.strikes.utils.SqlManager.java

Source

/*
 * NexusStrikes
 *     Copyright (C) 2014 AroyoMC
 *
 *     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 org.aroyomc.nexus.strikes.utils;

import java.sql.*;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ForkJoinPool;

import io.github.pieguy128.corelib.shaded.org.apache.commons.dbutils.AsyncQueryRunner;
import io.github.pieguy128.corelib.shaded.org.apache.commons.dbutils.DbUtils;
import io.github.pieguy128.corelib.shaded.org.apache.commons.dbutils.handlers.ArrayHandler;
import io.github.pieguy128.corelib.shaded.org.apache.commons.dbcp2.BasicDataSource;

public class SqlManager {

    // DB info...
    private final AsyncQueryRunner query;
    private final BasicDataSource source;

    // SQL commands...
    private final String createTables = "CREATE TABLE IF NOT EXISTS strikes (" + "playerId VARCHAR NOT NULL, "
            + "strikes ARRAY, " + "PRIMARY KEY (playerId)" + ");";

    private final String updateStrikes = "UPDATE strikes SET strikes = ? WHERE playerId = ?;";
    private final String firstStrike = "INSERT INTO strikes VALUES (?, ?);";
    private final String getStrikes = "SELECT strikes FROM strikes WHERE playerId = ?;";

    // Connection singleton
    private Connection connection;

    /**
     *
     * @param driver Location of the class that provides the SQL driver
     * @param url URL of the SQL server
     * @param username SQL server username
     * @param password SQL server password
     * @throws SQLException When an issue occurs while connecting
     */
    public SqlManager(String driver, String url, String username, String password) throws SQLException {
        source = new BasicDataSource();
        source.setDriverClassName(driver);
        source.setUrl(url);
        source.setUsername(username);
        source.setPassword(password);

        query = new AsyncQueryRunner(new ForkJoinPool());
    }

    /**
     * Open the connection to the SQL server
     * @throws SQLException When an issue occurs while connecting
     */
    protected void openConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = source.getConnection();
            connection.setAutoCommit(false);
        }
    }

    public void createTables() throws SQLException, ExecutionException, InterruptedException {
        openConnection();
        try {
            query.update(connection, createTables).get();
        } finally {
            connection.commit();
        }

    }

    /**
     *
     * @param playerId UUID of the player
     * @param reason Reason for the strike
     */
    public void firstStrike(UUID playerId, String reason)
            throws SQLException, ExecutionException, InterruptedException {
        /*
        PreparedStatement stmt = connection.prepareStatement(firstStrike);
        stmt.setString(1, playerId.toString());
        stmt.setArray(2, connection.createArrayOf("VARCHAR", new String[]{reason}));
        stmt.executeUpdate();
        */
        openConnection();
        try {
            query.update(connection, firstStrike, new Object[] { playerId.toString(),
                    connection.createArrayOf("VARCHAR", new String[] { reason }) }).get();
        } finally {
            connection.commit();
        }
    }

    /**
     *
     * @param playerId UUID of the player
     * @param reason Reason for the strike
     */
    public void updateStrikes(UUID playerId, String reason)
            throws SQLException, ExecutionException, InterruptedException {

        //final List<String> arrayResultList = Arrays.asList( (String[]) getStrikes(playerId).getArray("strikes").getArray());
        final List<String> arrayResultList = Arrays.asList(getStrikes(playerId));
        arrayResultList.add(reason);

        /*
        PreparedStatement stmt = connection.prepareStatement(updateStrikes);
        stmt.setString(1, playerId.toString());
        stmt.setArray(2, connection.createArrayOf("VARCHAR", arrayResultList.toArray()));
        stmt.executeUpdate();
        */
        try {
            query.update(connection, updateStrikes, new Object[] { playerId.toString(),
                    connection.createArrayOf("VARCHAR", arrayResultList.toArray()) }).get();
        } finally {
            connection.commit();
        }
    }

    /**
     *
     * @param playerId UUID of the player
     */
    public String[] getStrikes(UUID playerId) throws SQLException, ExecutionException, InterruptedException {
        /*
        PreparedStatement stmt = connection.prepareStatement(getStrikes);
        stmt.setString(1, playerId.toString());
        return stmt.executeQuery();
        */
        openConnection();
        String[] result;

        try {
            result = (String[]) query
                    .query(connection, getStrikes, new ArrayHandler(), new Object[] { playerId.toString() }).get();
        } finally {
            connection.commit();
        }
        return result;
    }

    /**
     * Close the connection, committing any changes first
     */
    public void close() throws SQLException {
        DbUtils.commitAndClose(connection);
        source.close();
    }

    @Override
    protected void finalize() throws Throwable {
        try {
            close();
        } finally {
            super.finalize();
        }
    }
}