Yunsam.Eo
410 lines
13 KiB
Plaintext
410 lines
13 KiB
Plaintext
<%@page import="com.eactive.eai.rms.common.datasource.DataSourceTypeManager"%>
|
|
<%@ page language="java" contentType="text/html; charset=utf-8"%>
|
|
<%@ page import="java.io.*,
|
|
java.util.*,
|
|
javax.xml.parsers.*,
|
|
org.w3c.dom.*,
|
|
javax.xml.xpath.*,
|
|
java.sql.*,
|
|
javax.sql.*,
|
|
com.eactive.eai.agent.command.*,
|
|
com.eactive.eai.agent.*,
|
|
com.eactive.eai.rms.onl.common.util.*,
|
|
com.eactive.eai.rms.common.datasource.*,
|
|
org.apache.commons.logging.Log,
|
|
org.apache.commons.logging.LogFactory,
|
|
java.util.Arrays,
|
|
com.eactive.eai.rms.common.util.StringUtils,
|
|
com.eactive.eai.common.util.*
|
|
" %>
|
|
<!-- import 에서 DataSourceFactory의 경로에 파일이 없어서 컴파일 에러가 나서 주석처리함 24.08.29 -->
|
|
<!-- com.eactive.eai.transformer.dao.jdbc.DataSourceFactory, -->
|
|
<%!
|
|
private static final Logger logger = Logger.getLogger("runSql.jsp");
|
|
|
|
private String NEW_LINE = "\n";
|
|
private int MAX_FETCH_SIZE = 1000;
|
|
|
|
private static Object obj = new Object();
|
|
|
|
public StringBuffer ListToString(List<String[]> list){
|
|
StringBuffer sb = new StringBuffer();
|
|
//first item is ColName
|
|
boolean isHead = true;
|
|
for(int row=1;row<list.size();row++){
|
|
String[] ar = (String[])list.get(row);
|
|
for(int i =0; i < ar.length; i++){
|
|
sb.append(ar[i]).append("\t");
|
|
}
|
|
sb.append(NEW_LINE);
|
|
if(isHead){
|
|
sb.append(StringUtils.repeat("--",sb.length())).append(NEW_LINE);
|
|
isHead= false;
|
|
}
|
|
}
|
|
return sb;
|
|
}
|
|
|
|
public HashMap runSql(List<String> list,String displayType, String serviceType ) throws Exception {
|
|
Connection conn = null;
|
|
Statement stmt = null;
|
|
String driver = null;
|
|
String url = null;
|
|
String id =null;
|
|
String pwd=null;
|
|
ResultSet rs = null;
|
|
|
|
HashMap<String, Object> results = new HashMap();
|
|
StringBuffer sb = new StringBuffer();
|
|
|
|
StringBuffer errorBuff = new StringBuffer();
|
|
|
|
int rownum = 0;
|
|
try {
|
|
DataSourceType dataType = DataSourceTypeManager.getDataSourceType(serviceType);
|
|
|
|
String jndiName = dataType.getJndiName();
|
|
ServiceLocator sl = ServiceLocator.getInstance();
|
|
DataSource ds = sl.getDataSource( jndiName);
|
|
|
|
conn = ds.getConnection();
|
|
stmt = conn.createStatement();
|
|
|
|
int cnt = 0;
|
|
for(String sql : list) {
|
|
try{
|
|
|
|
sql = sql.trim();
|
|
if(sql.length() == 0) continue;
|
|
// System.out.println(sql);
|
|
if(sql.toUpperCase().startsWith("SELECT")){
|
|
|
|
// 메모리 문제로 조회건수를 100건으로 제한하도록 한다. // 요건에 따라 변경 필요
|
|
sql = sql + "\n fetch first "+MAX_FETCH_SIZE+" rows only"; // DB2
|
|
stmt.execute(sql);
|
|
rs = stmt.getResultSet();
|
|
|
|
List ret = new ArrayList();
|
|
int maxColumn = rs.getMetaData().getColumnCount();
|
|
ResultSetMetaData rsmd = rs.getMetaData();
|
|
String[] colName = new String[maxColumn];
|
|
int[] colSize = new int[maxColumn];
|
|
//get Col Name
|
|
for(int i =1; i<=maxColumn; i++){
|
|
colName[i-1] = rsmd.getColumnName(i);
|
|
colSize[i-1] = rsmd.getPrecision(i);
|
|
}
|
|
ret.add(colSize);
|
|
ret.add(colName);
|
|
while(rs.next()) {
|
|
String[] row = new String[maxColumn];
|
|
for (int i=0;i<maxColumn;i++){
|
|
row[i] = rs.getString(i+1);
|
|
}
|
|
ret.add(row);
|
|
}
|
|
if( "FLAT".equals(displayType) ) sb = ListToString(ret);
|
|
if( "GRID".equals(displayType) ) results.put("SEL"+(cnt++),ret);
|
|
} else { //INSERT, UPDATE, DELETE
|
|
throw new Exception("SELECT 만 지원합니다.");
|
|
//if ((sql.toUpperCase().startsWith("UPDATE") || sql.toUpperCase().startsWith("DELETE")) && !(sql.toUpperCase().contains("WHERE"))) {
|
|
// throw new Exception("UPDATE/DELETE문은 반드시 WHERE절을 포함해야 합니다.");
|
|
//}
|
|
//int affected = stmt.executeUpdate(sql);
|
|
//sb.append("==> Execute - ["+sql.trim()+"], affected "+ affected +" rows").append(NEW_LINE);
|
|
}
|
|
|
|
}catch(Exception e){
|
|
errorBuff.append("==> Error - ["+sql + "] : " +e.getMessage()).append(NEW_LINE);
|
|
}
|
|
}
|
|
}catch(Exception e) {
|
|
logger.error(e.getMessage(),e);
|
|
throw e;
|
|
}
|
|
finally {
|
|
try {
|
|
if(stmt != null) stmt.close();
|
|
if(conn != null) conn.close();
|
|
if(rs != null) rs.close();
|
|
}
|
|
catch (SQLException sqle) {
|
|
logger.error("SQLException was thrown: " + sqle.getMessage());
|
|
}
|
|
}
|
|
|
|
HashMap retMap = new HashMap();
|
|
retMap.put("records",results);
|
|
retMap.put("result", sb.toString());
|
|
retMap.put("error", errorBuff.toString());
|
|
return retMap;
|
|
}
|
|
|
|
public HashMap execute(String statements, String displayType,String serviceType,ServletRequest request) throws Exception {
|
|
String[] results = new String[2];
|
|
|
|
String[] arr = statements.split(";");
|
|
List<String> list = Arrays.asList(arr);
|
|
|
|
return runSql(list, displayType, serviceType);
|
|
}
|
|
%>
|
|
|
|
|
|
<%
|
|
response.setHeader("Pragma", "No-cache");
|
|
response.setHeader("Cache-Control", "no-cache");
|
|
response.setHeader("Expires", "0");
|
|
%>
|
|
|
|
<%
|
|
request.setCharacterEncoding("utf-8");
|
|
String filter = request.getParameter("filter");
|
|
|
|
String displayType = request.getParameter("displayType");
|
|
String serviceType = request.getParameter("serviceType");
|
|
String statement = request.getParameter("statement");
|
|
String userType=request.getParameter("userType");
|
|
String run=request.getParameter("run");
|
|
|
|
if (run == null) run = "";
|
|
if (displayType == null) displayType = "GRID";
|
|
if (serviceType == null) serviceType = "";
|
|
if (statement == null) statement = "";
|
|
|
|
HashMap runResult =new HashMap();
|
|
|
|
String errorMessage = "";
|
|
|
|
if (!"".equals(run) ){
|
|
runResult = execute(statement,displayType, serviceType,request);
|
|
//response.sendRedirect("/monitoring/jsp/common/jsonResult.jsp");
|
|
errorMessage = (String)runResult.get("error");
|
|
if(errorMessage == null) errorMessage = "";
|
|
}else{
|
|
;
|
|
}
|
|
%>
|
|
<html>
|
|
<head>
|
|
<title></title>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
|
|
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
|
|
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>
|
|
<%@ include file="/jsp/common/include/localemessage.jsp" %>
|
|
<script language="javascript" src="<c:url value="/common/js/common.js" />"></script>
|
|
<jsp:include page="/jsp/common/include/css.jsp"/>
|
|
<jsp:include page="/jsp/common/include/script.jsp"/>
|
|
<%-- <script language="javascript" src="<c:url value="/js/jquery-1.7.2.min.js"/>"></script>
|
|
<script language="javascript" src="<c:url value="/js/jquery-ui.min.js"/>"></script> --%>
|
|
<script language="javascript" >
|
|
var $ = jQuery.noConflict();
|
|
var url = '<c:url value="/runSql.jsp" />';
|
|
|
|
$(document).ready(function() {
|
|
$('select[name=serviceType]').val("<%=serviceType%>");
|
|
//$('select[name=serviceType]').attr('disabled','disabled');
|
|
$("#btn_submit").click(function(){
|
|
var serviceType = $('select[name=serviceType] option:selected').val();
|
|
var postData = new Array();
|
|
var statement = $("textarea[name=statement]").val();
|
|
if(statement == "" || statement == null){
|
|
alert("<%= localeMessage.getString("runSql.checkMsg") %>");
|
|
return;
|
|
}
|
|
document.forms.runSql.run.value = "1";
|
|
document.forms.runSql.action= url;
|
|
document.forms.runSql.submit();
|
|
/* postData.push({name: "statement" , value:statement});
|
|
postData.push({name: "serviceType", value:serviceType});
|
|
postData.push({name: "run", value:"1"});
|
|
$.ajax({
|
|
type : "POST",
|
|
//url:url,
|
|
data:postData,
|
|
success:function(args){
|
|
alert("executed");
|
|
|
|
$("textarea[name=result]").val($(args).find('result'));
|
|
},
|
|
error:function(xhr, status, errorMsg){
|
|
//$("textarea[name=result]").val(errorMsg);
|
|
//alert(JSON.parse(xhr.responseText).errorMsg);
|
|
}
|
|
}); */
|
|
|
|
});
|
|
|
|
$("#btn_close").click(function(){
|
|
window.close();
|
|
});
|
|
|
|
$("#btn_download").click(function() {
|
|
var displayType = $("select[name=displayType]").val();
|
|
if(displayType=="FLAT"){
|
|
download();
|
|
}else{
|
|
$("#resultData").val($("#resultTable").html());
|
|
$("#excel_form").submit();
|
|
}
|
|
});
|
|
|
|
});
|
|
function download(){
|
|
var resData = document.getElementById("resultData").value;
|
|
var resDataBlob = new Blob([resData],{type:'text/plain'});
|
|
var fileName = "SQL Result.txt";
|
|
var downLink = document.createElement("a");
|
|
downLink.download = fileName;
|
|
downLink.innerHTML = "Download File";
|
|
downLink.href = window.webkitURL.createObjectURL(resDataBlob);
|
|
downLink.click();
|
|
}
|
|
|
|
</script>
|
|
</head>
|
|
<body>
|
|
<div class="right_box">
|
|
<div class="content_top">
|
|
<ul class="path">
|
|
<li><a href="#">${rmsMenuPath}</a></li>
|
|
</ul>
|
|
</div><!-- end content_top -->
|
|
<div class="content_middle">
|
|
<div class="search_wrap">
|
|
<button type="button" class="cssbtn" id="btn_submit" level="W"><i class="material-icons">expand_circle_down</i> <%= localeMessage.getString("button.operate") %></button>
|
|
<button type="button" class="cssbtn" id="btn_download" level="W"><i class="material-icons">file_download</i> <%= localeMessage.getString("button.download") %></button>
|
|
<%-- <img id="btn_submit" src="<c:url value="/img/btn_operate.png"/>" level="W" style="cursor:hand;"/> --%>
|
|
<%-- <img id="btn_download" src="<c:url value="/img/btn_download.png"/>" level="w" style="cursor:hand;"/> --%>
|
|
</div>
|
|
<div class="title">Data Explorer</div>
|
|
<table class="table_row" align="center">
|
|
<form method="post" name="runSql">
|
|
<tr>
|
|
<th align="right" width="80px">
|
|
Database :
|
|
</th>
|
|
<td align="left" width="200px">
|
|
<div class="select-style">
|
|
<select name="serviceType">
|
|
<%
|
|
List dsNames = DataSourceTypeManager.getDataSourceTypeNames();
|
|
String dsName = "";
|
|
for(int i=0; i< dsNames.size(); i++) {
|
|
dsName = (String)dsNames.get(i);
|
|
%>
|
|
<option value="<%= dsName %>"><%= dsName %></option>
|
|
<%
|
|
}
|
|
%>
|
|
</select>
|
|
</div>
|
|
</td>
|
|
<th align="right" width="80px">
|
|
DIsplay :
|
|
</th>
|
|
<td align="left">
|
|
<div class="select-style">
|
|
<select name="displayType">
|
|
<option value="FLAT" <%= "FLAT".equals(displayType)?"selected":"" %>>FLAT</option>
|
|
<option value="GRID" <%= "GRID".equals(displayType)?"selected":"" %>>GRID</option>
|
|
</select>
|
|
</div>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" colspan=4>
|
|
<table width="100%">
|
|
<tr>
|
|
<td class="search_td_title" width="150px"><%= localeMessage.getString("runSql.excute") %> : max fetch size <%= MAX_FETCH_SIZE %></td>
|
|
</tr>
|
|
<tr>
|
|
<td>
|
|
<textarea name="statement" style="width:100%;height:200px"><%= statement%></textarea>
|
|
<INPUT type=hidden name="run" value="<%=run%>">
|
|
</td>
|
|
</tr>
|
|
</form>
|
|
<tr>
|
|
<td class="search_td_title" width="150px"><%= localeMessage.getString("runSql.result") %></td>
|
|
</tr>
|
|
<tr>
|
|
<td class="search_td_title" width="150px"><font color=red><%= errorMessage %><font></td>
|
|
</tr>
|
|
<%
|
|
if("FLAT".equals(displayType)) {
|
|
%>
|
|
<tr>
|
|
<td >
|
|
<%-- <form name="excel_form" id = "excel_form" action="excelDown.jsp" method="post" target="_blank" style="height : 450px !important">
|
|
<input type="text" name="displayType" id="displayType" value="<%=displayType%>">
|
|
<textarea id = "resultData" name="resultData" style="width:100%;height:100%"><%= runResult.get("result")%></textarea>
|
|
</form> --%>
|
|
<textarea id = "resultData" name="resultData" style="width:100%;height:400px"><%= runResult.get("result")%></textarea>
|
|
</td>
|
|
</tr>
|
|
|
|
<%
|
|
}
|
|
else {
|
|
HashMap recordsMap = (HashMap)runResult.get("records");
|
|
if(recordsMap != null) {
|
|
%>
|
|
<tr>
|
|
<td valign="top"><%
|
|
for(int sel=0; sel <= 10;sel++) {
|
|
ArrayList resultList = (ArrayList)recordsMap.get("SEL"+sel);
|
|
// System.out.println("SEL"+sel+" = "+resultList);
|
|
if(resultList == null) {
|
|
break;
|
|
}
|
|
%><form name="excel_form" id = "excel_form" action="excelDown.jsp" method="post" target="_blank">
|
|
<input type="hidden" name="resultData" id="resultData">
|
|
<input type="hidden" name="displayType" id="displayType" value="<%=displayType%>">
|
|
<table width="100%" id="resultTable">
|
|
<%
|
|
int[] colSize = (int[])resultList.get(0);
|
|
int cellWidth = 40;
|
|
for(int index = 1; index<resultList.size(); index++ ) {
|
|
String[] rows = (String[])resultList.get(index);
|
|
%>
|
|
<tr>
|
|
<%
|
|
for(int p = 0; p<rows.length; p++ ) {
|
|
if(index==1) {
|
|
cellWidth = rows[p].length();
|
|
if(colSize[p] > cellWidth) cellWidth = colSize[p];
|
|
cellWidth = cellWidth * 10;
|
|
out.println(String.format("<th style=\"height : 20px; width:%dpx\">%s</th>", cellWidth, rows[p] ));
|
|
}
|
|
else {
|
|
out.println(String.format("<td style=\"height : 20px\">%s</td>", rows[p]));
|
|
}
|
|
|
|
}
|
|
%>
|
|
</tr>
|
|
<%
|
|
}
|
|
%>
|
|
</table>
|
|
</form>
|
|
<% }
|
|
%>
|
|
</td>
|
|
</tr>
|
|
<%
|
|
}
|
|
}
|
|
%>
|
|
</table>
|
|
</div><!-- end content_middle -->
|
|
</div><!-- end right_box -->
|
|
<!-- button -->
|
|
|
|
</body>
|
|
</html>
|
|
|
|
|