com.hangum.tadpole.commons.sql/src/com/hangum/tadpole/engine/sql/util/executer/ExecuteDDLCommand.java

   1 /*******************************************************************************
   2  * Copyright (c) 2013 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.sql.util.executer;
  12
  13 import java.sql.SQLException;
  14 import java.sql.Statement;
  15 import java.sql.Timestamp;
  16 import java.util.HashMap;
  17 import java.util.Map;
  18
  19 import org.apache.log4j.Logger;
  20
  21 import com.hangum.tadpole.commons.dialogs.message.dao.RequestResultDAO;
  22 import com.hangum.tadpole.commons.exception.TadpoleSQLManagerException;
  23 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine;
  24 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine.QUERY_DDL_STATUS;
  25 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine.QUERY_DDL_TYPE;
  26 import com.hangum.tadpole.engine.define.DBGroupDefine;
  27 import com.hangum.tadpole.engine.manager.TadpoleSQLManager;
  28 import com.hangum.tadpole.engine.query.sql.TadpoleSystem_ExecutedSQL;
  29 import com.hangum.tadpole.engine.query.sql.TadpoleSystem_SchemaHistory;
  30 import com.hangum.tadpole.engine.sql.parser.ddl.ParserDDL;
  31 import com.hangum.tadpole.engine.sql.parser.dto.QueryInfoDTO;
  32 import com.hangum.tadpole.engine.sql.util.OracleDbmsOutputUtil;
  33 import com.hangum.tadpole.engine.sql.util.SQLConvertCharUtil;
  34 import com.hangum.tadpole.engine.utils.RequestQuery;
  35 import com.hangum.tadpole.session.manager.SessionManager;
  36
  37 /**
  38  * tadpole system에서 공통으로 사용하는 DDL 모듈
  39  *
  40  * @author hangum
  41  *
  42  */
  43 public class ExecuteDDLCommand {
  44         /**
  45          * Logger for this class
  46          */
  47         private static final Logger logger = Logger.getLogger(ExecuteDDLCommand.class);
  48
  49         /**
  50          * 쿼리중에 quote sql을 반영해서 작업합니다.
  51          *
  52          * @param userDB
  53          * @param reqResultDAO
  54          * @param reqQuery
  55          * @throws Exception
  56          */
  57         public static RequestQuery executSQL(RequestQuery reqQuery) throws Exception {
  58                 if(logger.isDebugEnabled()) logger.debug("\t ### "+ reqQuery.getSql());
  59
  60                 RequestResultDAO reqResultDAO = new RequestResultDAO();
  61                 reqResultDAO.setStartDateExecute(new Timestamp(System.currentTimeMillis()));
  62                 reqResultDAO.setIpAddress(reqQuery.getUserIp());
  63                 reqResultDAO.setSql_text(reqQuery.getSql());
  64
  65                 try {
  66                         QueryInfoDTO queryInfoDTO = new QueryInfoDTO();
  67                         ParserDDL parser = new ParserDDL();
  68                         parser.parseQuery(reqQuery.getSql(), queryInfoDTO);
  69                         Map<String, Object> resultMap = _executSQL(queryInfoDTO.getQueryStatus(), queryInfoDTO.getQueryDDLType(), queryInfoDTO.getObjectName(), reqQuery);
  70
  71                         reqResultDAO.setDataChanged((Boolean)resultMap.get("result"));
  72                         reqResultDAO.setMesssage((String)resultMap.get("dbms_output"));
  73                 } catch(Exception e) {
  74                         logger.error("execute sql", e);
  75                         reqResultDAO.setResult(PublicTadpoleDefine.SUCCESS_FAIL.F.name()); //$NON-NLS-1$
  76                         reqResultDAO.setMesssage(e.getMessage());
  77                         reqResultDAO.setException(e);
  78
  79                         throw e;
  80                 } finally {
  81                         reqResultDAO.setEndDateExecute(new Timestamp(System.currentTimeMillis()));
  82                         reqQuery.setRequestResultDao(reqResultDAO);
  83
  84                         // 히스토리 정보 메타디비에 저장
  85                         TadpoleSystem_ExecutedSQL.insertExecuteHistory(
  86                                         SessionManager.getUserSeq(), reqQuery.getUserDB(),
  87                                                 reqResultDAO,
  88                                                 null
  89                                                 );
  90                 }
  91
  92                 return reqQuery;
  93         }
  94
  95         /**
  96          * 쿼리중에 quote sql을 반영해서 작업합니다.
  97          *
  98          * @param userDB
  99          * @param queryDDLStatus
 100          * @param query_DDL_TYPE
 101          * @param reqQuery
 102          * @throws TadpoleSQLManagerException, SQLException
 103          */
 104         private static Map<String, Object> _executSQL(QUERY_DDL_STATUS queryDDLStatus, QUERY_DDL_TYPE query_DDL_TYPE, String objName, RequestQuery reqQuery) throws TadpoleSQLManagerException, SQLException {
 105                 Map<String, Object> resultMap = new HashMap<String, Object>();
 106
 107                 if(queryDDLStatus == PublicTadpoleDefine.QUERY_DDL_STATUS.CREATE ||
 108                         queryDDLStatus == PublicTadpoleDefine.QUERY_DDL_STATUS.DROP ||
 109                         queryDDLStatus == PublicTadpoleDefine.QUERY_DDL_STATUS.ALTER
 110                 ) {
 111
 112                         java.sql.Connection javaConn = null;
 113                         OracleDbmsOutputUtil dbmsOutput = null;
 114                         Statement stmt = null;
 115                         try {
 116                                 javaConn = TadpoleSQLManager.getConnection(reqQuery.getUserDB());
 117
 118                                 stmt = javaConn.createStatement();
 119
 120                                 if (DBGroupDefine.ORACLE_GROUP == reqQuery.getUserDB().getDBGroup()){
 121                                         dbmsOutput = new OracleDbmsOutputUtil( javaConn );
 122                                         dbmsOutput.enable( 1000000 );
 123                                         resultMap.put("result", stmt.execute(SQLConvertCharUtil.toServer(reqQuery.getUserDB(), reqQuery.getSql())));
 124                                         dbmsOutput.show();
 125                                         resultMap.put("dbms_output", dbmsOutput.getOutput());
 126                                 }else{
 127                                         resultMap.put("result", stmt.execute(SQLConvertCharUtil.toServer(reqQuery.getUserDB(), reqQuery.getSql())));
 128                                         resultMap.put("dbms_output", "");
 129                                 }
 130
 131                                 // 성공시 변경 히스토리를 입력한다.
 132                                 TadpoleSystem_SchemaHistory.save(SessionManager.getUserSeq(), reqQuery.getUserDB(),
 133                                                 queryDDLStatus.name(), //$NON-NLS-1$
 134                                                 query_DDL_TYPE.name(),
 135                                                 objName,
 136                                                 reqQuery.getSql());
 137
 138                         } finally {
 139
 140                                 try { if(stmt != null) stmt.close(); } catch(Exception e) {}
 141                                 try { if(dbmsOutput != null) dbmsOutput.close(); } catch(Exception e) {}
 142                                 try { if(javaConn != null) javaConn.close(); } catch(Exception e) {}
 143                         }
 144                 }
 145                 return resultMap;
 146         }
 147 }