JSP 만으로 오라클 접속

Dev/Web

JSP 만으로 오라클 접속

newtype 2007. 1. 9. 09:04

서블릿을 사용하지 않고 JSP만 이용
commit, rollback 지원 하지 않고 단순 조회용

<%@ page language="java" contentType="text/html;charset=euc-kr" import="java.sql.*" %>
<html>
<head>
<title>SELECT Query Viewer</title>
<script Language="Javascript">

</script>
</head>
<body onload="init();">
<table width=100% border=0>
<tr height=50 valign=middle><th align=center>SELECT Query Viewer</th></tr>
<tr> <td align=right>by newtype</td></tr>
<tr height=30><th></th></tr>
</table>

<form name=sqvForm action=result.jsp method=post>

   <hr>
   <table width=100% border=0>
   <tr height=50>
   <th colspan=2>Server Info</th>
   <tr>
   <tr>
   <td>Server IP</td>
   <td><input type=input name=ORA_IP></td>
   <tr>
   <td>Server SID</td>
   <td><input type=input name=ORA_SID></td>
   </tr>
   <tr>
   <td>Server User</td>
   <td><input type=input name=ORA_USER></td>
   </tr>
   <tr>
   <td>Server Password</td>
   <td><input type=input name=ORA_PWD></td>
   </tr>
   </table>
   <hr>
   <table width=100% border=0>
   <tr height=50>
   <th>Query</th>
   <tr>
   <tr>
   <td align=center><textarea rows=20 cols=100 name=sqlQuery></textarea></td>
   <tr>
   <tr height=50>
   <td align=center><input type=submit> <input type=reset></td>
   <tr>
</form>

</body>
</html>

<%@ page language="java" contentType="text/html;charset=euc-kr" import="java.sql.*" %>
<%
   request.setCharacterEncoding("euc-kr");

   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   ResultSetMetaData rsmd = null;

   String sql = "select * from tabs";
   int i;

   String strHeader = "";
   String strData ="";
   int nCol=0;
   int nRow=0;

   // 연결 정보
   String ORA_IP = "";
   String ORA_SID= "";
   String ORA_USER= "";
   String ORA_PWD= "";

   if ( request.getParameter("ORA_IP") != null )
   ORA_IP = request.getParameter("ORA_IP");

   if ( request.getParameter("ORA_SID") != null )
   ORA_SID = request.getParameter("ORA_SID");

   if ( request.getParameter("ORA_USER") != null )
   ORA_USER = request.getParameter("ORA_USER");

   if ( request.getParameter("ORA_PWD") != null )
   ORA_PWD = request.getParameter("ORA_PWD");

   if ( request.getParameter("sqlQuery") != null )
   sql = request.getParameter("sqlQuery");


   try {

   //1.드라이버선택
   Class.forName("oracle.jdbc.driver.OracleDriver");

   //2.db연결
   conn = DriverManager.getConnection("jdbc:oracle:thin:@"+ORA_IP+":1521:"+ORA_SID, ORA_USER, ORA_PWD);

   //3.Statement객체및 쿼리생성
   stmt = conn.createStatement();

   //4.실행
   rs = stmt.executeQuery(sql);


   //5.처리
   // 헤더
   rsmd = rs.getMetaData();
   nCol = rsmd.getColumnCount();
   strHeader += "<tr>";
   for(i=1; i<=nCol; i++)
   {
   strHeader += "<th>" + rsmd.getColumnName(i) + "</th>";
   }
   strHeader += "</tr>";

   // 데이타
   while(rs.next())
   {
   strData += "<tr>";

   for(i=1; i<=nCol; i++)
   {
   strData += "<td>" + rs.getString(i) + "</td>";
   }

   strData += "</tr>\r\n";
   nRow++;

   //rs.next();
   }

   rs.close();

   //out.println("연결성공<br>");

   //6.연결해제
   stmt.close();
   conn.close();

   } catch (Exception e) {

   if(rs!=null){ try{rs.close();}catch(SQLException se){} }
   if(stmt!=null){ try{stmt.close();}catch(SQLException se){} }
   if(conn != null){ try{conn.close();} catch(SQLException se){}}
   out.println(e);

   }
%>
<html>
<head>
<title>SELECT Query Viewer</title>
</head>
<body>
<table width="100%" border="1">
<% out.println(strHeader);%>
<% out.println(strData);%>
</table>
</body>
</html>