/**
* 데이터 수정.
*
* @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;
}
