Java tutorial
/* * 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(); } } }