DBCP를 이용한 JSP 페이지와 이용하지 않은 JSP의 차이
DBCP 이용 X
viewMemberList.jsp
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<title>회원 목록</title>
</head>
<body>
MEMBER 테이블의 내용<br>
<table width="100%;" border="1">
<tr>
<th>이름</th><th>아이디</th><th>이메일</th>
</tr>
<%
//1. JDBC 드라이버 로딩
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
//2. 데이터베이스 커넥션(연결객체) 생성
//1) jdbcDriver? 2) 계정아이디? 3) 비밀번호?
String jdbcDriver = "jdbc:oracle:thin:@localhost:1521:xe";
String dbUser = "jspexam";
String dbPass = "java";
String query = "SELECT MEMBERID, PASSWORD,NAME,EMAIL FROM MEMBER";
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
//3. Statement 생성
stmt = conn.createStatement();
//4. 쿼리 실행
rs = stmt.executeQuery(query);
//5. 쿼리 실행 결과 화면 출력
while(rs.next()){
out.print("<tr>");
out.print("<td><a href='viewMember.jsp?memberId=" + rs.getString("MEMBERID")+"'>" + rs.getString("NAME") + "</td>");
out.print("<td>" + rs.getString("MEMBERID") + "</td>");
out.print("<td>" + rs.getString("EMAIL") + "</td>");
out.print("</tr>");
}
}catch(SQLException ex){
out.print(ex.getMessage());
ex.printStackTrace();
}finally{
//6. 사용한 Statement 객체 종료
if(rs!=null)try{rs.close();}catch(SQLException ex){}
if(stmt!=null)try{stmt.close();}catch(SQLException ex){}
//7. 커넥션 객체 종료
if(conn!=null)try{conn.close();}catch(SQLException ex){}
}
%>
</table>
</body>
</html>결과화면
DBCP 이용 O
viewMemberUsingPool.jsp
<%@page import="jdbc.ConnectionProvider"%>
<%@page import="jdbc.JdbcUtil"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<title>회원 목록</title>
</head>
<body>
MEMBER 테이블의 내용<br>
<table width="100%;" border="1">
<tr>
<th>이름</th><th>아이디</th><th>이메일</th>
</tr>
<%
//1. JDBC 드라이버 로딩
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
//2. 데이터베이스 커넥션(연결객체) 생성
//1) jdbcDriver? 2) 계정아이디? 3) 비밀번호?
String query = "SELECT MEMBERID, PASSWORD,NAME,EMAIL FROM MEMBER";
conn = ConnectionProvider.getConnection();
//3. Statement 생성
stmt = conn.createStatement();
//4. 쿼리 실행
rs = stmt.executeQuery(query);
//5. 쿼리 실행 결과 화면 출력
while(rs.next()){
out.print("<tr>");
out.print("<td><a href='viewMember.jsp?memberId=" + rs.getString("MEMBERID")+"'>" + rs.getString("NAME") + "</td>");
out.print("<td>" + rs.getString("MEMBERID") + "</td>");
out.print("<td>" + rs.getString("EMAIL") + "</td>");
out.print("</tr>");
}
}catch(SQLException ex){
out.print(ex.getMessage());
ex.printStackTrace();
}finally{
//6. 사용한 Statement 객체 종료
JdbcUtil.close(rs);
JdbcUtil.close(stmt);
//7. 커넥션 객체 종료 -> dbcp이니까 커넥션 객체를 커넥션 풀로 반환
JdbcUtil.close(conn);
}
%>
</table>
</body>
</html>결과화면
=> 둘의 결과는 같다.
여기서 이름을 클릭했을 때,
해당하는 사람의 상세 정보를 출력한다.
viewMember.jsp
<%@page import="jdbc.ConnectionProvider"%>
<%@page import="jdbc.JdbcUtil"%>
<%@page import="dto.Mem"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String memberId = request.getParameter("memberId");
%>
<!DOCTYPE html>
<html>
<head>
<title>회원 정보</title>
</head>
<body>
<%
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Mem memVO = null;
try{
String jdbcDriver = "jdbc:apache:commons:dbcp:ddit";
String sql = "SELECT MEMBERID, PASSWORD, NAME, EMAIL FROM MEMBER "+
" WHERE MEMBERID='" + memberId +"'";
conn = ConnectionProvider.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
memVO = new Mem();
memVO.setMemberid(rs.getString("MEMBERID"));
memVO.setName(rs.getString("NAME"));
memVO.setPassword(rs.getString("PASSWORD"));
memVO.setEmail(rs.getString("EMAIL"));
}
out.print("memVO : " + memVO);
}catch(SQLException ex){
out.print(ex.getMessage());
}finally{
//6. 사용한 Statement 객체 종료
JdbcUtil.close(rs);
JdbcUtil.close(stmt);
//7. 커넥션 객체 종료 -> dbcp이니까 커넥션 객체를 커넥션 풀로 반환
JdbcUtil.close(conn);
}
%>
<c:set var="memVO" value="<%=memVO%>" scope="page"/>
<table border="1">
<tr>
<th>아이디</th><td>${memVO.memberid}</td>
</tr>
<tr>
<th>비밀번호</th><td>${memVO.password}</td>
</tr>
<tr>
<th>이름</th><td>${memVO.name}</td>
</tr>
<tr>
<th>이메일</th><td>${memVO.email}</td>
</tr>
</table>
<%
%>
</body>
</html>미역은대를 클릭했을 때 - 결과화면
