JSP

기본JDBC 만들기

사라링 2012. 5. 8. 18:18

ojdbc.jpg  WEB-INF/lib 폴더 안에 ojdbc14.jar 파일을 넣는다. libraries/Web App Libraries 폴더 안에서 확인 가능

 

memberlist.jsp

 

<?xml version="1.0" encoding="UTF-8" ?>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>10/memberList.jsp</title>
</head>
<body>

<%
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    // 실행중에 해당 클래스를 로딩(문자열로) 하겠다.
    Class.forName("oracle.jdbc.driver.OracleDriver");
    conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","hun","java");

// member 라는 테이블이 생성 되어 있다는 전제 조건 하에 !
    psmt=conn.prepareStatement("select * from member");
    rs=psmt.executeQuery();
%>
<h4 align="center">회원 목록</h4>
<table align="center" border="1">
    <thead>
    <tr>
        <th bgcolor="pink">ID</th>
        <th bgcolor="pink">성명</th>
        <th bgcolor="pink">직업</th>
        <th bgcolor="pink">e-mail</th>
        <th bgcolor="pink">hp</th>
    </tr>
    </thead>
    <tbody>
    <%
    while(rs.next()){
    %>
        <tr>
            <td><%=rs.getString("mem_id") %></td>
            <td><%=rs.getString("mem_name") %></td>
            <td><%=rs.getString("mem_job") %></td>
            <td><%=rs.getString("mem_mail") %></td>
            <td><%=rs.getString("mem_hp") %></td>
        </tr>
    <%} %>
    </tbody>

</table>
</body>
</html>
<%
if(rs!=null)try{rs.close();}catch(SQLException e){}
if(psmt!=null)try{psmt.close();}catch(SQLException e){}
if(conn!=null)try{conn.close();}catch(SQLException e){}
%>

 

memberview.jsp

<?xml version="1.0" encoding="UTF-8" ?>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="<%=request.getContextPath() %>/css/main.css" />
<title>11/memberview.jsp</title>
</head>
<body>
<div id="form">
<%
    // 실제 회원아이디는 센션에서 꺼내야 합니다.
   
    String id=request.getParameter("mem_id");
    if(id==null || id.equals("")){
        response.sendRedirect(request.getContextPath()+"/index.jsp");
        return;
    }

    // 만약에 id 가 null 이라면
    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;
    // 실행중에 해당 클래스를 로딩(문자열로) 하겠다.
    Class.forName("oracle.jdbc.driver.OracleDriver");
    conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","hun","java");
    psmt=conn.prepareStatement("select * from member where mem_id = ?");
    psmt.setString(1, id);
   
    rs=psmt.executeQuery();
%>
<h4 align="center">회원 상세 보기</h4>
<table align="center" border="1">
    <tbody>
    <%
    if(rs.next()){
    %>
    <tr>
        <th>ID</th>
        <td><%=rs.getString("mem_id") %></td>
    </tr>
    <tr>
        <th>성명</th>
        <td><%=rs.getString("mem_name") %></td>
    </tr>
    <tr>
        <th>주민번호</th>
        <td><%=rs.getString("mem_regno1") %>-<%=rs.getString("mem_regno2") %></td>
    </tr>
    <tr>
        <th>생일</th>
        <td><%=rs.getString("mem_bir") %></td>
    </tr>
    <tr>
        <th>주소</th>
        <td><%=rs.getString("mem_zip") %><br />
            <%=rs.getString("mem_add1") %>
            <%=rs.getString("mem_add2") %>
        </td>
    </tr>
    <tr>
        <th>집전화</th>
        <td><%=rs.getString("mem_hometel") %></td>
    </tr>
    <tr>
        <th>직장전화</th>
        <td><%=rs.getString("mem_comtel") %></td>
    </tr>
    <tr>
        <th>HP</th>
        <td><%=rs.getString("mem_hp") %></td>
    </tr>
    <tr>
        <th>e-mail</th>
        <td><%=rs.getString("mem_mail") %></td>
    </tr>
    <tr>
        <th>직업</th>
        <td><%=rs.getString("mem_job") %></td>
    </tr>
    <tr>
        <th>취미</th>
        <td><%=rs.getString("mem_like") %></td>
    </tr>
    <tr>
        <th>기념일</th>
        <td><%=rs.getString("mem_memorial") %></td>
    </tr>
    <tr>
        <th>마일리지</th>
        <td><%=rs.getString("mem_mileage") %></td>
    </tr>
   
    <tr>
        <th>탈퇴여부</th>
        <td><%=rs.getString("mem_delete") %></td>
    </tr>
   
    <%}
    else {
    %>
    <tr>
        <td>
            <span class="warning">
            해당 회원이 존재 하지 않습니다.
            </span>
        </td>
    </tr>
   
    <%
    }%>
    </tbody>
</table>
</div>
</body>
</html>
<%
if(rs!=null)try{rs.close();}catch(SQLException e){}
if(psmt!=null)try{psmt.close();}catch(SQLException e){}
if(conn!=null)try{conn.close();}catch(SQLException e){}
%>

이 글은 스프링노트에서 작성되었습니다.