SQLiteSingleThreadedManager

SingleTreadedSQLiteManager.java

/*
 * Copyright (c) 2018, tauTerra, LLC; Nicholas Folse
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * * Redistributions of source code must retain the above copyright notice, this
 *   list of conditions and the following disclaimer.
 * * Redistributions in binary form must reproduce the above copyright notice,
 *   this list of conditions and the following disclaimer in the documentation
 *   and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

/**
 * Gradle:
 *
 * dependencies {
 *   compile group: 'org.xerial', name: 'sqlite-jdbc', version: '3.23.1'
 * }
 *
 */
package com.tauterra.sqlite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

/**
 * Usage:
 * This class provides a basic framework for a single-threaded interface
 * to an SQLite database. To use it, extend the class and call 
 * submitAndGet(...) or submitAndGetInTransaction(...). Both of these functions
 * accept callables, so you can return results as necessary.
 * If you're database is write-heavy and you don't need confirmation of writes,
 * just remove the .get() statements and return the Futures.
 * 
 * This class also provides convenience methods for beginning, commiting, and
 * rolling back transactions. It's safe to use these methods from within
 * the submit methods.
 * 
 * Finally, this class provides a map of preparedStatements which you're
 * free to use within yor implementation. Call prepareStatement(key, SQL) to 
 * prepare a statement with the specified SQL. You can retrieve the prepared
 * statement by making a call to preparedStatement(key).
 * @author Nicholas Folse <https://github.com/NF1198>
 */
public class SingleTreadedSQLiteManager implements AutoCloseable {

    private final String connectionUrl;
    private final ExecutorService exec;
    private Connection conn;

    private static String STMT_BEGIN_TRANS = "STMT_BEGIN_TRANS";
    private static String STMT_COMMIT_TRANS = "STMT_COMMIT_TRANS";
    private static String STMT_ROLLBACK_TRANS = "STMT_ROLLBACK_TRANS";

    private Map<String, PreparedStatement> psmap = new HashMap<>();

    public SingleTreadedSQLiteManager(String connectionUrl) {
        this(Executors.newSingleThreadExecutor(), connectionUrl);
    }

    public SingleTreadedSQLiteManager(ExecutorService executor, String connectionUrl) {
        this.exec = executor;
        this.connectionUrl = connectionUrl;
    }

    protected <T> T submitAndGetInTransaction(Callable<T> task) throws SQLiteManagerException {
        try {
            ExecutorService e = this.exec;
            return e.submit(() -> {
                try {
                    beginTransaction();
                    T result = task.call();
                    commitTransaction();
                    return result;
                } catch (Exception ex) {
                    rollbackTransaction();
                    throw new SQLiteManagerException("error with transaction", ex);
                }
            }).get();
        } catch (InterruptedException | ExecutionException ex) {
            throw new SQLiteManagerException("error executing task", ex);
        }
    }

    protected <T> T submitAndGet(Callable<T> task) throws SQLiteManagerException {
        try {
            ExecutorService e = this.exec;
            return e.submit(task).get();
        } catch (InterruptedException | ExecutionException ex) {
            throw new SQLiteManagerException("error executing task", ex);
        }
    }

    protected Connection getConnection() throws SQLiteManagerException {
        try {
            if (this.conn == null || this.conn.isClosed()) {
                this.conn = DriverManager.getConnection(this.connectionUrl);
            }
            return this.conn;
        } catch (SQLException ex) {
            throw new SQLiteManagerException("error reading connection", ex);
        }
    }

    protected void beginTransaction() throws SQLException {
        PreparedStatement stmt = prepareStatement(STMT_BEGIN_TRANS, "BEGIN TRANSACTION");
        stmt.execute();
    }

    protected void commitTransaction() throws SQLException {
        PreparedStatement stmt = prepareStatement(STMT_COMMIT_TRANS, "COMMIT TRANSACTION");
        stmt.execute();
    }

    protected void rollbackTransaction() throws SQLiteManagerException {
        try {
            PreparedStatement stmt = prepareStatement(STMT_ROLLBACK_TRANS, "ROLLBACK TRANSACTION");
            stmt.execute();
        } catch (SQLException e) {
            throw new SQLiteManagerException("error rolling back transaction", e);
        }
    }

    protected PreparedStatement prepareStatement(String tag) throws SQLException {
        PreparedStatement stmt = psmap.get(tag);
        stmt.clearParameters();
        return stmt;
    }

    protected PreparedStatement prepareStatement(String tag, String SQL) throws SQLException {
        if (!psmap.containsKey(tag) || psmap.get(tag).isClosed()) {
            Connection c = getConnection();
            PreparedStatement stmt = c.prepareStatement(SQL);
            psmap.put(tag, stmt);
        }
        PreparedStatement stmt = psmap.get(tag);
        stmt.clearParameters();
        return stmt;
    }

    @Override
    public void close() throws Exception {
        exec.shutdown();
        exec.awaitTermination(120, TimeUnit.SECONDS);
        for (PreparedStatement stmt : psmap.values()) {
            stmt.close();
        }
        psmap.clear();
        conn.close();

    }

    public static class SQLiteManagerException extends RuntimeException {

        public SQLiteManagerException(String message) {
            super(message);
        }

        public SQLiteManagerException(String message, Throwable cause) {
            super(message, cause);
        }

    }

}