JSP ORACLE Connect

Study

JSP ORACLE Connect

Mr.Martin 2011. 5. 31. 20:49

index.jsp

<%@ 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 = "127.0.0.1";
    String ORA_SID= "xe";
    String ORA_USER= "scott";
    String ORA_PWD= "rudnf";

    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>

====================================================================================
result.jsp

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

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");