dbutil

package marketmanagement.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/** Database utility
 * @author Dharmendrasinh Chudasama
 */
public class DatabaseUtil {
	private static final List<Connection> connList = new LinkedList<>();

	static {
		// will call on shutdown JVM, example on System.exit(?);
		Runtime.getRuntime().addShutdownHook(new Thread(()->{
			closeAllConnections();
		}, "Remaining connection closer thread hook"));
	}

	/** get db connection, for close connection use {@link #close(Connection)}, never call, conn.close()
	 * @return new database connection
	 */
	public static Connection getConnection() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketmanagement","root","root");
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		connList.add(conn);
		return conn;
	}

	public static void close(Connection conn) throws SQLException {
		if(conn != null){
			conn.close();
			connList.remove(conn);
		}
	}

	/** close all remaining connection if any
	 * @return 0=allClosed or remaining open connection due exceptions */
	public static synchronized int closeAllConnections(){ //close connections if open by mistake
		if(!connList.isEmpty()) {
			for (Object conn : connList.toArray()) { //toArray() for prevent concurrent modification exception
				try {
					close((Connection) conn);
				} catch (Exception e) {}
			}
		}
		return connList.size();
	}

	/** @param sql
	 * @param arguments arguments for sql statement
	 * @param def <code>null</code> allowed
	 * @return first result column element, if not found def parameter
	 */
	public static Object get(Connection conn, String sql, Object[] arguments, Object def) throws Exception {
		return select(conn, sql, arguments, rs-> rs.next() ? rs.getObject(1) : def);
 	}

	public static Object get(String sql, String[] arguments, Object def) throws Exception {
		return execute(conn->get(conn, sql, arguments, def));
	}

	/**
	 * @param rs resultset instance
	 * @return converted list from resultset
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> toMapList(ResultSet rs) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		ResultSetMetaData meta = rs.getMetaData();
		int cols = meta.getColumnCount();
		Map<String, Object> map; // = new HashMap<>(cols);

		rs.beforeFirst();
		while (rs.next()) {
			map = new LinkedHashMap<>(cols);
			for(int i=1; i<=cols; i++)
				map.put(meta.getColumnLabel(i), rs.getObject(i));
			list.add(map);
		}
		return list;
	}

	/** first column
	 * @param rs resultset instance (should has at least 1 column)
	 * @return first column
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public static <E> List<E> toList(ResultSet rs) throws SQLException {
		List<E> list = new ArrayList<E>();
		rs.beforeFirst();
		while(rs.next())
			list.add((E) rs.getObject(1));
		return list;
	}

	/** first 2 columns
	 * @param <K>
	 * @param <V>
	 * @param rs resultset instance (should has at least 2 columns)
	 * @return converted map<col1, col2> from resultset
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public static <K, V> Map<K, V> toMap(ResultSet rs) throws SQLException {
		Map<K,V> map = new LinkedHashMap<>();
		rs.beforeFirst();
		while(rs.next())
			map.put((K) rs.getObject(1), (V) rs.getObject(2));
		return map;
	}


	public static <R> R select(final Connection con, final String sql, final Object[] arguments, final Function<ResultSet, R> func) throws Exception {
		try (PreparedStatement stmt = con.prepareStatement(sql))
		{
			if(arguments != null && arguments.length>0) {
				for (int i = 0; i<arguments.length; i++)
					stmt.setObject(i+1, arguments[i]);
			}

			try (ResultSet rs = stmt.executeQuery()) {
				return func.accept(rs);
			}
		}
 	}

	public static <R> R select(final String sql, final Object[] arguments, final Function<ResultSet, R> func) throws Exception {
		return execute(conn->select(conn, sql, arguments, func));
	}

	public static <R> R call(Connection con, String spName, Object[] args, Function<ResultSet, R> func) throws Exception {
		String spCode = args==null || args.length==0 ? "{CALL "+spName+"()}" : "{CALL "+spName+"("+String.join(",", Collections.nCopies(args.length, "?"))+")}";
		try (CallableStatement stmt = con.prepareCall(spCode))
		{
			if(args != null && args.length>0) {
				for (int i = 0; i<args.length; i++)
					stmt.setObject(i+1, args[i]);
			}

			try (ResultSet rs = stmt.executeQuery()) {
				return func.accept(rs);
			}
		}
 	}

	public static <R> R call(String spName, Object[] args, Function<ResultSet, R> func) throws Exception {
		return execute(conn->call(conn, spName, args, func));
	}

	/**
	 * @param <R>
	 * @param sqlQuery
	 * @param args
	 * @param function called once only
	 * @throws Exception
	 */
/*	public static <R> R select(String sqlQuery, Object[] args, Function<ResultSet, R> function) throws Exception {
		Connection conn = getConnection();
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			if(args==null || args.length==0)
				rs = conn.createStatement().executeQuery(sqlQuery);
			else {
				pStmt = conn.prepareStatement(sqlQuery);
				for (int i = 0; i<args.length; i++)
					pStmt.setObject(i+1, args[i]);
				rs = pStmt.executeQuery();
			}

			return function.accept(rs);
		} finally {
			if(rs != null) rs.close();
			if(pStmt != null) pStmt.close();
			if(conn != null) close(conn);
		}
 	}*/

	@FunctionalInterface public interface Function<T,R>{ R accept(T data) throws Exception; }
	public static <R> R execute(final Function<Connection, R> function) throws Exception {
		final Connection conn = getConnection();
		try{
			return function.accept(conn);
		}finally {
			close(conn);
		}
	}
//	public static final Function<ResultSet, List<Map<String, Object>>> toList = DatabaseUtil::toList;

	/** same as {@link DatabaseUtil#execute(Function)} but here handled transactions (commit & rollbacks)<br>
	 * 	example:<br>
	 * <pre>
	  		DatabaseUtil.transaction(conn->{
				conn.createStatement().executeUpdate("insert into table_parent(col1) values(123)");
				conn.createStatement().executeUpdate("insert into table_child(col1) values(123)");
				return "success(committed)";
			});
	 </pre>
	 * @throws Exception throws after rollback if any exceptions are occured
	 */
	public static <R> R transaction(final Function<Connection, R> function) throws Exception {
		final Connection conn = getConnection();
		try{
			conn.setAutoCommit(false);
			R ret = function.accept(conn);
			conn.commit();
			return ret;
		}catch (Exception e) {
			conn.rollback();
			throw e;
		}finally {
			close(conn);
		}
	}
}