DBConnection.java

package com.github.collinalpert.java2db.database;

import com.github.collinalpert.java2db.mappers.FieldMapper;
import com.github.collinalpert.java2db.queries.*;
import com.github.collinalpert.java2db.queries.async.*;

import java.io.Closeable;
import java.sql.*;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.function.Consumer;

import static com.github.collinalpert.java2db.utilities.Utilities.supplierHandling;

/**
 * Wrapper around {@link Connection} which eases use of running queries.
 * Also supports running functions and stored procedures.
 *
 * @author Collin Alpert
 */
public class DBConnection implements Closeable {

	/**
	 * Constant which determines if the queries generated by Java2DB will be logged in the console.
	 */
	public static boolean LOG_QUERIES = true;

	private final Connection underlyingConnection;
	private boolean isConnectionValid;

	public DBConnection(Connection underlyingConnection) {
		this.underlyingConnection = underlyingConnection;
		this.isConnectionValid = true;
	}

	/**
	 * Checks if the connection is valid/successful.
	 *
	 * @return True if connection was successful, false if not.
	 */
	public boolean isValid() {
		return this.isConnectionValid;
	}


	/**
	 * Executes a DQL statement on the database without Java parameters.
	 *
	 * @param query The query to be executed.
	 * @return The {@link ResultSet} containing the result from the DQL statement.
	 * @throws SQLException if the query is malformed or cannot be executed.
	 */
	public ResultSet execute(String query) throws SQLException {
		var statement = this.underlyingConnection.createStatement();
		log(query);
		var set = statement.executeQuery(query);
		statement.closeOnCompletion();

		return set;
	}

	/**
	 * Executes a DQL statement on the database with Java parameters.
	 *
	 * @param query  The query to be executed.
	 * @param params The Java parameters to be inserted into the query.
	 * @return The {@link ResultSet} containing the result from the DQL statement.
	 * @throws SQLException if the query is malformed or cannot be executed.
	 */
	public ResultSet execute(String query, Object... params) throws SQLException {
		var statement = this.underlyingConnection.prepareStatement(query);
		for (int i = 0; i < params.length; i++) {
			statement.setObject(i + 1, params[i]);
		}

		log(query);
		var set = statement.executeQuery();
		statement.closeOnCompletion();
		return set;
	}

	/**
	 * This command is used for any DDL/DML queries.
	 *
	 * @param query The query to be executed.
	 * @return the last generated ID. This return value should only be used with INSERT statements.
	 * @throws SQLException if the query is malformed or cannot be executed.
	 */
	public int update(String query) throws SQLException {
		var statement = this.underlyingConnection.createStatement();
		log(query);
		statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
		return updateInternal(statement);
	}

	/**
	 * This command is used for any DDL/DML queries with Java parameters.
	 *
	 * @param query  The query to be executed.
	 * @param params The Java parameters to be inserted into the query.
	 * @return the last generated ID. This return value should only be used with INSERT statements.
	 * @throws SQLException if the query is malformed or cannot be executed.
	 */
	public int update(String query, Object... params) throws SQLException {
		var statement = this.underlyingConnection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
		for (int i = 0; i < params.length; i++) {
			statement.setObject(i + 1, params[i]);
		}

		log(query);
		statement.executeUpdate();
		return updateInternal(statement);
	}

	/**
	 * Determines if a connection to the database still exists or not.
	 *
	 * @return {@code True} if a connection exists, {@code false} if not.
	 * This method will return {@code false} if an exception occurs.
	 */
	public boolean isOpen() {
		try {
			return !this.underlyingConnection.isClosed();
		} catch (SQLException e) {
			System.err.println("Could not determine connection status");
			return this.isConnectionValid = false;
		}
	}

	/**
	 * Closes the connection to the database.
	 */
	@Override
	public void close() {
		try {
			if (this.underlyingConnection != null) {
				this.underlyingConnection.close();
			}
		} catch (SQLException e) {
			System.err.println("Could not close database connection");
			e.printStackTrace();
		} finally {
			this.isConnectionValid = false;
		}
	}

	/**
	 * Calls an SQL function.
	 *
	 * @param returnType   The Java equivalent of the SQL datatype the function returns.
	 * @param functionName The name of the function.
	 * @param arguments    The arguments to be supplied to the function, if any.
	 * @param <T>          The functions return type.
	 * @return The return value of the function, as a Java datatype.
	 * @throws SQLException In case there is an error communicating with the database, i.e. the function does not exist.
	 */
	public <T> Optional<T> callFunction(Class<T> returnType, String functionName, Object... arguments) throws SQLException {
		var joiner = new StringJoiner(",");
		for (int i = 0; i < arguments.length; i++) {
			joiner.add("?");
		}

		try (var set = execute(String.format("select %s(%s);", functionName, joiner.toString()), arguments)) {
			return new FieldMapper<>(returnType).map(set);
		}
	}

	/**
	 * Calls an SQL function asynchronously.
	 *
	 * @param exceptionHandler The exception handler which handles the {@link SQLException} in case something goes wrong.
	 * @param returnType       The Java type which the result will be mapped into. It needs to have a parameterless constructor available.
	 * @param functionName     The name of the function to call.
	 * @param arguments        The arguments to supply to the function, if any.
	 * @param <T>              The functions return type.
	 * @return The return value of the function, as a Java datatype.
	 */
	public <T> CompletableFuture<Optional<T>> callFunctionAsync(Consumer<SQLException> exceptionHandler, Class<T> returnType, String functionName, Object... arguments) {
		return CompletableFuture.supplyAsync(supplierHandling(() -> this.callFunction(returnType, functionName, arguments), exceptionHandler));
	}

	/**
	 * Calls an SQL function asynchronously. If an exception occurs, it gets printed to the console.
	 *
	 * @param returnType   The Java type which the result will be mapped into. It needs to have a parameterless constructor available.
	 * @param functionName The name of the function to call.
	 * @param arguments    The arguments to supply to the function, if any.
	 * @param <T>          The functions return type.
	 * @return The return value of the function, as a Java datatype.
	 */
	public <T> CompletableFuture<Optional<T>> callFunctionAsync(Class<T> returnType, String functionName, Object... arguments) {
		return CompletableFuture.supplyAsync(supplierHandling(() -> this.callFunction(returnType, functionName, arguments)));
	}

	/**
	 * Calls an SQL function asynchronously and applies a {@link Consumer} to the returned value.
	 *
	 * @param returnType   The Java type which the result will be mapped into. It needs to have a parameterless constructor available.
	 * @param callback     A consumer which specifies which action to perform once the function has been called.
	 * @param functionName The name of the function to call.
	 * @param arguments    The arguments to supply to the function, if any.
	 * @param <T>          The functions return type.
	 * @return The return value of the function, as a Java datatype.
	 */
	public <T> CompletableFuture<Void> callFunctionAsync(Class<T> returnType, Consumer<? super Optional<T>> callback, String functionName, Object... arguments) {
		return CompletableFuture.supplyAsync(supplierHandling(() -> this.callFunction(returnType, functionName, arguments))).thenAcceptAsync(callback);
	}

	/**
	 * Calls an SQL function asynchronously and applies a {@link Consumer} to the returned value.
	 *
	 * @param exceptionHandler The exception handler which handles the {@link SQLException} in case something goes wrong.
	 * @param returnType       The Java type which the result will be mapped into. If it is a complex type, it needs to have a parameterless constructor available.
	 * @param callback         A consumer which specifies which action to perform once the function has been called.
	 * @param functionName     The name of the function to call.
	 * @param arguments        The arguments to supply to the function, if any.
	 * @param <T>              The functions return type.
	 * @return The return value of the function, as a Java datatype.
	 */
	public <T> CompletableFuture<Void> callFunctionAsync(Consumer<SQLException> exceptionHandler, Class<T> returnType, Consumer<? super Optional<T>> callback, String functionName, Object... arguments) {
		return CompletableFuture.supplyAsync(supplierHandling(() -> this.callFunction(returnType, functionName, arguments), exceptionHandler)).thenAcceptAsync(callback);
	}

	/**
	 * Calls a stored procedure and returns a {@link Queryable} to fetch the data in the desired format.
	 *
	 * @param returnType          The Java type which the result will be mapped into. If it is a complex type, it needs to have a parameterless constructor available.
	 * @param storedProcedureName The name of the stored procedure to call.
	 * @param arguments           The arguments to pass to the stored procedure.
	 * @param <T>                 The Java type to be returned?
	 * @return A {@link Queryable} to fetch the data in the desired format.
	 */
	public <T> Queryable<T> callStoredProcedure(Class<T> returnType, String storedProcedureName, Object... arguments) {
		return new StoredProcedureQuery<>(returnType, this, storedProcedureName, arguments);
	}

	/**
	 * Calls a stored procedure asynchronously and returns a {@link Queryable} to fetch the data in the desired format.
	 *
	 * @param returnType          The Java type which the result will be mapped into. If it is a complex type, it needs to have a parameterless constructor available.
	 * @param storedProcedureName The name of the stored procedure to call.
	 * @param arguments           The arguments to pass to the stored procedure.
	 * @param <T>                 The Java type to be returned?
	 * @return An {@link AsyncQueryable} to fetch the data in the desired format.
	 */
	public <T> AsyncQueryable<T> callStoredProcedureAsync(Class<T> returnType, String storedProcedureName, Object... arguments) {
		return new AsyncStoredProcedureQuery<>(returnType, this, storedProcedureName, arguments);
	}

	/**
	 * @return The {@link Connection} object this class uses to operate on.
	 */
	public Connection underlyingConnection() {
		return this.underlyingConnection;
	}

	/**
	 * Prints queries to the console, while considering the {@link DBConnection#LOG_QUERIES} constant.
	 *
	 * @param text The message to print.
	 */
	private void log(String text) {
		if (LOG_QUERIES) {
			System.out.println(text);
		}
	}

	private int updateInternal(Statement statement) throws SQLException {
		statement.closeOnCompletion();
		var set = statement.getGeneratedKeys();
		if (set.next()) {
			return set.getInt(1);
		}

		return -1;
	}
}