/******************************** * 프로젝트 : 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; } }