/********************************
 *	프로젝트 : VisualFxVoEditor
 *	패키지   : com.kyj.fx.voeditor.visual.util
 *	작성일   : 2015. 10. 16.
 *	작성자   : KYJ
 *******************************/
package com.kyj.fx.voeditor.visual.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Optional;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.BiFunction;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import java.util.stream.Stream;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import org.json.simple.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import com.kyj.fx.voeditor.visual.exceptions.GargoyleException;
import com.kyj.fx.voeditor.visual.exceptions.NotSupportException;
import com.kyj.fx.voeditor.visual.framework.handler.ExceptionHandler;
import com.kyj.fx.voeditor.visual.framework.thread.ExecutorDemons;
import com.kyj.fx.voeditor.visual.functions.BiTransactionScope;
import com.kyj.fx.voeditor.visual.functions.FourThFunction;
import com.kyj.fx.voeditor.visual.functions.ResultSetToMapConverter;
import com.kyj.fx.voeditor.visual.momory.ConfigResourceLoader;
import com.kyj.fx.voeditor.visual.momory.ResourceLoader;
import com.kyj.utils.EncrypUtil;

import javafx.util.Callback;

/**
 * jdbc
 *
 * @author KYJ
 *
 */
public class DbUtil extends ConnectionManager {
	/**
	 * 
	 * @최초생성일 2016. 8. 4.
	 */
	public static final int DEFAULT_FETCH_SIZE = 100;

	/**
	 * @최초생성일 2016. 9. 1.
	 */
	public static final int DEFAULT_LIMIT_ROW_COUNT = 1000;

	private static final Logger LOGGER = LoggerFactory.getLogger(DbUtil.class);

	/**
	 * 쿼리이벤트 수신 클래스 목록
	 *
	 * @최초생성일 2016. 2. 12.
	 */
	private static ConcurrentHashMap<String, DbExecListener> listeners = new ConcurrentHashMap<>();

	public static String[] dmlkeyword;// = { "insert", "update", "delete",
										// "create", "drop", "alter" };

	static {
		String item = ConfigResourceLoader.getInstance().get(ConfigResourceLoader.DML_KEYWORD);
		dmlkeyword = item.split(",");
	}

	static BiFunction<Connection, String, PreparedStatement> DEFAULT_PREPAREDSTATEMENT_CONVERTER = (c, sql) -> {
		try {
			return c.prepareStatement(sql);
		} catch (Exception e) {
			throw new RuntimeException(e);
			// return null;
		}
	};

	static BiFunction<Connection, String, PreparedStatement> READ_ONLY_CURSOR_PREPAREDSTATEMENT_CONVERTER = (c, sql) -> {
		try {
			return c.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			return null;
		}
	};

	/**
	 * 쿼리가 수행되면 메세지를 보낸다.
	 *
	 * @작성자 : KYJ
	 * @작성일 : 2016. 2. 4.
	 * @param query
	 */
	private static void noticeQuery(String query) {

		// 성능 차이로 인한 극복이 필요한경우에는 아래 주석을 해제하고 사용할것.
		// listeners.values().parallelStream().forEach(listener ->
		// listener.onQuertying(query));

		listeners.values().stream().forEach(listener -> listener.onQuerying(query));
	}

	/**
	 * 쿼리 수행 이벤트 수신을 등록한다.
	 *
	 * @작성자 : KYJ
	 * @작성일 : 2016. 2. 12.
	 * @param listener
	 */
	public static void registQuertyListener(DbExecListener listener) {
		String canonicalName = listener.getClass().getCanonicalName();
		if (!listeners.containsKey(canonicalName))
			listeners.put(canonicalName, listener);
	}

	public static List<Map<String, Object>> select(final String sql) throws Exception {
		Connection con = getConnection();
		return select(con, sql);
	}

	public static <T> List<Map<String, T>> select(final String sql, int fetchCount, int limitSize,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, T>>> convert) throws Exception {
		Connection con = getConnection();
		return select(con, sql, fetchCount, limitSize, convert);
	}

	public static List<Map<String, Object>> select(final String sql,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, Object>>> convert) throws Exception {
		Connection con = getConnection();
		return select(con, sql, convert);
	}

	public static List<Map<String, Object>> select(Connection con, final String sql) throws Exception {
		return select(con, sql, 10);
	}

	public static List<Map<String, Object>> select(final Connection con, final String sql,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, Object>>> convert) throws Exception {
		return select(con, sql, 10, -1, convert);
	}

	public static List<Map<String, Object>> selectLimit(final String sql) throws Exception {
		Connection con = getConnection();
		return selectLimit(con, sql, 10, DEFAULT_FETCH_SIZE);
	}

	public static List<Map<String, Object>> selectLimit(final String sql, int limitSize) throws Exception {
		Connection con = getConnection();
		return selectLimit(con, sql, 10, limitSize);
	}

	public static List<Map<String, Object>> selectLimit(Connection con, final String sql, int fetchCount, int limitSize) throws Exception {
		return select(con, sql, fetchCount, limitSize, new ResultSetToMapConverter());
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 *
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> select(final Connection con, final String sql, int fetchCount) throws Exception {
		return select(con, sql, fetchCount, -1, new ResultSetToMapConverter());
	}

	public static Map<String, Object> findOne(final Connection con, final String sql) throws Exception {
		List<Map<String, Object>> select = select(con, sql, 1, 1, new ResultSetToMapConverter());
		return select.isEmpty() ? Collections.emptyMap() : select.get(0);
	}

	public static <T> T findOne(Connection con, String sql, Class<T> clazz) throws Exception {
		List<T> select = selectBeans(con, sql, 1, 1, createBeanRowMapper(clazz));
		return select.isEmpty() ? null : select.get(0);
	}

	public static List<Map<String, Object>> selectCursor(final Connection con, final String sql, int startRow) throws Exception {
		return selectCursor(con, sql, startRow, -1);
	}

	public static List<Map<String, Object>> selectCursor(final Connection con, final String sql, int startRow, int limitRow)
			throws Exception {
		Properties properties = new Properties();
		properties.put(ResultSetToMapConverter.START_ROW, --startRow);
		return select(con, sql, DEFAULT_FETCH_SIZE, limitRow, READ_ONLY_CURSOR_PREPAREDSTATEMENT_CONVERTER,
				new ResultSetToMapConverter(properties));
	}

	public static <T> List<Map<String, T>> select(final Connection con, final String sql, int fetchCount,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, T>>> convert) throws Exception {
		return select(con, sql, fetchCount, -1, DEFAULT_PREPAREDSTATEMENT_CONVERTER, convert);
	}

	public static <T> List<Map<String, T>> select(final Connection con, final String sql, int fetchCount, int limitedSize,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, T>>> convert) throws Exception {
		return select(con, sql, fetchCount, limitedSize, DEFAULT_PREPAREDSTATEMENT_CONVERTER, convert);
	}

	public static <T> List<Map<String, T>> select(final Connection con, final String sql, int fetchCount, int limitedSize,
			BiFunction<Connection, String, PreparedStatement> prestatementConvert,
			BiFunction<ResultSetMetaData, ResultSet, List<Map<String, T>>> convert) throws Exception {
		List<Map<String, T>> arrayList = Collections.emptyList();

		try {

			noticeQuery(sql);

			PreparedStatement prepareStatement = null;
			ResultSet executeQuery = null;

			/* 쿼리 타임아웃 시간 설정 SEC */
			// int queryTimeout = getQueryTimeout();

			prepareStatement = prestatementConvert.apply(con, sql); // con.prepareStatement(sql);
			// postgre-sql can't
			// prepareStatement.setQueryTimeout(queryTimeout);
			if (prepareStatement != null) {
				if (!(limitedSize <= 0)) {
					prepareStatement.setMaxRows(limitedSize);
				}

				if (fetchCount > 0) {
					prepareStatement.setFetchSize(fetchCount);
				}
				executeQuery = prepareStatement.executeQuery();

				ResultSetMetaData metaData = executeQuery.getMetaData();

				arrayList = convert.apply(metaData, executeQuery);
			}

		} catch (Throwable e) {
			throw e;
		}
		// finally {
		// close();
		// }

		return arrayList;
	}

	public static <T> List<T> selectBeans(final Connection con, String sql, final Class<T> bean) throws Exception {
		RowMapper<T> createBeanRowMapper = createBeanRowMapper(bean);
		return selectBeans(con, sql, 30, -1, createBeanRowMapper);
	}

	public static <T> List<T> selectBeans(final Connection con, String sql, int fetchCount, final Class<T> bean) throws Exception {
		RowMapper<T> createBeanRowMapper = createBeanRowMapper(bean);
		return selectBeans(con, sql, fetchCount, -1, createBeanRowMapper);
	}

	public static <T> List<T> selectBeans(final Connection con, final String sql, int fetchCount, RowMapper<T> mapper) throws Exception {
		return selectBeans(con, sql, fetchCount, -1, mapper);
	}

	public static <T> List<T> selectBeans(final Connection con, final String sql, int fetchCount, int limitedSize, RowMapper<T> mapper)
			throws Exception {
		List<T> resultList = Collections.emptyList();

		try {
			noticeQuery(sql);

			PreparedStatement prepareStatement = null;
			ResultSet executeQuery = null;

			/* 쿼리 타임아웃 시간 설정 SEC */
			// int queryTimeout = getQueryTimeout();

			prepareStatement = con.prepareStatement(sql);
			LOGGER.debug(sql);
			// postgre-sql can't
			// prepareStatement.setQueryTimeout(queryTimeout);
			if (!(limitedSize <= 0)) {
				prepareStatement.setMaxRows(limitedSize);
			}

			if (fetchCount > 0) {
				prepareStatement.setFetchSize(fetchCount);
			}
			executeQuery = prepareStatement.executeQuery();

			RowMapperResultSetExtractor<T> rowMapperResultSetExtractor = new RowMapperResultSetExtractor<T>(mapper);
			resultList = rowMapperResultSetExtractor.extractData(executeQuery);
		} catch (Throwable e) {
			throw e;
		} finally {
			close(con);
		}
		return resultList;
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 *
	 * @param sql
	 * @return
	 * @throws Exception
	 */

	public static <T> List<T> select(final String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper) throws Exception {
		return select(getDataSource(), sql, paramMap, rowMapper);
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 21.
	 * @param sql
	 * @param mapSqlParameterSource
	 * @param rowMapper
	 * @param limit
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> selectLimit(String sql, MapSqlParameterSource mapSqlParameterSource,
			RowMapper<Map<String, Object>> rowMapper, int limit) throws Exception {
		return selectLimit(getDataSource(), sql, mapSqlParameterSource, rowMapper, limit);
	}

	public static <T> List<T> select(DataSource dataSource, final String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper)
			throws Exception {
		return select(dataSource, sql, new MapSqlParameterSource(paramMap), rowMapper);
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 *
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> select(final String sql, MapSqlParameterSource paramMap, RowMapper<T> rowMapper) throws Exception {
		return select(getDataSource(), sql, paramMap, rowMapper);
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 21.
	 * @param dataSource
	 * @param sql
	 * @param paramMap
	 * @param rowMapper
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> select(DataSource dataSource, final String sql, MapSqlParameterSource paramMap, RowMapper<T> rowMapper)
			throws Exception {
		return selectLimit(dataSource, sql, paramMap, rowMapper, -1);
	}

	public static String selectScala(DataSource dataSource, final String sql, Map<String, Object> paramMap) {
		return selectScala(dataSource, sql, new MapSqlParameterSource(paramMap));
	}

	/**
	 * 첫번쨰로우의 첫번쨰 컬럼값 리턴. <br/>
	 * 값이 없는경우 NULL <br/>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 27.
	 * @param dataSource
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public static String selectScala(DataSource dataSource, final String sql, MapSqlParameterSource paramMap) {
		String r = null;
		try {
			noticeQuery(sql);
			NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

			ResultSetExtractor<String> extr = new ResultSetExtractor<String>() {

				@Override
				public String extractData(ResultSet rs) throws SQLException, DataAccessException {
					if (rs.next()) {
						return rs.getString(1);
					}
					return null;
				}
			};
			r = jdbcTemplate.query(sql, paramMap, extr);

		} catch (Exception e) {
			throw e;
		} finally {
			cleanDataSource();
		}
		return r;
	}

	/**
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 21.
	 * @param dataSource
	 * @param sql
	 * @param paramMap
	 * @param rowMapper
	 * @param limit
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> selectLimit(DataSource dataSource, final String sql, MapSqlParameterSource paramMap, RowMapper<T> rowMapper,
			final int limit) throws Exception {
		List<T> query = null;
		try {
			noticeQuery(sql);
			NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

			// String _sql = ValueUtil.getVelocityToText(sql, paramMap);
			// LOGGER.debug(_sql);

			ResultSetExtractor<List<T>> extr = new ResultSetExtractor<List<T>>() {

				@Override
				public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException {

					ArrayList<T> arrayList = new ArrayList<T>();
					int rownum = 1;

					while (rs.next()) {
						T mapRow = rowMapper.mapRow(rs, rownum);
						arrayList.add(mapRow);
						rownum++;

						if ((limit != -1) && (limit <= rownum)) {
							break;
						}

					}
					return arrayList;
				}
			};

			query = jdbcTemplate.query(sql, paramMap, extr);
		} catch (Exception e) {
			throw e;
		} finally {
			cleanDataSource();
			// close(dataSource);
		}
		return query;
	}

	/**
	 * SQL을 실행하고 결과를 반환
	 *
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> select(final String sql, Map<String, Object> paramMap) throws Exception {

		List<Map<String, Object>> arrayList = null;
		Connection connection = getConnection();
		try {

			noticeQuery(sql);

			PreparedStatement prepareStatement = null;
			ResultSet executeQuery = null;

			/* 쿼리 타임아웃 시간 설정 SEC */
			int queryTimeout = getQueryTimeout();

			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setQueryTimeout(queryTimeout);

			executeQuery = prepareStatement.executeQuery();
			/*
			 * if (fetchCount > 0) { executeQuery.setFetchSize(fetchCount); }
			 */

			ResultSetMetaData metaData = executeQuery.getMetaData();
			int columnCount = metaData.getColumnCount();
			arrayList = new ArrayList<Map<String, Object>>();

			while (executeQuery.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int c = 1; c <= columnCount; c++) {
					map.put(metaData.getColumnLabel(c), executeQuery.getString(c));
				}
				arrayList.add(map);
			}

		} catch (Exception e) {
			LOGGER.debug(ValueUtil.toString(e));
			throw e;
		} finally {
			close(connection);
		}

		return arrayList;
	}

	public static int update(Connection con, String query) throws Exception {
		try {
			noticeQuery(query);

			PreparedStatement prepareStatement = con.prepareStatement(query);
			return prepareStatement.executeUpdate();
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			throw e;
		}
	}

	public static int update(String query) throws Exception {
		Connection con = null;

		try {
			noticeQuery(query);

			con = getConnection();
			PreparedStatement prepareStatement = con.prepareStatement(query);
			return prepareStatement.executeUpdate();
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			throw e;
		} finally {
			close(con);
		}
	}

	public static <T> int update(String query, Map<String, Object> map) throws Exception {

		return getTransactionedScope(null, new BiTransactionScope<T, NamedParameterJdbcTemplate>() {

			@Override
			public int scope(T t, NamedParameterJdbcTemplate u) throws Exception {
				return u.update(query, map);
			}
		});
	}

	/**
	 * API 수정.
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 12. 6.
	 * @param query
	 * @param maps
	 * @return
	 * @throws Exception
	 */
	public static <T> int updateList(String query, List<Map<String, Object>> maps) throws Exception {

		return getTransactionedScope(null, new BiTransactionScope<T, NamedParameterJdbcTemplate>() {

			@Override
			public int scope(T t, NamedParameterJdbcTemplate u) throws Exception {

				@SuppressWarnings("unchecked")
				Map<String, ?>[] array = maps.toArray(new HashMap[maps.size()]);
				noticeQuery(query);
				int[] batchUpdate = u.batchUpdate(query, array);
				return IntStream.of(batchUpdate).sum();
			}
		});

		// DataSource dataSource = null;
		// Connection connection = null;
		// try {
		//
		// noticeQuery(query);
		//
		// connection = getDataSource().getConnection();
		// connection.setAutoCommit(false);
		// NamedParameterJdbcTemplate jdbcTemplate = new
		// NamedParameterJdbcTemplate(dataSource);
		// Map<String, ?>[] array = maps.toArray(new HashMap[maps.size()]);
		// jdbcTemplate.batchUpdate(query, array);
		// connection.commit();
		// } catch (Exception e) {
		// connection.rollback();
		// e.printStackTrace();
		// } finally {
		// close(connection);
		// }

	}

	/**
	 * 트랜잭션으로 감싸진 영역을 반환
	 *
	 * @작성자 : KYJ
	 * @작성일 : 2015. 10. 29.
	 * @param consumer
	 * @throws Exception
	 */
	public static <T> int getTransactionedScope(T userObj, BiTransactionScope<T, NamedParameterJdbcTemplate> consumer) {
		return getTransactionedScope(userObj, consumer, null);
	}

	public static <T> int getTransactionedScope(T userObj, BiTransactionScope<T, NamedParameterJdbcTemplate> consumer,
			Consumer<Exception> exceptionHandler) {
		try {
			return getTransactionedScope(getDataSource(), userObj, consumer, exceptionHandler);
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			if (exceptionHandler != null)
				exceptionHandler.accept(e);
		}
		return -1;
	}

	public static <T> int getTransactionedScope(DataSource dataSource, T userObj,
			BiTransactionScope<T, NamedParameterJdbcTemplate> consumer, Consumer<Exception> exceptionHandler) {
		// DataSource dataSource = null;
		try {
			// dataSource = getDataSource();
			NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

			TransactionTemplate template = new TransactionTemplate();
			DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);
			template.setTransactionManager(transactionManager);

			return template.execute(status -> {
				int result = -1;
				try {
					result = consumer.scope(userObj, namedParameterJdbcTemplate);
				} catch (Exception e) {
					status.setRollbackOnly();
					LOGGER.error(ValueUtil.toString(e));
					if (exceptionHandler != null)
						exceptionHandler.accept(e);
					result = -1;
				}
				return result;
			});

		} catch (Exception e) {
			exceptionHandler.accept(e);
		} finally {
			try {
				close(dataSource);
			} catch (Exception e) {
			}
		}
		return -1;
	}

	/**
	 * @작성자 : KYJ
	 * @작성일 : 2017. 9. 18.
	 * @param con
	 * @param userObj
	 * @param sqlConverter
	 * @return
	 * @throws Exception
	 */
	public static <T> int getTransactionedScope(Connection con, T userObj, Function<T, List<String>> sqlConverter) throws Exception {
		return getTransactionedScope(con, userObj, sqlConverter, err -> {
			throw new RuntimeException(err);
		});

	}

	public static <T> int getTransactionedScope(Connection con, T userObj, Function<T, List<String>> sqlConverter,
			Consumer<Exception> exceptionHandler) throws Exception {
		int result = -1;
		try {
			LOGGER.debug("is AutoCommit ? : {}", con.getAutoCommit());
			con.setAutoCommit(false);
			List<String> apply = sqlConverter.apply(userObj);
			Statement createStatement = con.createStatement();
			for (String sql : apply) {

				/*
				 * sqlite에서 공백이 포함된 sql은 add한경우 에러. 확인해보니 isEmpty함수에 이상이 있는듯하여 수정.
				 */
				if (ValueUtil.isEmpty(sql))
					continue;

				LOGGER.debug(sql);
				createStatement.addBatch(sql);
			}

			int[] executeBatch = createStatement.executeBatch();

			con.commit();
			result = (int) IntStream.of(executeBatch).filter(v -> v == 0).count();
		} catch (Exception e) {
			con.rollback();
			exceptionHandler.accept(e);
			result = -1;
		} finally {
			con.commit();
			close(con);
		}
		return result;
	}

	/**
	 * 롤백
	 *
	 * @작성자 : KYJ
	 * @작성일 : 2015. 10. 29.
	 * @param e
	 * @param connection
	 */
	private static void rollback(Exception e, Connection connection) {
		try {
			e.printStackTrace();
			if (connection != null)
				connection.rollback();
		} catch (SQLException e1) {
			LOGGER.error(ValueUtil.toString(e1));
		}
	}

	/**
	 * 쿼리가 데이터 조작 SQL문인지를 체크한다.
	 *
	 * @param query
	 * @return
	 */
	public static boolean isDml(final String query) {
		if (query != null && !query.trim().isEmpty()) {
			final String sql = getSubLowerCase(query);
			return Arrays.stream(dmlkeyword).anyMatch(keyword -> {
				return sql.startsWith(keyword);
			});
		}
		return false;
	}

	public static boolean isNotDml(final String query) {
		return !isDml(query);
	}

	/**
	 * isDml 함수에서 문자열이 너무 긴경우는 substring으로 앞부분만 잘라 확인함.
	 *
	 * @param sql
	 * @return
	 */
	private static String getSubLowerCase(String sql) {
		String lowerCase = null;
		if (sql.length() > 15)
			lowerCase = sql.trim().substring(0, 15).toLowerCase();
		else
			lowerCase = sql.toLowerCase();
		return lowerCase;
	}

	/**
	 * 커넥션의 Driver명을 리턴
	 *
	 * @param connection
	 * @return
	 * @throws SQLException
	 * @throws NotSupportException
	 */
	public static String getDriverNameByConnection(Connection connection) throws SQLException, NotSupportException {
		if (connection == null)
			throw new NullPointerException("Connection is null");

		if (connection != null) {
			Driver dbmsDriver = DriverManager.getDriver(connection.getMetaData().getURL());
			if (dbmsDriver != null) {
				Class<? extends Driver> class1 = dbmsDriver.getClass();
				return class1.getName();
			}
		}
		throw new NotSupportException("could not  found DBMS Driver! ");
	}

	/**
	 * Map에 존재하는 값들을 ''가 붙은 형태로 새로 만들어서 리턴.
	 *
	 * @작성자 : KYJ
	 * @작성일 : 2016. 2. 15.
	 * @param param
	 * @return
	 */
	public static Map<String, Object> replaceDotMapValue(Map<String, Object> param) {
		Iterator<Entry<String, Object>> iterator = param.entrySet().iterator();
		Map<String, Object> map = new LinkedHashMap<String, Object>();
		while (iterator.hasNext()) {
			Entry<String, Object> next = iterator.next();
			Object value = null;
			if (next.getValue() != null)
				value = "'".concat(next.getValue().toString()).concat("'");
			map.put(next.getKey(), value);
		}
		return map;
	}

	/**
	 * 데이터베이스 접속 여부 확인 기본 대기시간 : 2초
	 *
	 * @param conSupplier
	 *            커넥션정보를 제공
	 * @param onSuccess
	 *            접속성공시 처리
	 * @param exHandler
	 *            에러발생시 처리
	 * @return 접속 성공여부
	 */
	public static void ping(Supplier<PoolProperties> conSupplier, Consumer<Boolean> onSuccess, Consumer<Throwable> exHandler) {
		ping(conSupplier, onSuccess, exHandler, 1, false);
	}

	public static void pingAsync(Supplier<PoolProperties> conSupplier, Consumer<Boolean> onSuccess, Consumer<Throwable> exHandler) {
		ping(conSupplier, onSuccess, exHandler, 1, true);
	}

	/**
	 * 데이터베이스 접속 여부 확인
	 *
	 * @param conSupplier
	 *            커넥션정보를 제공
	 * @param onSuccess
	 *            접속성공시 처리
	 * @param exHandler
	 *            에러발생시 처리
	 * @param timeoutSec
	 *            핑테스트 대기시간 (Secound)
	 * @return 접속 성공여부
	 */
	public static void ping(Supplier<PoolProperties> conSupplier, Consumer<Boolean> onSuccess, Consumer<Throwable> exHandler,
			int timeoutSec, boolean async) {

		// boolean result = false;
		PoolProperties prop = conSupplier.get();

		String driverClassName = prop.getDriverClassName();
		String url = prop.getUrl();
		String userName = prop.getUsername();
		String password = prop.getPassword();

		if (async) {
			ExecutorDemons.getGargoyleSystemExecutorSerivce().execute(() -> {
				ping(onSuccess, exHandler, timeoutSec, driverClassName, url, userName, password);
			});
		} else {
			ping(onSuccess, exHandler, timeoutSec, driverClassName, url, userName, password);
		}

	}

	private static void ping(Consumer<Boolean> onSuccess, Consumer<Throwable> exHandler, int timeoutSec, String driverClassName, String url,
			String userName, String password) {
		boolean result;
		try (Connection connection = getConnection(driverClassName, url, userName, password, timeoutSec)) {

			String pingSQL = ConfigResourceLoader.getInstance().get(ConfigResourceLoader.SQL_PING, driverClassName);

			// 리스너들에게 공지
			noticeQuery(pingSQL);

			if (connection != null) {
				// connection.setAutoCommit(false);
				// try {
				// // postgresql was not supported...
				// Oracle에서 관련 에러 발생.. Connection객체를 얻어오는 과정과
				// executeQuery처리결과만으로 판단
				// connection.setNetworkTimeout(Executors.newSingleThreadExecutor(),
				// 5000);
				// } catch (Exception e) {
				//
				// }
				ResultSet executeQuery = connection.createStatement().executeQuery(pingSQL);
				result = executeQuery.next();
				onSuccess.accept(result);
			} else
				exHandler.accept(new Exception("Connection create fail."));
		} catch (Throwable e) {
			exHandler.accept(e);
		}
	}

	/********************************
	 * 작성일 : 2016. 8. 11. 작성자 : KYJ
	 *
	 *
	 * @return
	 * @throws Exception
	 ********************************/
	public static List<String> tables(String tableNamePattern) throws Exception {
		return tables(tableNamePattern, rs -> {
			try {
				return rs.getString(3);
			} catch (SQLException e) {
				LOGGER.error(ValueUtil.toString(e));
			}
			return "";
		});
	}

	/********************************
	 * 작성일 : 2016. 8. 11. 작성자 : KYJ
	 *
	 * 2016-11-10 모든 테이블탐색후 대소문자무시 검색으로 수정 </br>
	 * </br>
	 * 1.TABLE_CAT String => table catalog (may be null) </br>
	 * 2.TABLE_SCHEM String => table schema (may be null) </br>
	 * 3.TABLE_NAME String => table name </br>
	 * 4.TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
	 * "SYNONYM". </br>
	 * 5.REMARKS String => explanatory comment on the table </br>
	 * 6.TYPE_CAT String => the types catalog (may be null) </br>
	 * 7.TYPE_SCHEM String => the types schema (may be null) </br>
	 * 8.TYPE_NAME String => type name (may be null) </br>
	 * 9.SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null) </br>
	 * 10.REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED".
	 * (may be null) </br>
	 * 
	 * @param converter
	 * @return
	 * @throws Exception
	 ********************************/
	public static <T> List<T> tables(String tableNamePattern, Function<ResultSet, T> converter) throws Exception {
		try (Connection connection = getConnection()) {
			return tables(connection, tableNamePattern, converter);
		}
	}

	/********************************
	 * 작성일 : 2016. 8. 11. 작성자 : KYJ
	 *
	 * 2016-11-10 모든 테이블탐색후 대소문자무시 검색으로 수정 2017-07-12 Connection을 파라미터로 넣어 동적으로 찾을 수 있게 수정 </br>
	 * </br>
	 * 1.TABLE_CAT String => table catalog (may be null) </br>
	 * 2.TABLE_SCHEM String => table schema (may be null) </br>
	 * 3.TABLE_NAME String => table name </br>
	 * 4.TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
	 * "SYNONYM". </br>
	 * 5.REMARKS String => explanatory comment on the table </br>
	 * 6.TYPE_CAT String => the types catalog (may be null) </br>
	 * 7.TYPE_SCHEM String => the types schema (may be null) </br>
	 * 8.TYPE_NAME String => type name (may be null) </br>
	 * 9.SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null) </br>
	 * 10.REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED".
	 * (may be null) </br>
	 * 
	 * @param connection
	 * @param converter
	 * @return
	 * @throws Exception
	 ********************************/
	public static <T> List<T> tables(Connection connection, String tableNamePattern, Function<ResultSet, T> converter) throws Exception {
		if (converter == null)
			throw new GargoyleException(GargoyleException.ERROR_CODE.PARAMETER_EMPTY, "converter is null ");

		List<T> tables = new ArrayList<>();

		DatabaseMetaData metaData = connection.getMetaData();

		/* 17.9.19 catalog를 추가하여 조회할 수 있게 코드 수정 */

		ResultSet catalogs = metaData.getCatalogs();
		boolean existsCatalog = false;
		boolean existsSchema = false;

		while (catalogs.next()) {
			existsCatalog = true;
			String catal = catalogs.getString(1);
			ResultSet rs = metaData.getTables(catal, null, "%"/* + tableNamePattern + "%" */, new String[] { "TABLE" });
			String tableNamePatternUpperCase = tableNamePattern.toUpperCase();
			while (rs.next()) {

				// 2016-08-18 특정데이터베이스(sqlite)에서는 인덱스 트리거정보도 동시에 출력된다.
				String tableType = rs.getString(4);
				if ("TABLE".equals(tableType)) {

					String tableName = rs.getString(3);
					if (tableName.toUpperCase().indexOf(tableNamePatternUpperCase) != -1) {
						T apply = converter.apply(rs);
						if (apply != null)
							tables.add(apply);
					}

				}

			}
		}

		if (!existsCatalog) {
			ResultSet schemas = metaData.getSchemas();
			while (schemas.next()) {
				existsSchema = true;
				ResultSet rs = metaData.getTables(null, schemas.getString(0), "%"/* + tableNamePattern + "%" */, new String[] { "TABLE" });
				String tableNamePatternUpperCase = tableNamePattern.toUpperCase();
				while (rs.next()) {

					// 2016-08-18 특정데이터베이스(sqlite)에서는 인덱스 트리거정보도 동시에 출력된다.
					String tableType = rs.getString(4);
					if ("TABLE".equals(tableType)) {

						String tableName = rs.getString(3);
						if (tableName.toUpperCase().indexOf(tableNamePatternUpperCase) != -1) {
							T apply = converter.apply(rs);
							if (apply != null)
								tables.add(apply);
						}

					}
				}
			}
		}

		if (!existsCatalog && !existsSchema) {
			ResultSet rs = metaData.getTables(null, null, "%"/* + tableNamePattern + "%" */, new String[] { "TABLE" });
			String tableNamePatternUpperCase = tableNamePattern.toUpperCase();
			while (rs.next()) {

				// 2016-08-18 특정데이터베이스(sqlite)에서는 인덱스 트리거정보도 동시에 출력된다.
				String tableType = rs.getString(4);
				if ("TABLE".equals(tableType)) {

					String tableName = rs.getString(3);
					if (tableName.toUpperCase().indexOf(tableNamePatternUpperCase) != -1) {
						T apply = converter.apply(rs);
						if (apply != null)
							tables.add(apply);
					}

				}
			}
		}
		return tables;
	}

	public static List<String> columns(String tableNamePattern) throws Exception {
		try (Connection con = getConnection()) {
			return columns(con, tableNamePattern, t -> {
				try {
					return t.getString(4);
				} catch (SQLException e) {
					LOGGER.error(ValueUtil.toString(e));
				}
				return "";
			});
		}
	}

	/**
	 *
	 * 1. TABLE_CAT String => table catalog (may be null) </br>
	 * 2.TABLE_SCHEM String => table schema (may be null) </br>
	 * 3.TABLE_NAME String => table name </br>
	 * 4.COLUMN_NAME String => column name </br>
	 * 5.DATA_TYPE int => SQL type from java.sql.Types </br>
	 * 6.TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified </br>
	 * 7.COLUMN_SIZE int => column size. </br>
	 * 8.BUFFER_LENGTH is not used. </br>
	 * 9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
	 * </br>
	 * 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2) </br>
	 * 11.NULLABLE int => is NULL allowed. ◦ columnNoNulls - might not allow NULL values </br>
	 * ◦ columnNullable - definitely allows NULL values </br>
	 * ◦ columnNullableUnknown - nullability unknown </br>
	 * </br>
	 * 12.REMARKS String => comment describing column (may be null) </br>
	 * 13.COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single
	 * quotes (may be null) </br>
	 * 14.SQL_DATA_TYPE int => unused </br>
	 * 15.SQL_DATETIME_SUB int => unused </br>
	 * 16.CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column </br>
	 * 17.ORDINAL_POSITION int => index of column in table (starting at 1) </br>
	 * 18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column. ◦ YES --- if the column can include NULLs
	 * </br>
	 * ◦ NO --- if the column cannot include NULLs </br>
	 * ◦ empty string --- if the nullability for the column is unknown </br>
	 * </br>
	 * 19.SCOPE_CATALOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF) </br>
	 * 20.SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 21.SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if
	 * DATA_TYPE isn't DISTINCT or user-generated REF) </br>
	 * 23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented ◦ YES --- if the column is auto incremented </br>
	 * ◦ NO --- if the column is not auto incremented </br>
	 * ◦ empty string --- if it cannot be determined whether the column is auto incremented </br>
	 * </br>
	 * 24.IS_GENERATEDCOLUMN String => Indicates whether this is a generated column ◦ YES --- if this a generated column </br>
	 * ◦ NO --- if this not a generated column </br>
	 * ◦ empty string --- if it cannot be determined whether this is a generated column </br>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 11. 10.
	 * @param tableNamePattern
	 * @param converter
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> columns(String tableNamePattern, Function<ResultSet, T> converter) throws Exception {

		try (Connection con = getConnection()) {
			return columns(con, tableNamePattern, converter);
		}
	}

	/**
	 * 1. TABLE_CAT String => table catalog (may be null) </br>
	 * 2.TABLE_SCHEM String => table schema (may be null) </br>
	 * 3.TABLE_NAME String => table name </br>
	 * 4.COLUMN_NAME String => column name </br>
	 * 5.DATA_TYPE int => SQL type from java.sql.Types </br>
	 * 6.TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified </br>
	 * 7.COLUMN_SIZE int => column size. </br>
	 * 8.BUFFER_LENGTH is not used. </br>
	 * 9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
	 * </br>
	 * 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2) </br>
	 * 11.NULLABLE int => is NULL allowed. ◦ columnNoNulls - might not allow NULL values </br>
	 * ◦ columnNullable - definitely allows NULL values </br>
	 * ◦ columnNullableUnknown - nullability unknown </br>
	 * </br>
	 * 12.REMARKS String => comment describing column (may be null) </br>
	 * 13.COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single
	 * quotes (may be null) </br>
	 * 14.SQL_DATA_TYPE int => unused </br>
	 * 15.SQL_DATETIME_SUB int => unused </br>
	 * 16.CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column </br>
	 * 17.ORDINAL_POSITION int => index of column in table (starting at 1) </br>
	 * 18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column. ◦ YES --- if the column can include NULLs
	 * </br>
	 * ◦ NO --- if the column cannot include NULLs </br>
	 * ◦ empty string --- if the nullability for the column is unknown </br>
	 * </br>
	 * 19.SCOPE_CATALOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF) </br>
	 * 20.SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 21.SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if
	 * DATA_TYPE isn't DISTINCT or user-generated REF) </br>
	 * 23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented ◦ YES --- if the column is auto incremented </br>
	 * ◦ NO --- if the column is not auto incremented </br>
	 * ◦ empty string --- if it cannot be determined whether the column is auto incremented </br>
	 * </br>
	 * 24.IS_GENERATEDCOLUMN String => Indicates whether this is a generated column ◦ YES --- if this a generated column </br>
	 * ◦ NO --- if this not a generated column </br>
	 * ◦ empty string --- if it cannot be determined whether this is a generated column </br>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 11. 10.
	 * @param con
	 * @param tableNamePattern
	 * @return
	 * @throws Exception
	 * @Deprecated catalog, schema 파라미터가 존재하는 API 사용할것.
	 */
	@Deprecated
	public static List<String> columns(Connection con, String tableNamePattern) throws Exception {
		return columns(con, tableNamePattern, t -> {
			try {
				return t.getString(4);
			} catch (SQLException e) {
				LOGGER.error(ValueUtil.toString(e));
			}
			return "";
		});
	}

	/**
	 * 테이블 컬럼 조회
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 24.
	 * @param con
	 * @param catalog
	 * @param schema
	 * @param tableNamePattern
	 * @return
	 * @throws Exception
	 */
	public static List<String> columns(Connection con, String catalog, String schema, String tableNamePattern) throws Exception {
		return columns(con, catalog, schema, tableNamePattern, t -> {
			try {
				return t.getString(4);
			} catch (SQLException e) {
				LOGGER.error(ValueUtil.toString(e));
			}
			return "";
		});
	}

	/**
	 * 1. TABLE_CAT String => table catalog (may be null) </br>
	 * 2.TABLE_SCHEM String => table schema (may be null) </br>
	 * 3.TABLE_NAME String => table name </br>
	 * 4.COLUMN_NAME String => column name </br>
	 * 5.DATA_TYPE int => SQL type from java.sql.Types </br>
	 * 6.TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified </br>
	 * 7.COLUMN_SIZE int => column size. </br>
	 * 8.BUFFER_LENGTH is not used. </br>
	 * 9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
	 * </br>
	 * 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2) </br>
	 * 11.NULLABLE int => is NULL allowed. ◦ columnNoNulls - might not allow NULL values </br>
	 * ◦ columnNullable - definitely allows NULL values </br>
	 * ◦ columnNullableUnknown - nullability unknown </br>
	 * </br>
	 * 12.REMARKS String => comment describing column (may be null) </br>
	 * 13.COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single
	 * quotes (may be null) </br>
	 * 14.SQL_DATA_TYPE int => unused </br>
	 * 15.SQL_DATETIME_SUB int => unused </br>
	 * 16.CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column </br>
	 * 17.ORDINAL_POSITION int => index of column in table (starting at 1) </br>
	 * 18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column. ◦ YES --- if the column can include NULLs
	 * </br>
	 * ◦ NO --- if the column cannot include NULLs </br>
	 * ◦ empty string --- if the nullability for the column is unknown </br>
	 * </br>
	 * 19.SCOPE_CATALOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF) </br>
	 * 20.SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 21.SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF) </br>
	 * 22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if
	 * DATA_TYPE isn't DISTINCT or user-generated REF) </br>
	 * 23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented ◦ YES --- if the column is auto incremented </br>
	 * ◦ NO --- if the column is not auto incremented </br>
	 * ◦ empty string --- if it cannot be determined whether the column is auto incremented </br>
	 * </br>
	 * 24.IS_GENERATEDCOLUMN String => Indicates whether this is a generated column ◦ YES --- if this a generated column </br>
	 * ◦ NO --- if this not a generated column </br>
	 * ◦ empty string --- if it cannot be determined whether this is a generated column </br>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 11. 10.
	 * @param connection
	 * @param tableNamePattern
	 * @param converter
	 * @return
	 * @throws Exception
	 * @Deprecated catalog, schema 파라미터가 존재하는 API 사용할것.
	 */
	@Deprecated
	public static <T> List<T> columns(Connection connection, String tableNamePattern, Function<ResultSet, T> converter) throws Exception {
		return columns(connection, null, null, tableNamePattern, COLUMN_CONVERTER, converter);
	}

	/**
	 * @작성자 : KYJ
	 * @작성일 : 2017. 9. 18.
	 * @param connection
	 * @param catalog
	 * @param tableNamePattern
	 * @param converter
	 * @return
	 * @throws Exception
	 * 
	 */
	public static <T> List<T> columns(Connection connection, String catalog, String schema, String tableNamePattern,
			Function<ResultSet, T> converter) throws Exception {
		return columns(connection, catalog, schema, tableNamePattern, COLUMN_CONVERTER, converter);
	}

	/**
	 * @작성자 : KYJ
	 * @작성일 : 2017. 9. 15.
	 * @param connection
	 * @param tableNamePattern
	 * @param columnNameConverter
	 * @param converter
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> columns(Connection connection, String catalog, String schema, String tableNamePattern,
			FourThFunction<String, String, String, DatabaseMetaData, ResultSet> columnNameConverter, Function<ResultSet, T> converter)
			throws Exception {
		if (converter == null)
			throw new GargoyleException(GargoyleException.ERROR_CODE.PARAMETER_EMPTY, "converter is null ");

		List<T> tables = new ArrayList<>();
		// try (Connection connection = getConnection()) {

		DatabaseMetaData metaData = connection.getMetaData();
		ResultSet rs = columnNameConverter.apply(catalog, schema, tableNamePattern, metaData); // metaData.getColumns(null,
		// null,
		// tableNamePattern,
		// null);

		while (rs.next()) {
			tables.add(converter.apply(rs));
		}
		// }

		return tables;
	}

	public static <K, T> Map<K, T> columnsToMap(Connection connection, String tableNamePattern, Function<ResultSet, K> keyMapper,
			Function<ResultSet, T> valueMapper) throws Exception {
		if (keyMapper == null || valueMapper == null)
			throw new GargoyleException(GargoyleException.ERROR_CODE.PARAMETER_EMPTY, "converter is null ");

		Map<K, T> tables = new LinkedHashMap<>();
		// try (Connection connection = getConnection()) {

		DatabaseMetaData metaData = connection.getMetaData();
		ResultSet rs = COLUMN_CONVERTER.apply(null, null, tableNamePattern, metaData);

		while (rs.next()) {
			K k = keyMapper.apply(rs);
			if (k == null)
				continue;
			T t = valueMapper.apply(rs);
			tables.put(k, t);
		}
		return tables;
	}

	public static final FourThFunction<String, String, String, DatabaseMetaData, ResultSet> COLUMN_CONVERTER = (catalog, schema,
			tableNamePattern, metaData) -> {

		try {
			return metaData.getColumns(catalog, schema, tableNamePattern, null);
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
		}
		return null;
	};

	public static final FourThFunction<String, String, String, DatabaseMetaData, ResultSet> PRIMARY_CONVERTER = (catalog, schema,
			tableNamePattern, metaData) -> {
		try {

			return metaData.getPrimaryKeys(catalog, schema, tableNamePattern);
		} catch (Exception e) {
			// LOGGER.error(ValueUtil.toString(e));
		}
		return null;
	};

	public static List<String> pks(String tableNamePattern) throws Exception {
		return pks(getConnection(), null, null, tableNamePattern, t -> {
			try {
				return t.getString(4);
			} catch (SQLException e) {
				LOGGER.error(ValueUtil.toString(e));
			}
			return "";
		});
	}

	public static List<String> pks(Connection con, String tableNamePattern) throws Exception {
		return pks(con, null, null, tableNamePattern, t -> {
			try {
				return t.getString(4);
			} catch (SQLException e) {
				LOGGER.error(ValueUtil.toString(e));
			}
			return "";
		});
	}

	public static <T> List<T> pks(String tableNamePattern, Function<ResultSet, T> converter) throws Exception {
		List<T> tables = Collections.emptyList();
		try (Connection connection = getConnection()) {
			tables = pks(connection, null, null, tableNamePattern, converter);
		}
		return tables;
	}

	public static <T> List<T> pks(Connection connection, String catalog, String schema, String tableNamePattern,
			Function<ResultSet, T> converter) throws Exception {
		if (converter == null)
			throw new GargoyleException(GargoyleException.ERROR_CODE.PARAMETER_EMPTY, "converter is null ");

		List<T> tables = new ArrayList<>();

		DatabaseMetaData metaData = connection.getMetaData();

		ResultSet rs = PRIMARY_CONVERTER.apply(catalog, schema, tableNamePattern, metaData); // metaData.getPrimaryKeys(null,
		// null,
		// tableNamePattern);

		if (rs != null) {
			while (rs.next()) {
				tables.add(converter.apply(rs));
			}
		}

		return tables;
	}

	/**
	 *
	 * // 16.09.01 >> 쿼리로 부터 테이블을 찾아옴 퍼옴 by Hong
	 *
	 * @param sql
	 * @return
	 */
	public static String getTableNames(String sql) {
		// 2016-11-10 문자열 관련된 처리므로 ValueUtil로 이동.
		return ValueUtil.getTableNames(sql);
	}

	/********************************
	 * 작성일 : 2016. 9. 3. 작성자 : KYJ
	 *
	 * 스키마라는 개념이 존재하는 데이터베이스인지 유무
	 *
	 * @return
	 ********************************/
	public static boolean isExistsSchemaDatabase() {

		String driver = DbUtil.getDriver().trim();
		if (driver == null || driver.isEmpty())
			return true;
		return isExistsSchemaDatabase(driver);
	}

	/********************************
	 * 작성일 : 2016. 9. 3. 작성자 : KYJ
	 *
	 * 스키마라는 개념이 존재하는 데이터베이스인지 유무
	 *
	 * @param con
	 * @return
	 ********************************/
	public static boolean isExistsSchemaDatabase(Connection con) {
		try {
			String driverName = con.getMetaData().getDriverName();
			return isExistsSchemaDatabase(driverName);
		} catch (SQLException e) {
			// Nothing.
		}
		return false;
	}

	/********************************
	 * 작성일 : 2016. 9. 3. 작성자 : KYJ
	 *
	 * 스키마라는 개념이 존재하는 데이터베이스인지 유무
	 *
	 * @param driver
	 * @return
	 ********************************/
	public static boolean isExistsSchemaDatabase(String driver) {

		String drivers = ConfigResourceLoader.getInstance().get(ConfigResourceLoader.NOT_EXISTS_SCHEMA_DRIVER_NAMES);
		if (drivers != null && !driver.isEmpty()) {
			drivers = drivers.trim();
			Optional<String> findFirst = Stream.of(drivers.split(",")).filter(v -> v.equals(driver)).findFirst();
			return !findFirst.isPresent();
		}
		return true;
	}

	public static List<String> getSqlFunctions(final Connection con, boolean autoClose) throws SQLException {
		try {
			DatabaseMetaData metaData = con.getMetaData();

			String stringFunctions = metaData.getStringFunctions();
			String numericFunctions = metaData.getNumericFunctions();
			String timeDateFunctions = metaData.getTimeDateFunctions();

			return Stream.of(stringFunctions.split(","), numericFunctions.split(","), timeDateFunctions.split(",")).flatMap(v -> {
				return Stream.of(v);
			}).collect(Collectors.toList());

		} catch (Exception e) {

		} finally {

			if (autoClose)
				con.close();
		}
		return Collections.emptyList();
	}

	public static void getCurrentSchema(Connection con) {

	}

	/**
	 * 빈프로퍼티 로우 매퍼클래스를 생성후 리턴
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 4. 4.
	 * @param clazz
	 * @return
	 */
	public static <T> RowMapper<T> createBeanRowMapper(Class<T> clazz) {
		return ParameterizedBeanPropertyRowMapper.newInstance(clazz);
	}

	/**
	 * find procedure.
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 9. 12.
	 * @param connection
	 * @param cat
	 * @param schem
	 * @param procedureNamePattern
	 * @return
	 * @throws SQLException
	 */
	public static List<String> findProcedure(Connection connection, String cat, String schem, String procedureNamePattern)
			throws SQLException {
		List<String> items = new ArrayList<String>();
		try {

			ResultSet rs = connection.getMetaData().getProcedures(cat, schem, "%" + procedureNamePattern + "%");

			while (rs.next()) {
				items.add(String.format("%s.%s.%s", rs.getString(1), rs.getString(2), rs.getString(3)));
			}

		} catch (SQLException e) {
			throw e;
		}

		return items;

	}

	public static List<String> findProcedure(Connection connection, String procedureNamePattern) throws SQLException {
		List<String> items = new ArrayList<String>();

		ResultSet catalogs = connection.getMetaData().getCatalogs();
		while (catalogs.next()) {

			List<String> findProcedure = findProcedure(connection, catalogs.getString(1), null, procedureNamePattern);
			items.addAll(findProcedure);
		}
		return items;
	}

	public static String decryp(Object str) {
		if (ValueUtil.isEmpty(str))
			return "";

		try {
			return EncrypUtil.decryp(str.toString());
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			throw new RuntimeException(e.toString());
		}
	}

	/**
	 * 처리 가능한 데이터베이스 접속 목록 정보를 로드한다. <br/>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 10. 31.
	 * @return
	 */
	public static List<Map<String, Object>> getAvailableConnections() {

		ResourceLoader instance = ResourceLoader.getInstance();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Enumeration<Object> keySet = instance.keySet();
		while (keySet.hasMoreElements()) {
			Object _key = keySet.nextElement();
			if (_key == null)
				continue;
			String key = (String) _key;
			if (!key.startsWith("database.info."))
				continue;

			String value = instance.get(key);
			if ("jdbc.pass".equals(key))
				value = decryp(value);
			JSONObject json = ValueUtil.toJSONObject(value);
			Map<String, Object> map = new HashMap<String, Object>(json);
			map.put("seqNum", key);
			list.add(0, map);
		}
		return list;
	}

	// TODO 구현가능한부분인지 확인.
	// public void cancel(Connection activeConnection) {
	// try {
	// if (activeConnection != null) {
	// if (!activeConnection.isClosed()) {
	//
	// activeConnection.abort(new Executor() {
	//
	// @Override
	// public void execute(Runnable command) {
	// LOGGER.debug("abort.....");
	// }
	// });
	// }
	// }
	//
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// }

	public static Connection getConnection(Map<String, Object> map) throws Exception {
		String jdbcDriver = ConfigResourceLoader.getInstance().get("dbms." + map.get(ResourceLoader.DBMS.toString()));
		String password = map.get(ResourceLoader.BASE_KEY_JDBC_PASS) == null ? "" : map.get(ResourceLoader.BASE_KEY_JDBC_PASS).toString();
		password = EncrypUtil.decryp(password);

		return getConnection(jdbcDriver, map.get(ResourceLoader.BASE_KEY_JDBC_URL).toString(),
				map.get(ResourceLoader.BASE_KEY_JDBC_ID) == null ? "" : map.get(ResourceLoader.BASE_KEY_JDBC_ID).toString(), password);

	}

	/**
	 * 프로시저 컬럼 정보를 찾는다.
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 17.
	 * @param con
	 * @param catalog
	 * @param schemaPattern
	 * @param procedureNamePattern
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> getProcedureColumns(Connection con, String catalog, String schemaPattern,
			String procedureNamePattern) throws SQLException {
		return getProcedureColumns(con, catalog, schemaPattern, procedureNamePattern, null);
	}

	/**
	 * 프로시저 컬럼 정보를 찾는다.
	 * 
	 * <OL>
	 * <LI><B>PROCEDURE_CAT</B> String {@code =>} procedure catalog (may be <code>null</code>)
	 * <LI><B>PROCEDURE_SCHEM</B> String {@code =>} procedure schema (may be <code>null</code>)
	 * <LI><B>PROCEDURE_NAME</B> String {@code =>} procedure name
	 * <LI><B>COLUMN_NAME</B> String {@code =>} column/parameter name
	 * <LI><B>COLUMN_TYPE</B> Short {@code =>} kind of column/parameter:
	 * <UL>
	 * <LI>procedureColumnUnknown - nobody knows
	 * <LI>procedureColumnIn - IN parameter
	 * <LI>procedureColumnInOut - INOUT parameter
	 * <LI>procedureColumnOut - OUT parameter
	 * <LI>procedureColumnReturn - procedure return value
	 * <LI>procedureColumnResult - result column in <code>ResultSet</code>
	 * </UL>
	 * <LI><B>DATA_TYPE</B> int {@code =>} SQL type from java.sql.Types
	 * <LI><B>TYPE_NAME</B> String {@code =>} SQL type name, for a UDT type the type name is fully qualified
	 * <LI><B>PRECISION</B> int {@code =>} precision
	 * <LI><B>LENGTH</B> int {@code =>} length in bytes of data
	 * <LI><B>SCALE</B> short {@code =>} scale - null is returned for data types where SCALE is not applicable.
	 * <LI><B>RADIX</B> short {@code =>} radix
	 * <LI><B>NULLABLE</B> short {@code =>} can it contain NULL.
	 * <UL>
	 * <LI>procedureNoNulls - does not allow NULL values
	 * <LI>procedureNullable - allows NULL values
	 * <LI>procedureNullableUnknown - nullability unknown
	 * </UL>
	 * <LI><B>REMARKS</B> String {@code =>} comment describing parameter/column
	 * <LI><B>COLUMN_DEF</B> String {@code =>} default value for the column, which should be interpreted as a string when the value is
	 * enclosed in single quotes (may be <code>null</code>)
	 * <UL>
	 * <LI>The string NULL (not enclosed in quotes) - if NULL was specified as the default value
	 * <LI>TRUNCATE (not enclosed in quotes) - if the specified default value cannot be represented without truncation
	 * <LI>NULL - if a default value was not specified
	 * </UL>
	 * <LI><B>SQL_DATA_TYPE</B> int {@code =>} reserved for future use
	 * <LI><B>SQL_DATETIME_SUB</B> int {@code =>} reserved for future use
	 * <LI><B>CHAR_OCTET_LENGTH</B> int {@code =>} the maximum length of binary and character based columns. For any other datatype the
	 * returned value is a NULL
	 * <LI><B>ORDINAL_POSITION</B> int {@code =>} the ordinal position, starting from 1, for the input and output parameters for a
	 * procedure. A value of 0 is returned if this row describes the procedure's return value. For result set columns, it is the ordinal
	 * position of the column in the result set starting from 1. If there are multiple result sets, the column ordinal positions are
	 * implementation defined.
	 * <LI><B>IS_NULLABLE</B> String {@code =>} ISO rules are used to determine the nullability for a column.
	 * <UL>
	 * <LI>YES --- if the column can include NULLs
	 * <LI>NO --- if the column cannot include NULLs
	 * <LI>empty string --- if the nullability for the column is unknown
	 * </UL>
	 * <LI><B>SPECIFIC_NAME</B> String {@code =>} the name which uniquely identifies this procedure within its schema.
	 * </OL>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 17.
	 * @param con
	 * @param catalog
	 * @param schemaPattern
	 * @param procedureNamePattern
	 * @param columnNamePattern
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> getProcedureColumns(Connection con, String catalog, String schemaPattern,
			String procedureNamePattern, String columnNamePattern) throws SQLException {

		// List<Map<String, Object>> r = Collections.emptyList();
		// try {
		// ResultSet resultSet = con.getMetaData().getProcedureColumns(catalog, schemaPattern, procedureNamePattern, columnNamePattern);

		//
		// r = c.apply(resultSet.getMetaData(), resultSet);
		// } catch (Exception e) {
		// LOGGER.error(ValueUtil.toString(e));
		// throw e;
		// }

		ResultSetToMapConverter c = new ResultSetToMapConverter();
		return getProcedureColumns(con, catalog, schemaPattern, procedureNamePattern, columnNamePattern, rs -> {
			try {
				return c.apply(rs.getMetaData(), rs);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		});

		// return r;
	}

	/**
     *  <OL>
     *  <LI><B>PROCEDURE_CAT</B> String {@code =>} procedure catalog (may be <code>null</code>)
     *  <LI><B>PROCEDURE_SCHEM</B> String {@code =>} procedure schema (may be <code>null</code>)
     *  <LI><B>PROCEDURE_NAME</B> String {@code =>} procedure name
     *  <LI><B>COLUMN_NAME</B> String {@code =>} column/parameter name
     *  <LI><B>COLUMN_TYPE</B> Short {@code =>} kind of column/parameter:
     *      <UL>
     *      <LI> procedureColumnUnknown - nobody knows
     *      <LI> procedureColumnIn - IN parameter
     *      <LI> procedureColumnInOut - INOUT parameter
     *      <LI> procedureColumnOut - OUT parameter
     *      <LI> procedureColumnReturn - procedure return value
     *      <LI> procedureColumnResult - result column in <code>ResultSet</code>
     *      </UL>
     *  <LI><B>DATA_TYPE</B> int {@code =>} SQL type from java.sql.Types
     *  <LI><B>TYPE_NAME</B> String {@code =>} SQL type name, for a UDT type the
     *  type name is fully qualified
     *  <LI><B>PRECISION</B> int {@code =>} precision
     *  <LI><B>LENGTH</B> int {@code =>} length in bytes of data
     *  <LI><B>SCALE</B> short {@code =>} scale -  null is returned for data types where
     * SCALE is not applicable.
     *  <LI><B>RADIX</B> short {@code =>} radix
     *  <LI><B>NULLABLE</B> short {@code =>} can it contain NULL.
     *      <UL>
     *      <LI> procedureNoNulls - does not allow NULL values
     *      <LI> procedureNullable - allows NULL values
     *      <LI> procedureNullableUnknown - nullability unknown
     *      </UL>
     *  <LI><B>REMARKS</B> String {@code =>} comment describing parameter/column
     *  <LI><B>COLUMN_DEF</B> String {@code =>} default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be <code>null</code>)
     *      <UL>
     *      <LI> The string NULL (not enclosed in quotes) - if NULL was specified as the default value
     *      <LI> TRUNCATE (not enclosed in quotes)        - if the specified default value cannot be represented without truncation
     *      <LI> NULL                                     - if a default value was not specified
     *      </UL>
     *  <LI><B>SQL_DATA_TYPE</B> int  {@code =>} reserved for future use
     *  <LI><B>SQL_DATETIME_SUB</B> int  {@code =>} reserved for future use
     *  <LI><B>CHAR_OCTET_LENGTH</B> int  {@code =>} the maximum length of binary and character based columns.  For any other datatype the returned value is a
     * NULL
     *  <LI><B>ORDINAL_POSITION</B> int  {@code =>} the ordinal position, starting from 1, for the input and output parameters for a procedure. A value of 0
     *is returned if this row describes the procedure's return value.  For result set columns, it is the
     *ordinal position of the column in the result set starting from 1.  If there are
     *multiple result sets, the column ordinal positions are implementation
     * defined.
     *  <LI><B>IS_NULLABLE</B> String  {@code =>} ISO rules are used to determine the nullability for a column.
     *       <UL>
     *       <LI> YES           --- if the column can include NULLs
     *       <LI> NO            --- if the column cannot include NULLs
     *       <LI> empty string  --- if the nullability for the
     * column is unknown
     *       </UL>
     *  <LI><B>SPECIFIC_NAME</B> String  {@code =>} the name which uniquely identifies this procedure within its schema.
     *  </OL>
     *  
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 29.
	 * @param con
	 * @param catalog
	 * @param schemaPattern
	 * @param procedureNamePattern
	 * @param columnNamePattern
	 * @param callback
	 * @return
	 * @throws SQLException
	 */
	public static <T> T getProcedureColumns(Connection con, String catalog, String schemaPattern, String procedureNamePattern,
			String columnNamePattern, Callback<ResultSet, T> callback) throws SQLException {

		List<Map<String, Object>> r = Collections.emptyList();
		try {
			ResultSet resultSet = con.getMetaData().getProcedureColumns(catalog, schemaPattern, procedureNamePattern, columnNamePattern);
			// ResultSetToMapConverter c = new ResultSetToMapConverter();

			return callback.call(resultSet);
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			throw e;
		}
	}

	/**
	 * 프로시저에 대한 정보를 리턴 <br/>
	 *
	 * <ol>
	 * <li>PROCEDURE_CAT String => procedure catalog (may be null)</li>
	 * <li>PROCEDURE_SCHEM String => procedure schema (may be null)</li>
	 * <li>PROCEDURE_NAME String => procedure name</li>
	 * </ol>
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2017. 11. 17.
	 * @param con
	 * @param catalog
	 * @param schemaPattern
	 * @param procedureNamePattern
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> getProcedures(Connection con, String catalog, String schemaPattern, String procedureNamePattern)
			throws SQLException {

		List<Map<String, Object>> r = Collections.emptyList();
		try {
			ResultSet resultSet = con.getMetaData().getProcedures(catalog, schemaPattern, procedureNamePattern);
			ResultSetToMapConverter c = new ResultSetToMapConverter();

			r = c.apply(resultSet.getMetaData(), resultSet);
		} catch (Exception e) {
			LOGGER.error(ValueUtil.toString(e));
			throw e;
		}
		return r;
	}

	public static Optional<ResultSet> executeProcedure(Connection con, String _sql, TreeMap<String, Object> param,
			ExceptionHandler handler) {

		Optional<ResultSet> op = Optional.empty();
		if (ValueUtil.isEmpty(_sql))
			return null;

		// if the _sql is dynamic sql , convert param by velocity context.
		String sql = ValueUtil.getVelocityToText(_sql, param);
		if (ValueUtil.isNotEmpty(param)) {
			sql = ValueUtil.getVelocityToText(_sql, param);
		}
		try {

			CallableStatement prepareCall = con.prepareCall(sql);

			Set<String> keySet = param.keySet();

			int seq = 1;
			for (String key : keySet) {
				Object val = param.get(key);
				prepareCall.setObject(seq, val);
				seq++;
			}

			ResultSet rs = prepareCall.executeQuery();

			op = Optional.of(rs);
		} catch (Exception e) {
			if (handler != null)
				handler.handle(e);
			else
				throw new RuntimeException(e);
		}

		return op;
	}

}