Empire Minecraft DB Wrapper - EmpireDb - Released as MIT - Ignore the copyright headers

AsyncDbQueue.java

/*
 * Copyright (c) 2015. Starlis LLC / dba Empire Minecraft
 *
 * This source code is proprietary software and must not be redistributed without Starlis LLC's approval
 *
 */

package com.empireminecraft.systems.db;

import com.empireminecraft.util.Util;

import java.sql.SQLException;
import java.util.Queue;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class AsyncDbQueue implements Runnable {
    private static final Queue<AsyncDbStatement> queue = new ConcurrentLinkedQueue<>();
    private static final Lock lock = new ReentrantLock();

    @Override
    public void run() {
        processQueue();
    }

    public static void processQueue() {
        if (queue.isEmpty() || !lock.tryLock()) {
            return;
        }

        AsyncDbStatement stm = null;
        DbStatement dbStatement;

        try {
            dbStatement = new DbStatement();
        } catch (Exception e) {
            lock.unlock();
            Util.printException("Exception getting DbStatement in AsyncDbQueue", e);
            return;
        }

        while ((stm = queue.poll()) != null) {
            try {
                if (dbStatement.isClosed()) {
                    dbStatement = new DbStatement();
                }
                stm.process(dbStatement);
            } catch (SQLException e) {
                stm.onError(e);
            }
        }
        dbStatement.close();
        lock.unlock();
    }

    public static boolean queue(AsyncDbStatement stm) {
        return queue.offer(stm);
    }

}

AsyncDbStatement.java

/*
 * Copyright (c) 2015. Starlis LLC / dba Empire Minecraft
 *
 * This source code is proprietary software and must not be redistributed without Starlis LLC's approval
 *
 */

package com.empireminecraft.systems.db;

import com.empireminecraft.util.Util;

import java.sql.SQLException;

/**
 * Template class for user to override. Will run on a different thread so
 * you can run SQL queries safely without impacting main thread.
 * <p/>
 * Will automatically close the connection once run() is done!
 * <p/>
 * Calls onError when a SQLException is fired, and provides
 * an onResultsSync method to be overridden to receive all DB Results back on main thread,
 * by calling getResultsSync() on the Async run(DbStatement) call.
 */
public abstract class AsyncDbStatement {
    protected String query;
    private boolean done = false;

    public AsyncDbStatement() {
        queue(null);
    }

    public AsyncDbStatement(String query) {
        queue(query);
    }

    /**
     * Schedules this async statement to run on anther thread. This is the only method that should be
     * called on the main thread and it should only be called once.
     *
     * @param query
     */
    private void queue(final String query) {
        this.query = query;
        AsyncDbQueue.queue(this);
    }

    /**
     * Implement this method with your code that does Async SQL logic.
     *
     * @param statement
     * @throws SQLException
     */
    protected abstract void run(DbStatement statement) throws SQLException;

    /**
     * Override this event to have special logic for when an exception is fired.
     *
     * @param e
     */
    public void onError(SQLException e) {
        Util.printException("Exception in AsyncDbStatement" + query, e);
    }

    public void process(DbStatement stm) throws SQLException {
        synchronized (this) {
            if (!done) {
                if (query != null) {
                    stm.query(query);
                }
                run(stm);
                done = true;
            }
        }
    }
}

DbRow.java

/*
 * Copyright (c) 2015. Starlis LLC / dba Empire Minecraft
 *
 * This source code is proprietary software and must not be redistributed without Starlis LLC's approval
 *
 */

package com.empireminecraft.systems.db;

import java.util.HashMap;

/**
 * TypeDef alias for results with a template return type getter
 * so casting/implicit getInt type calls are not needed.
 */
public class DbRow extends HashMap<String, Object> {
    /**
     * Get the result as proper type.
     * <p/>
     * VALID: Long myLong = row.get("someUnsignedIntColumn");
     * INVALID: String myString = row.get("someUnsignedIntColumn");
     *
     * @param <T>
     * @param column
     * @return Object of the matching type of the result.
     */
    public <T> T get(String column) {
        return (T) super.get(column);
    }
    /**
     * Get the result as proper type., returning default if not found.
     * <p/>
     * VALID: Long myLong = row.get("someUnsignedIntColumn");
     * INVALID: String myString = row.get("someUnsignedIntColumn");
     *
     * @param <T>
     * @param column
     * @return Object of the matching type of the result.
     */
    public <T> T get(String column, T def) {
        T res = (T) super.get(column);
        if (res == null) {
            return def;
        }
        return res;
    }
    /**
     * Removes a result, returning as proper type.
     * <p/>
     * VALID: Long myLong = row.remove("someUnsignedIntColumn");
     * INVALID: String myString = row.remove("someUnsignedIntColumn");
     *
     * @param <T>
     * @param column
     * @return Object of the matching type of the result.
     */
    public <T> T remove(String column) {
        return (T) super.remove(column);
    }

    /**
     * Removes a result, returning as proper type, returning default if not found
     * <p/>
     * VALID: Long myLong = row.get("someUnsignedIntColumn");
     * INVALID: String myString = row.get("someUnsignedIntColumn");
     *
     * @param <T>
     * @param column
     * @return Object of the matching type of the result.
     */
    public <T> T remove(String column, T def) {
        T res = (T) super.remove(column);
        if (res == null) {
            return def;
        }
        return res;
    }

    public DbRow clone() {
        DbRow row = new DbRow();
        row.putAll(this);
        return row;
    }
}

DbStatement.java

/*
 * Copyright (c) 2015. Starlis LLC / dba Empire Minecraft
 *
 * This source code is proprietary software and must not be redistributed without Starlis LLC's approval
 *
 */

package com.empireminecraft.systems.db;

import com.empireminecraft.util.Util;
import org.spigotmc.timings.Timing;
import org.spigotmc.timings.Timings;

import java.sql.*;
import java.util.ArrayList;

import static org.bukkit.Bukkit.getServer;

/**
 * Manages a connection to the database pool and lets you work with an active
 * prepared statement.
 * <p/>
 * Must close after you are done with it, preferably wrapping in a try/catch/finally
 * DbStatement statement = null;
 * try {
 * statement = new DbStatement();
 * // use it
 * } catch (Exception e) {
 * // handle exception
 * } finally {
 * if (statement != null) {
 * statement.close();
 * }
 * }
 */
public class DbStatement implements AutoCloseable {
    Connection dbConn;
    PreparedStatement preparedStatement;
    ResultSet resultSet;
    public ResultSetMetaData resultSetMetaData;
    public String[] resultCols;
    public String query = "";
    // Has changes been made to a transaction w/o commit/rollback on close
    public boolean isDirty = false;
    static Timing sqlTimings = Util.timings("Database");

    public DbStatement() throws SQLException {
        if (EmpireDb.pooledDataSource == null) {
            Util.printException("No database connection, shutting down", new SQLException("We do not have a database"));
            getServer().shutdown();
            return;
        }

        dbConn = EmpireDb.pooledDataSource.getConnection();
    }

    public DbStatement(Connection connection) throws SQLException {
        dbConn = connection;
    }

    private static Timing time(String name) {
        return Timings.ofStart(Util.getEmpire(), name, sqlTimings);
    }

    /**
     * Starts a transaction on this connection
     *
     * @return
     * @throws SQLException
     */
    public void startTransaction() throws SQLException {
        try (Timing ignored = time("SQL - start transaction")) {
            dbConn.setAutoCommit(false);
        }
    }

    /**
     * Commits a pending transaction on this connection
     *
     * @return
     * @throws SQLException
     */
    public void commit() throws SQLException {
        try (Timing ignored = time("SQL - commit")) {
            dbConn.commit();
            isDirty = false;
        }
    }

    /**
     * Rollsback a pending transaction on this connection.
     *
     * @return
     * @throws SQLException
     */
    public void rollback() throws SQLException {
        try (Timing ignored = time("SQL - rollback")) {
            dbConn.rollback();
            isDirty = false;
        }
    }

    /**
     * Initiates a new prepared statement on this connection.
     *
     * @param query
     * @throws SQLException
     */
    public DbStatement query(String query) throws SQLException {
        this.query = query;
        try (Timing ignored = time("SQL - query: " + query)) {
            closeStatement();
            try {
                preparedStatement = dbConn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            } catch (SQLException e) {
                close();
                throw e;
            }
        }

        return this;
    }

    /**
     * Utility method used by execute calls to set the statements parameters to execute on.
     *
     * @param params Array of Objects to use for each parameter.
     * @return
     * @throws SQLException
     */
    protected void prepareExecute(Object... params) throws SQLException {
        isDirty = true;
        try (Timing ignored = time("SQL - prepareExecute: " + query)) {
            closeResult();
            if (preparedStatement == null) {
                throw new IllegalStateException("Run Query first on statement before executing!");
            }

            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
        }
    }

    /**
     * Execute an update query with the supplied parameters
     *
     * @param params
     * @return
     * @throws SQLException
     */
    public int executeUpdate(Object... params) throws SQLException {
        try (Timing ignored = time("SQL - executeUpdate: " + query)) {
            try {
                prepareExecute(params);
                return preparedStatement.executeUpdate();
            } catch (SQLException e) {
                close();
                throw e;
            }
        }
    }

    /**
     * Executes the prepared statement with the supplied parameters.
     *
     * @param params
     * @return
     * @throws SQLException
     */
    public DbStatement execute(Object... params) throws SQLException {
        try (Timing ignored = time("SQL - execute: " + query)) {
            try {
                prepareExecute(params);
                resultSet = preparedStatement.executeQuery();
                resultSetMetaData = resultSet.getMetaData();
                int numberOfColumns = resultSetMetaData.getColumnCount();

                resultCols = new String[numberOfColumns];
                // get the column names; column indexes start from 1
                for (int i = 1; i < numberOfColumns + 1; i++) {
                    resultCols[i - 1] = resultSetMetaData.getColumnLabel(i);
                }
            } catch (SQLException e) {
                close();
                throw e;
            }
        }
        return this;
    }

    /**
     * Gets the Id of last insert
     *
     * @return Long
     */
    public Long getLastInsertId() throws SQLException {
        try (Timing ignored = time("SQL - getLastInsertId")) {
            try (ResultSet genKeys = preparedStatement.getGeneratedKeys()) {
                if (genKeys == null) {
                    return null;
                }
                Long result = null;
                if (genKeys.next()) {
                    result = genKeys.getLong(1);
                }
                return result;
            }
        }
    }

    /**
     * Gets all results as an array of DbRow
     *
     * @return
     * @throws SQLException
     */
    public ArrayList<DbRow> getResults() throws SQLException {
        if (resultSet == null) {
            return null;
        }
        try (Timing ignored = time("SQL - getResults")) {
            ArrayList<DbRow> result = new ArrayList<>();
            DbRow row;
            while ((row = getNextRow()) != null) {
                result.add(row);
            }
            return result;
        }
    }

    /**
     * Gets the next DbRow from the result set.
     *
     * @return DbRow containing a hashmap of the columns
     * @throws SQLException
     */
    public DbRow getNextRow() throws SQLException {
        if (resultSet == null) {
            return null;
        }

        ResultSet nextResultSet = getNextResultSet();
        if (nextResultSet != null) {
            DbRow row = new DbRow();
            for (String col : resultCols) {
                row.put(col, nextResultSet.getObject(col));
            }
            return row;
        }
        return null;
    }

    public <T> T getFirstColumn() throws SQLException {
        ResultSet resultSet = getNextResultSet();
        if (resultSet != null) {
            return (T) resultSet.getObject(1);
        }
        return null;
    }

    /**
     * Util method to get the next result set and close it when done.
     *
     * @return
     * @throws SQLException
     */
    protected ResultSet getNextResultSet() throws SQLException {
        if (resultSet != null && resultSet.next()) {
            return resultSet;
        } else {
            closeResult();
            return null;
        }
    }
    private void closeResult() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
            resultSet = null;
        }
    }
    private void closeStatement() throws SQLException {
        if (preparedStatement != null) {
            preparedStatement.close();
            resultSet = null;
            preparedStatement = null;
        }
    }
    /**
     * Closes all resources associated with this statement and returns the connection to the pool.
     */
    public void close() {
        try (Timing ignored = time("SQL - close")) {
            try {
                if (dbConn != null) {
                    if (isDirty && !dbConn.getAutoCommit()) {
                        Util.printException(new Exception("Statement was not finalized: " + query));
                    }
                    dbConn.close();
                }
                preparedStatement = null;
                resultSet = null;
                dbConn = null;
            } catch (SQLException ex) {
                Util.printException("Failed to close DB connection: " + query, ex);
            }
        }
    }

    public boolean isClosed() throws SQLException {
        return dbConn == null || dbConn.isClosed();
    }
}

EmpireDb.java

/*
 * Copyright (c) 2015. Starlis LLC / dba Empire Minecraft
 *
 * This source code is proprietary software and must not be redistributed without Starlis LLC's approval
 *
 */

package com.empireminecraft.systems.db;

import com.empireminecraft.config.EmpireConfig;
import com.empireminecraft.config.EmpireServer;
import com.empireminecraft.util.Util;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.bukkit.Bukkit;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public final class EmpireDb {
    static HikariDataSource pooledDataSource;

    private EmpireDb() {}

    /**
     * Called in onDisable, destroys the Data source and nulls out references.
     */
    public static void close() {
        pooledDataSource.close();
        pooledDataSource = null;
    }

    /**
     * Called in onEnable, initializes the pool and configures it and opens the first connection to spawn the pool.
     */
    public static void initialize() {
        try {
            HikariConfig config = new HikariConfig();
            config.setPoolName("EmpireDb");

            String jdbc = System.getProperty("empire.db");
            if (jdbc == null) {
                jdbc = EmpireConfig.getString("db.database-endpoint");
                if (EmpireServer.isDev) {
                    jdbc = "localhost:3306";
                }
            }
            Util.log("Connecting to Database: " + jdbc);
            config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
            config.addDataSourceProperty("url", "jdbc:mysql://" + jdbc + "/minecraft");
            config.addDataSourceProperty("user", EmpireConfig.getString("db.user"));
            config.addDataSourceProperty("password", EmpireConfig.getString("db.pass"));
            config.addDataSourceProperty("cachePrepStmts", true);
            config.addDataSourceProperty("prepStmtCacheSize", 250);
            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
            config.addDataSourceProperty("useServerPrepStmts", true);
            config.addDataSourceProperty("cacheCallableStmts", true);
            config.addDataSourceProperty("cacheResultSetMetadata", true);
            config.addDataSourceProperty("cacheServerConfiguration", true);
            config.addDataSourceProperty("useLocalSessionState", true);
            config.addDataSourceProperty("elideSetAutoCommits", true);
            config.addDataSourceProperty("alwaysSendSetIsolation", false);

            config.setConnectionTestQuery("SELECT 1");
            config.setInitializationFailFast(true);
            config.setMinimumIdle(3);
            config.setMaximumPoolSize(5);

            pooledDataSource = new HikariDataSource(config);
            pooledDataSource.setTransactionIsolation("TRANSACTION_READ_COMMITTED");
        } catch (Exception ex) {
            pooledDataSource = null;
            Util.printException("EmpireDB: Error Creating Database Pool", ex);
            Bukkit.getServer().shutdown();
        }
    }

    /**
     * Initiates a new DbStatement and prepares the first query.
     * <p/>
     * YOU MUST MANUALLY CLOSE THIS STATEMENT IN A finally {} BLOCK!
     *
     * @param query
     * @return
     * @throws SQLException
     */
    public static DbStatement query(String query) throws SQLException {
        return (new DbStatement()).query(query);
    }

    /**
     * Utility method to execute a query and retrieve the first row, then close statement.
     * You should ensure result will only return 1 row for maximum performance.
     *
     * @param query  The query to run
     * @param params The parameters to execute the statement with
     * @return DbRow of your results (HashMap with template return type)
     * @throws SQLException
     */
    public static DbRow getFirstRow(String query, Object... params) throws SQLException {
        try (DbStatement statement = EmpireDb.query(query).execute(params)) {
            return statement.getNextRow();
        }
    }

    /**
     * Utility method to execute a query and retrieve the first column of the first row, then close statement.
     * You should ensure result will only return 1 row for maximum performance.
     *
     * @param query  The query to run
     * @param params The parameters to execute the statement with
     * @return DbRow of your results (HashMap with template return type)
     * @throws SQLException
     */
    public static <T> T getFirstColumn(String query, Object... params) throws SQLException {
        try (DbStatement statement = EmpireDb.query(query).execute(params)) {
            return statement.getFirstColumn();
        }
    }

    /**
     * Utility method to execute a query and retrieve first column of all results, then close statement.
     *
     * Meant for single queries that will not use the statement multiple times.
     * @param query
     * @param params
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> getFirstColumnResults(String query, Object... params) throws SQLException {
        List<T> dbRows = new ArrayList<>();
        T result;
        try (DbStatement statement = EmpireDb.query(query).execute(params)) {
            while ((result = statement.getFirstColumn()) != null) {
                dbRows.add(result);
            }
        }
        return dbRows;
    }

    /**
     * Utility method to execute a query and retrieve all results, then close statement.
     *
     * Meant for single queries that will not use the statement multiple times.
     *
     * @param query  The query to run
     * @param params The parameters to execute the statement with
     * @return List of DbRow of your results (HashMap with template return type)
     * @throws SQLException
     */
    public static List<DbRow> getResults(String query, Object... params) throws SQLException {
        try (DbStatement statement = EmpireDb.query(query).execute(params)) {
            return statement.getResults();
        }
    }

    /**
     * Utility method for executing an update synchronously, and then close the statement.
     *
     * @param query  Query to run
     * @param params Params to execute the statement with.
     * @return Number of rows modified.
     * @throws SQLException
     */
    public static int executeUpdate(String query, Object... params) throws SQLException {
        try (DbStatement statement = EmpireDb.query(query)) {
            return statement.executeUpdate(params);
        }
    }

    /**
     * Utility method to execute an update statement asynchronously and close the connection.
     *
     * @param query  Query to run
     * @param params Params to execute the update with
     */
    public static void executeUpdateAsync(String query, final Object... params) {
        new AsyncDbStatement(query) {
            @Override
            public void run(DbStatement statement) throws SQLException {
                statement.executeUpdate(params);
            }
        };
    }
}