com.skynetcomputing.database.Database.java Source code

Java tutorial

Introduction

Here is the source code for com.skynetcomputing.database.Database.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.skynetcomputing.database;

import com.skynetcomputing.utils.MiscUtils;
import org.apache.commons.pool2.BasePooledObjectFactory;
import org.apache.commons.pool2.PooledObject;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.apache.commons.pool2.impl.PooledSoftReference;

import java.lang.ref.SoftReference;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.stream.Stream;

/**
 * @author rich
 */
public class Database {
    private static final String URL = "jdbc:mysql://iris.fhict.nl:3306/i309527_skynet?zeroDateTimeBehavior=convertToNull";
    private static final String USERNAME = "i309527_user";
    private static final String PASSWORD = "user123";

    protected GenericObjectPool<Connection> pool;

    private static Database instance;

    public static Database getInstance() {
        if (instance == null)
            instance = new Database();
        return instance;
    }

    /**
     * Creates a new database connection pool so that statements and queries can be executed.
     */
    @SuppressWarnings("unchecked")
    private Database() {
        GenericObjectPoolConfig config = new GenericObjectPoolConfig();
        config.setMaxIdle(5);
        config.setMaxTotal(10);

        // Configurable connectionstring.
        String connString = MiscUtils.getPropertyOrDefault("dbConnection", URL);
        String connUser = MiscUtils.getPropertyOrDefault("dbUser", USERNAME);
        String connPassword = MiscUtils.getPropertyOrDefault("dbPassword", PASSWORD);

        pool = new GenericObjectPool<>(new BasePooledObjectFactory() {
            @Override
            public Object create() throws Exception {
                return DriverManager.getConnection(connString, connUser, connPassword);
            }

            @Override
            public PooledObject wrap(Object o) {
                return new PooledSoftReference<>(new SoftReference<>(o));
            }
        }, config);
    }

    /**
     * Executes an action on a pooled {@link Connection} and adds this {@link Connection} to the pool after execution.
     *
     * @param action Action to execute.
     * @return Result or null if no connect was given.
     */
    public void connect(IConnectionAction action) throws Exception {
        if (action == null)
            return;
        Connection conn = pool.borrowObject();
        try {
            action.execute(conn);
        } finally {
            pool.returnObject(conn);
        }
    }

    /**
     * Connects to the database with an expected result.
     *
     * @param action Action that executes tasks on the database and returns a result.
     * @param <T>    Type of result to return.
     * @return Object that was returned by the action.
     * @throws Exception
     */
    public <T> T connectResult(IConnectionResult<T> action) throws Exception {
        if (action == null)
            return null;
        Connection conn = pool.borrowObject();
        try {
            return action.execute(conn);
        } finally {
            pool.returnObject(conn);
        }
    }

    /**
     * Builds a query to execute on the database.
     *
     * @param query     Query to run on the database. Use ? in the query to prepare arguments for the query.
     * @param statement Action to run on the query object.
     * @throws Exception
     */
    public void query(String query, IPreparedStatement statement) throws Exception {
        if (query == null || query.isEmpty())
            return;
        connect((conn) -> statement.prepare(conn.prepareStatement(query)));
    }

    /**
     * Builds a call statement of executing a procedure on the database.
     *
     * @param name Name of the procedure to call.
     * @param args Arguments that the procedure requires in order to call it.
     * @return Query builder that has built the statement.
     */
    private StringBuilder buildProcedure(String name, Object... args) {
        if (name == null || name.isEmpty())
            throw new IllegalArgumentException("Parameter name should not be null or empty.");

        // Build procedure call query.
        StringBuilder queryBuilder = new StringBuilder();
        queryBuilder.append("{call ");
        queryBuilder.append(name);

        // Parameters.
        queryBuilder.append('(');
        for (int i = 0; i < args.length; i++) {
            // Last
            if (i == args.length - 1) {
                queryBuilder.append("%s");
            } else {
                // Append to query.
                queryBuilder.append("%s,");
            }
        }
        queryBuilder.append(")}");
        return queryBuilder;
    }

    /**
     * Builds a procedure for use in query, insert, update and delete.
     *
     * @param procedureName Name of the procedure to execute.
     * @param statement     Action to run on the procedure.
     * @param parameters    Parameters to give with the procedure.
     * @throws Exception
     */
    public void procedure(String procedureName, ICallableStatement statement, Object... parameters)
            throws Exception {
        StringBuilder queryBuilder = buildProcedure(procedureName, parameters);

        // Creates a new connection, builds the query to call the procedure with arguments, parses the arguments to a string array and passes it to the procedure call.
        connect((connection -> statement.call(connection.prepareCall(String.format(queryBuilder.toString(),
                (Object[]) Stream.of(parameters).map(this::objectToSqlString).toArray(String[]::new))))));
    }

    /**
     * Builds a procedure and executes a returnable action on it.
     *
     * @param procedureName Name of the procedure to execute on the database.
     * @param statement     Statement to run (execute statement and return result).
     * @param parameters    Parameters that the procedure needs.
     * @param <T>           Type to return.
     * @param <TState>      Type of statement to execute.
     * @return Object that was returned in the statement.
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public <T, TState extends ICallableStatementResult> T procedureResult(String procedureName, TState statement,
            Object... parameters) throws Exception {
        StringBuilder queryBuilder = buildProcedure(procedureName, parameters);

        // Creates a new connection, builds the query to call the procedure with arguments, parses the arguments to a string array and passes it to the procedure call.
        return (T) connectResult(
                (connection -> statement.call(connection.prepareCall(String.format(queryBuilder.toString(),
                        (Object[]) Stream.of(parameters).map(this::objectToSqlString).toArray(String[]::new))))));
    }

    /**
     * Changes an object to be conformal to SQL.
     *
     * @param obj Object to convert to a string representation that SQL accepts.
     * @return String representation of the object for SQL.
     */
    private String objectToSqlString(Object obj) {
        if (obj == null)
            return null;
        if (obj instanceof String) {
            return '\'' + obj.toString().replace("\\", "\\\\") + '\'';
        } else if (obj instanceof Integer || obj instanceof Double || obj instanceof Short || obj instanceof Long) {
            return obj.toString();
        } else {
            return obj.toString();
        }
    }

}