반응형
JSP에서 DB 쿼리 실행기 작성 초간단 샘플
프로젝트 수행시 급하게 쿼리를 수행할 일이 생겨서 정리한다.
운영서버의 DB 경우 현업담당자는 권한이없고, DB 관리자 또한 어플리케이션 코드를 통해 처리하라고 가이드했다.
현업과 DB 관리자에게 의견을 구하고 아래와 같이 쿼리 작성 페이지를 만들어서 쿼리를 수행하기로했다.
1. query.JSP 파일 작성
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.sql.CallableStatement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.ResultSetMetaData" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.util.Properties" %>
<%
request.setCharacterEncoding("utf-8");
String query= request.getParameter("query")==null?"":request.getParameter("query");
Properties properties = new Properties();
properties.load(new FileInputStream("/application/conf/db.properties"));
String driver = properties.getProperty("driverClassName");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
%>
<html>
<head>
<title>DB excute</title>
<style type="text/css">
table { border-top: 1px solid black; border-left: 1px solid black; }
table td { border-bottom: 1px solid black; border-right: 1px solid black; }
</style>
</head>
<body>
<h1 style="width:100%; text-align:center;">DB excute</h1>
<form name="ok" action="" method="post">
<textarea name="query" rows="10" style="width:100%"><%=query %></textarea>
<input name="go" type="button" value="처리" onclick="javascript:document.ok.submit();" style="width: 100%; height: 50px; margin-top: 10px;" />
<br>
<%
if(query != null && !"".equals(query))
{
out.println(query + "</br></br>");
try
{
Class.forName(driver);
}
catch (ClassNotFoundException e)
{
out.println(e);
}
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
con = DriverManager.getConnection(url,username,password);
stmt = con.createStatement();
int iStart = 0;
for(int i = 0 ; i < query.length() ; i++)
{
char temp = query.charAt(i);
if(temp != ' ')
{
iStart = i;
break;
}
}
String tempSQL = query.substring(iStart);
tempSQL = tempSQL.substring(0, tempSQL.indexOf(" "));
if(tempSQL.toUpperCase().indexOf("EXEC") > -1)
{
query = query.replaceAll("EXEC", "call");
query = query.replaceAll("exec", "call");
query = "{? = " + query + "}";
out.println(query);
CallableStatement cs = con.prepareCall(query);
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet) cs.getObject(1);
ResultSetMetaData rsmd = rs.getMetaData();
int colCnt = rsmd.getColumnCount();
out.println("<table border='0' cellspacing='0' cellpadding='0'>");
out.println("<tr>");
out.println("<td>row</td>");
for(int i = 1 ; i <= colCnt ; i++)
{
out.println("<td>");
out.println(rsmd.getColumnLabel(i));
out.println("</td>");
}
out.println("</tr>");
while (rs.next())
{
out.println("<tr>");
out.println("<td>");
out.println(rs.getRow());
out.println("</td>");
for(int i = 1 ; i <= colCnt ; i++)
{
out.println("<td>");
System.out.println(rsmd.getColumnTypeName(i));
out.println(rs.getString(i));
out.println("</td>");
}
out.println("</tr>");
}
out.println("</table>");
}
else if(tempSQL.toUpperCase().indexOf("SELECT") > -1 || tempSQL.toUpperCase().indexOf("WITH") > -1)
{
rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int colCnt = rsmd.getColumnCount();
out.println("<table border='0' cellspacing='0' cellpadding='0'>");
out.println("<tr>");
out.println("<td>row</td>");
for(int i = 1 ; i <= colCnt ; i++)
{
out.println("<td>");
out.println(rsmd.getColumnLabel(i));
out.println("</td>");
}
out.println("</tr>");
while (rs.next())
{
out.println("<tr>");
out.println("<td>");
out.println(rs.getRow());
out.println("</td>");
for(int i = 1 ; i <= colCnt ; i++)
{
out.println("<td>");
if(!"BLOB".equals(rsmd.getColumnTypeName(i)) ){
out.println(rs.getString(i));
}
out.println("</td>");
}
out.println("</tr>");
}
out.println("</table>");
}
else
{
int iRows = stmt.executeUpdate(query);
out.println(iRows + " ROWS EXCUTED!");
}
}
catch (SQLException e)
{
out.println(e);
}
finally
{
try
{
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
}
}
}
%>
</form>
</body>
</html>
2. db.properties 작성
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@xxx.xx.xxx.xx:1521:orcl
username=XXXXXXX
password=XXXXXXX
3. 추가 필요 파일 - ojdbc.jar
{{application}}/WEB-INF/lib/ojdbc{version}.jar
4. query.jsp 호출 후 쿼리 수행
'프로그래밍 > JAVA' 카테고리의 다른 글
| JAVA 기본 String을 Map으로 변환하기 ( 예제 ) (0) | 2023.04.19 |
|---|---|
| java8 stream List를 Map으로 변환 (0) | 2023.02.14 |
| sonarQube - Public constants and fields initialized at declaration should be "static final" rather than merely "final" (0) | 2022.07.22 |
| sonarQube - Field names should comply with a naming convention. (0) | 2022.07.22 |
| java8 stream의 쉬운 사용방법 (map, filter, collect) (0) | 2022.06.10 |
댓글