com.hangum.tadpole.commons.sql/src/com/hangum/tadpole/engine/query/surface/BasicDBInfo.java

   1 /*******************************************************************************
   2  * Copyright (c) 2017 hangum.
   3  * All rights reserved. This program and the accompanying materials
   4  * are made available under the terms of the GNU Lesser Public License v2.1
   5  * which accompanies this distribution, and is available at
   6  * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
   7  *
   8  * Contributors:
   9  *     hangum - initial API and implementation
  10  ******************************************************************************/
  11 package com.hangum.tadpole.engine.query.surface;
  12
  13 import java.sql.Connection;
  14 import java.sql.DatabaseMetaData;
  15 import java.sql.PreparedStatement;
  16 import java.sql.ResultSet;
  17 import java.sql.Statement;
  18 import java.util.ArrayList;
  19 import java.util.HashMap;
  20 import java.util.List;
  21 import java.util.Map;
  22
  23 import org.apache.log4j.Logger;
  24
  25 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine.SQL_STATEMENT_TYPE;
  26 import com.hangum.tadpole.engine.query.dao.mysql.TableColumnDAO;
  27 import com.hangum.tadpole.engine.query.dao.mysql.TableDAO;
  28 import com.hangum.tadpole.engine.query.dao.system.UserDBDAO;
  29 import com.hangum.tadpole.engine.sql.util.PartQueryUtil;
  30 import com.hangum.tadpole.engine.sql.util.resultset.QueryExecuteResultDTO;
  31
  32 /**
  33  * JDBC 메타데이터를 이용하여 스키마, 테이블, 컬럼 정보를 요청합니다,
  34  *
  35  * @author hangum
  36  *
  37  */
  38 public abstract class BasicDBInfo implements ConnectionInterfact {
  39         private static final Logger logger = Logger.getLogger(BasicDBInfo.class);
  40
  41         public abstract Connection getConnection(final UserDBDAO userDB) throws Exception;
  42
  43         /**
  44          * @param userDB
  45          * @return
  46          * @throws Exception
  47          */
  48         public String getKeyworkd(final UserDBDAO userDB) throws Exception {
  49                 String strKeyWord = "";
  50
  51                 Connection javaConn = null;
  52                 try {
  53                         javaConn = getConnection(userDB);
  54                         strKeyWord = javaConn.getMetaData().getSQLKeywords();
  55                 } finally {
  56                         try { if(javaConn != null) javaConn.close(); } catch(Exception e){}
  57                 }
  58
  59                 return strKeyWord;
  60         }
  61
  62         /**
  63          * not select
  64          *
  65          * @param userDB
  66          * @param sqlQuery
  67          * @return
  68          */
  69         public int executeUpdate(UserDBDAO userDB, String sqlQuery) throws Exception {
  70                 Statement statement = null;
  71
  72                 Connection javaConn = null;
  73                 try {
  74                         javaConn = getConnection(userDB);
  75                         statement = javaConn.createStatement();
  76                         return statement.executeUpdate(sqlQuery);
  77                 } finally {
  78                         try { if(statement != null) statement.close(); } catch(Exception e) {}
  79                         try { if(javaConn != null) javaConn.close(); } catch(Exception e){}
  80                 }
  81         }
  82
  83         /**
  84          * execute query plan
  85          *
  86          * @param objects
  87          * @param sql_STATEMENT_TYPE
  88          * @param statementParameter
  89          *
  90          * @return
  91          * @throws Exception
  92          */
  93         public QueryExecuteResultDTO executeQueryPlan(UserDBDAO userDB, String strQuery, SQL_STATEMENT_TYPE sql_STATEMENT_TYPE, Object[] statementParameter) throws Exception {
  94                 return select(userDB, PartQueryUtil.makeExplainQuery(userDB, strQuery), statementParameter, 1000);
  95         }
  96
  97         /**
  98          * execute update
  99          *
 100          * @param javaConn
 101          * @param userDB
 102          * @param string
 103          * @param name
 104          * @throws Exception
 105          */
 106         public int executeUpdate(UserDBDAO userDB, String string, String name) throws Exception {
 107                 Statement statement = null;
 108
 109                 Connection javaConn = null;
 110                 try {
 111                         javaConn = getConnection(userDB);
 112                         String quoteString = javaConn.getMetaData().getIdentifierQuoteString();
 113
 114                         statement = javaConn.createStatement();
 115                         return statement.executeUpdate(String.format(string, quoteString + name + quoteString));
 116                 } finally {
 117                         try { if(statement != null) statement.close(); } catch(Exception e) {}
 118                         try { if(javaConn != null) javaConn.close(); } catch(Exception e){}
 119                 }
 120         }
 121
 122         /**
 123          * 연결 테스트 합니다.
 124          *
 125          * @param userDB
 126          */
 127         public void connectionCheck(UserDBDAO userDB) throws Exception {
 128
 129                 Connection javaConn = null;
 130                 try {
 131                         javaConn = getConnection(userDB);
 132                         DatabaseMetaData dbmd = javaConn.getMetaData();
 133                         // just test
 134                 dbmd.getCatalogSeparator();
 135
 136                 } catch(Exception e) {
 137                         logger.error("connection check", e);
 138                         throw e;
 139                 } finally {
 140                         try { if(javaConn != null) javaConn.close(); } catch(Exception e) {}
 141                 }
 142         }
 143
 144         /**
 145          * table 정보를 리턴합니다.
 146          *
 147          * @param userDB
 148          * @throws Exception
 149          */
 150         public List<TableDAO> tableList(UserDBDAO userDB) throws Exception {
 151                 List<TableDAO> showTables = new ArrayList<TableDAO>();
 152
 153                 ResultSet rs = null;
 154                 Connection javaConn = null;
 155                 try {
 156                         javaConn = getConnection(userDB);
 157                         DatabaseMetaData dbmd = javaConn.getMetaData();
 158                 rs = dbmd.getTables(null, null, "%", null);
 159
 160                 while(rs.next()) {
 161                         String strTBName = rs.getString("TABLE_NAME");
 162                         String strComment = rs.getString("REMARKS");
 163 //                      logger.debug( rs.getString("TABLE_CAT") );
 164 //                      logger.debug( rs.getString("TABLE_SCHEM") );
 165 //                      logger.debug( rs.getString("TABLE_NAME") );
 166 //                      logger.debug( rs.getString("TABLE_TYPE") );
 167 //                      logger.debug( rs.getString("REMARKS") );
 168
 169                         TableDAO tdao = new TableDAO(strTBName, strComment);
 170                         showTables.add(tdao);
 171                 }
 172
 173                 return showTables;
 174                 } catch(Exception e) {
 175                         logger.error("table list", e);
 176                         throw e;
 177                 } finally {
 178                         try { if(rs != null) rs.close(); } catch(Exception e) {}
 179                         try { if(javaConn != null) javaConn.close(); } catch(Exception e) {}
 180                 }
 181
 182         }
 183
 184         /**
 185          * table의 컬컴 정보를 리턴합니다.
 186          *
 187          * @param userDB
 188          * @param tbName
 189          * @throws Exception
 190          */
 191         public List<TableColumnDAO> tableColumnList(UserDBDAO userDB, Map<String, String> mapParam) throws Exception {
 192                 List<TableColumnDAO> showTableColumns = new ArrayList<TableColumnDAO>();
 193                 ResultSet rs = null;
 194                 ResultSet rsPrimaryKey = null;
 195
 196                 Map<String, String> mapPrimaryKey = new HashMap<String, String>();
 197
 198                 Connection javaConn = null;
 199                 try {
 200                         javaConn = getConnection(userDB);
 201                         DatabaseMetaData dbmd = javaConn.getMetaData();
 202                 rs = dbmd.getColumns(null, null, mapParam.get("table"), null);
 203
 204                 rsPrimaryKey = dbmd.getPrimaryKeys(null, null, mapParam.get("table"));
 205                 while(rsPrimaryKey.next()) {
 206                         mapPrimaryKey.put(rsPrimaryKey.getString("COLUMN_NAME"), "PRI");
 207                 }
 208
 209                 while(rs.next()) {
 210                         TableColumnDAO tcDAO = new TableColumnDAO();
 211                         tcDAO.setName(rs.getString("COLUMN_NAME"));
 212                         if(mapPrimaryKey.containsKey(tcDAO.getName())) {
 213                                 tcDAO.setKey("PRI");
 214                         }
 215                         tcDAO.setType(rs.getString("TYPE_NAME"));
 216                         tcDAO.setNull(rs.getString("IS_NULLABLE"));
 217                         tcDAO.setComment(rs.getString("REMARKS"));
 218
 219                         showTableColumns.add(tcDAO);
 220                 }
 221
 222                 return showTableColumns;
 223                 } catch(Exception e) {
 224                         logger.error(mapParam.get("table") + " table column", e);
 225                         throw e;
 226                 } finally {
 227                         try { if(rsPrimaryKey != null) rsPrimaryKey.close(); } catch(Exception e) {}
 228                         try { if(rs != null) rs.close(); } catch(Exception e) {}
 229                         try { if(javaConn != null) javaConn.close(); } catch(Exception e) {}
 230                 }
 231
 232         }
 233
 234         /**
 235          * select
 236          *
 237          * @param userDB
 238          * @param requestQuery
 239          * @param statementParameter
 240          * @param limitCount
 241          *
 242          * @throws Exception
 243          */
 244         public QueryExecuteResultDTO select(UserDBDAO userDB, String requestQuery, Object[] statementParameter, int limitCount) throws Exception {
 245                 if(logger.isDebugEnabled()) logger.debug("\t * Query is [ " + requestQuery );
 246
 247                 PreparedStatement pstmt = null;
 248                 ResultSet rs = null;
 249
 250                 Connection javaConn = null;
 251                 try {
 252                         javaConn = getConnection(userDB);
 253                         pstmt = javaConn.prepareStatement(requestQuery);
 254                         if(statementParameter != null) {
 255                                 for (int i=1; i<=statementParameter.length; i++) {
 256                                         pstmt.setObject(i, statementParameter[i-1]);
 257                                 }
 258                         }
 259                         rs = pstmt.executeQuery();
 260
 261                         return new QueryExecuteResultDTO(userDB, requestQuery, true, rs, limitCount);
 262                 } catch(Exception e) {
 263                         logger.error(e);
 264                         throw e;
 265
 266                 } finally {
 267                         try { if(pstmt != null) pstmt.close(); } catch(Exception e) {}
 268                         try { if(rs != null) rs.close(); } catch(Exception e) {}
 269                         try { if(javaConn != null) javaConn.close(); } catch(Exception e){}
 270                 }
 271         }
 272 }