/** * 데이터 수정. * * @param userDB * @param title * @param desc * @param cronExp * @param listSchedule * @return * @throws Exception */ public static ScheduleMainDAO modifySchedule( final UserDBDAO userDB, ScheduleMainDAO scheduleDao, List<ScheduleDAO> listSchedule) throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); sqlClient.update("scheduleMainUpdate", scheduleDao); // 기존 데이터를 삭제합니다. sqlClient.update("deleteSchedule", scheduleDao.getSeq()); // save schedule for (ScheduleDAO scheduleDAO : listSchedule) { scheduleDAO.setSchedule_main_seq(scheduleDao.getSeq()); ScheduleDAO retScheduleDAO = (ScheduleDAO) sqlClient.insert("scheduleInsert", scheduleDAO); // sql String[] sqls = SQLUtil.makeResourceDataArays(scheduleDAO.getSql()); ScheduleDetailDAO detailDao = null; for (String sql : sqls) { detailDao = new ScheduleDetailDAO(); detailDao.setSchedule_seq(retScheduleDAO.getSeq()); detailDao.setDatas(sql); sqlClient.insert("scheduleDetailInsert", detailDao); } } return scheduleDao; }
/** * schedule result * * @param seq * @param isResult * @param msg */ public static void saveScheduleResult(int seq, boolean isResult, String msg) throws Exception { ScheduleResultDAO dao = new ScheduleResultDAO(); dao.setSchedule_main_seq(seq); dao.setResult( isResult ? PublicTadpoleDefine.YES_NO.YES.name() : PublicTadpoleDefine.YES_NO.NO.name()); dao.setDescription(msg); SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); sqlClient.insert("scheduleResultInsert", dao); }
/** * sequenc * * @param dao * @return * @throws TadpoleSQLManagerException, SQLException */ public static TadpoleSequenceDAO getSequence(TadpoleSequenceDAO dao) throws TadpoleSQLManagerException, SQLException { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); dao = (TadpoleSequenceDAO) sqlClient.queryForObject("lastSequene", dao); dao.setNo(dao.getNo() + 1); sqlClient.update("updateSequence", dao); return dao; }
/** * 마지막 실행했떤 쿼리 100개를 리턴합니다. * * @param user_seq * @param dbSeq * @return * @throws Exception */ public static List<SQLHistoryDAO> getExecuteQueryHistory(int user_seq, int dbSeq, String filter) throws Exception { List<SQLHistoryDAO> returnSQLHistory = new ArrayList<SQLHistoryDAO>(); Map<String, Object> queryMap = new HashMap<String, Object>(); queryMap.put("user_seq", user_seq); queryMap.put("db_seq", dbSeq); queryMap.put("filter", "%" + filter + "%"); queryMap.put("count", 50); SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); List<java.util.Map> listResourceData = sqlClient.queryForList("getExecuteQueryHistory", queryMap); for (Map resultMap : listResourceData) { int seq = (Integer) resultMap.get("executed_sql_resource_seq"); Long startdateexecute = 0l; // This case sqlite if (resultMap.get("startdateexecute") instanceof Long) { startdateexecute = (Long) resultMap.get("startdateexecute"); // This case mysql } else { startdateexecute = ((Timestamp) resultMap.get("startdateexecute")).getTime(); } String strSQLText = (String) resultMap.get("datas"); Long enddateexecute = 0l; // This case sqlite if (resultMap.get("enddateexecute") instanceof Long) { enddateexecute = (Long) resultMap.get("enddateexecute"); // This case mysql } else { enddateexecute = ((Timestamp) resultMap.get("enddateexecute")).getTime(); } String strMessage = (String) resultMap.get("message"); int row = (Integer) resultMap.get("row"); String result = (String) resultMap.get("result"); SQLHistoryDAO dao = new SQLHistoryDAO( new Timestamp(startdateexecute), strSQLText, new Timestamp(enddateexecute), row, result, strMessage); dao.setSeq(seq); returnSQLHistory.add(dao); } return returnSQLHistory; }
/** * resource data * * @param userDBResource * @param contents * @throws Exception */ private static void insertResourceData(ExecutedSqlResourceDAO userDBResource, String contents) throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); // content data를 저장합니다. ExecutedSqlResourceDataDAO dataDao = new ExecutedSqlResourceDataDAO(); dataDao.setExecuted_sql_resource_seq(userDBResource.getSeq()); String[] arrayContent = SQLUtil.makeResourceDataArays(contents); for (String content : arrayContent) { dataDao.setDatas(content); sqlClient.insert("userExecuteSQLResourceDataInsert", dataDao); // $NON-NLS-1$ } }
/** * seq * * @param seq {@link ScheduleDAO#getSeq()} * @return * @throws Exception */ public static List<ScheduleDAO> findSchedule(final int seq) throws Exception { List<ScheduleDAO> listSchedule = new ArrayList<ScheduleDAO>(); SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); listSchedule = sqlClient.queryForList("getSchedule", seq); for (ScheduleDAO scheduleDAO : listSchedule) { List<ScheduleDetailDAO> listDetail = sqlClient.queryForList("getScheduleDetail", scheduleDAO.getSeq()); StringBuffer sbSQL = new StringBuffer(); for (ScheduleDetailDAO scheduleDetailDAO : listDetail) { sbSQL.append(scheduleDetailDAO.getDatas()); } scheduleDAO.setSql(sbSQL.toString()); } return listSchedule; }
/** * @param userDB * @param title * @param desc * @param cronExp * @param listSchedule */ public static ScheduleMainDAO addSchedule( final UserDBDAO userDB, String title, String desc, String cronExp, List<ScheduleDAO> listSchedule) throws Exception { int userSeq = SessionManager.getUserSeq(); ScheduleMainDAO dao = new ScheduleMainDAO(); dao.setSeq(userSeq); dao.setUser_seq(SessionManager.getUserSeq()); dao.setDb_seq(userDB.getSeq()); dao.setTitle(title); dao.setDescription(desc); dao.setCron_exp(cronExp); SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); dao = (ScheduleMainDAO) sqlClient.insert("scheduleMainInsert", dao); // save schedule for (ScheduleDAO scheduleDAO : listSchedule) { scheduleDAO.setSchedule_main_seq(dao.getSeq()); ScheduleDAO retScheduleDAO = (ScheduleDAO) sqlClient.insert("scheduleInsert", scheduleDAO); // sql String[] sqls = SQLUtil.makeResourceDataArays(scheduleDAO.getSql()); ScheduleDetailDAO detailDao = null; for (String sql : sqls) { detailDao = new ScheduleDetailDAO(); detailDao.setSchedule_seq(retScheduleDAO.getSeq()); detailDao.setDatas(sql); sqlClient.insert("scheduleDetailInsert", detailDao); } } return dao; }
/** * save sqlhistory * * @param user_seq * @param userDB * @param sqlType * @param sqlHistoryDAO */ public static void saveExecuteSQUeryResource( int user_seq, UserDBDAO userDB, PublicTadpoleDefine.EXECUTE_SQL_TYPE sqlType, SQLHistoryDAO sqlHistoryDAO) throws Exception { if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getIs_profile())) { ExecutedSqlResourceDAO executeSQLResourceDao = new ExecutedSqlResourceDAO(); executeSQLResourceDao.setDb_seq(userDB.getSeq()); executeSQLResourceDao.setUser_seq(user_seq); executeSQLResourceDao.setTypes(sqlType.toString()); executeSQLResourceDao.setStartDateExecute(sqlHistoryDAO.getStartDateExecute()); executeSQLResourceDao.setEndDateExecute(sqlHistoryDAO.getEndDateExecute()); long duration = sqlHistoryDAO.getEndDateExecute().getTime() - sqlHistoryDAO.getStartDateExecute().getTime(); executeSQLResourceDao.setDuration(Integer.parseInt("" + duration)); executeSQLResourceDao.setRow(sqlHistoryDAO.getRows()); executeSQLResourceDao.setResult(sqlHistoryDAO.getResult()); executeSQLResourceDao.setMessage( sqlHistoryDAO .getMesssage()); // ""+(sqlHistoryDAO.getEndDateExecute().getTime() - // sqlHistoryDAO.getStartDateExecute().getTime())); executeSQLResourceDao.setIpAddress(sqlHistoryDAO.getIpAddress()); // 기존에 등록 되어 있는지 검사한다 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); ExecutedSqlResourceDAO executeSQL = (ExecutedSqlResourceDAO) sqlClient.insert( "userExecuteSQLResourceInsert", executeSQLResourceDao); // $NON-NLS-1$ insertResourceData(executeSQL, sqlHistoryDAO.getStrSQLText()); } }
/** * find user schedule * * @return * @throws Exception */ public static List<ScheduleMainDAO> findUserScheduleMain() throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); return sqlClient.queryForList("getUserScheduleMain", SessionManager.getUserSeq()); }
/** * get result * * @param seq * @return * @throws Exception */ public static List<ScheduleResultDAO> getScheduleResult(int seq) throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); return sqlClient.queryForList("getScheduleResult", seq); }
/** * delete schedule * * @param seq * @throws Exception */ public static void deleteSchedule(int seq) throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); sqlClient.update("deleteScheduleMain", seq); }
/** * find schedule main * * @param seq {@link ScheduleMainDAO#getSeq()} * @return * @throws Exception */ public static ScheduleMainDAO findScheduleMain(final int seq) throws Exception { SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); return (ScheduleMainDAO) sqlClient.queryForObject("getScheduleMain", seq); }
/** * 쿼리 실행 히스토리 디테일 창을 얻습니다. * * @param strType * @param dbSeq * @param executeTime * @param durationLimit * @return * @throws Exception */ public static List<SQLHistoryDAO> getExecuteQueryHistoryDetail( String strType, String dbSeq, long startTime, long endTime, int duringExecute, String strSearch) throws Exception { List<SQLHistoryDAO> returnSQLHistory = new ArrayList<SQLHistoryDAO>(); Map<String, Object> queryMap = new HashMap<String, Object>(); queryMap.put("db_seq", dbSeq); queryMap.put("type", strType); if (ApplicationArgumentUtils.isDBServer()) { Date date = new Date(startTime); DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS"); queryMap.put("startTime", formatter.format(date)); Date dateendTime = new Date(endTime); queryMap.put("endTime", formatter.format(dateendTime)); } else { queryMap.put("startTime", startTime); queryMap.put("endTime", endTime); } queryMap.put("duration", duringExecute); queryMap.put("count", 1000); queryMap.put("strSearch", strSearch); SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleSystemInitializer.getUserDB()); List<java.util.Map> listResourceData = new ArrayList<Map>(); if (PublicTadpoleDefine.EXECUTE_SQL_TYPE.API.name().endsWith(strType)) { listResourceData = sqlClient.queryForList("getExecuteQueryHistoryAPIDetail", queryMap); } else { listResourceData = sqlClient.queryForList("getExecuteQueryHistoryDetail", queryMap); } for (Map resultMap : listResourceData) { int seq = (Integer) resultMap.get("executed_sql_resource_seq"); Long startdateexecute = 0l; String strSQLText = (String) resultMap.get("datas"); Long enddateexecute = 0l; if (ApplicationArgumentUtils.isDBServer()) { startdateexecute = ((Timestamp) resultMap.get("startdateexecute")).getTime(); enddateexecute = ((Timestamp) resultMap.get("enddateexecute")).getTime(); } else { startdateexecute = (Long) resultMap.get("startdateexecute"); enddateexecute = (Long) resultMap.get("enddateexecute"); } int row = (Integer) resultMap.get("row"); String result = (String) resultMap.get("result"); String userName = resultMap.get("name") == null ? "" : (String) resultMap.get("name"); String dbName = (String) resultMap.get("display_name"); String ipAddress = (String) resultMap.get("ipaddress"); int dbSeq2 = (Integer) resultMap.get("dbseq"); String strMessage = (String) resultMap.get("message"); SQLHistoryDAO dao = new SQLHistoryDAO( userName, dbName, new Timestamp(startdateexecute), strSQLText, new Timestamp(enddateexecute), row, result, strMessage, ipAddress, dbSeq2); dao.setSeq(seq); if (PublicTadpoleDefine.EXECUTE_SQL_TYPE.API.name().endsWith(strType)) { dao.setEXECUSTE_SQL_TYPE(PublicTadpoleDefine.EXECUTE_SQL_TYPE.API); } else { dao.setEXECUSTE_SQL_TYPE(PublicTadpoleDefine.EXECUTE_SQL_TYPE.EDITOR); } returnSQLHistory.add(dao); } return returnSQLHistory; }