-- 회원정보 테이블
CREATE TABLE EX07_MEMBER (
USER_ID VARCHAR2(20) PRIMARY KEY,
USER_PW VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(40) NOT NULL,
USER_EMAIL VARCHAR2(80),
REG_DATE DATE DEFAULT SYSDATE
);
--조회 테스트를 위해 몇개의 회원정보를 저장
INSERT INTO EX07_MEMBER VALUES('hong','1111','홍길동','hong@gmial.com',SYSDATE);
INSERT INTO EX07_MEMBER VALUES('lee','2222','이순신','lee@gmial.com',SYSDATE);
INSERT INTO EX07_MEMBER VALUES('son','3333','손흥민','son@gmial.com',SYSDATE);
INSERT INTO EX07_MEMBER VALUES('kim','4444','김민재','kim@gmial.com',SYSDATE);
SELECT * FROM EX07_MEMBER;
COMMIT;ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원정보 검색</title>
</head>
<body>
<h1>회원 정보 검색</h1>
<fieldset>
<form action="member.jsp" method="get" enctype="application/x-www-form-urlencoded">
이름 : <input type="text" placeholder="홍길동" name="name">
<input type="submit" value="검색하기">
</form>
</fieldset>
</body>
</html>ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
<%@page import="model.memberVO"%>
<%@page import="java.util.List"%>
<%@page import="model.memberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 정보 출력창</title>
</head>
<body>
<!-- 사용자 요청처리/ 응답화면구현 모두 이 JSP에서 구현 [Controller/View 역할] -->
<!-- 1. 사용자 요청 처리 -->
<%
String name=request.getParameter("name");
%>
<!-- 2. 비즈니스 로직[DB작업] -->
<%
//별도의 비즈니스 로직을 처리하는 클래스를 활용 [DAO ,VO]
memberDAO memberDao= new memberDAO();
List<memberVO> memberList= memberDao.getMemberList(name);
System.out.println(memberList.size());
%>
<!-- 3. 응답화면 구현 -->
<h1>회원정보 출력</h1>
<table border="1" width="800" style="text-align: center;">
<tr style="background-color: #FFFF66">
<th>아이디</th>
<th>비밀번호</th>
<th>이름</th>
<th>이메일</th>
<th>가입일자</th>
</tr>
<!-- 검색된 회원정보 리스트를 반복문으로 처리 ..[스크립트릿]-->
<%
for(memberVO member : memberList){
%>
<tr>
<td><%= member.getUserId() %></td>
<td><%= member.getUserPw() %></td>
<td><%= member.getUserName() %></td>
<td><%= member.getUserEmail() %></td>
<td><%= member.getRegDate() %></td>
</tr>
<%
}
%>
</table>
</body>
</html>ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
package model;
import java.sql.Date;
public class memberVO {
private String userId;
private String userPw;
private String userName;
private String userEmail;
private Date regDate;
//getter& setter
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserPw() {
return userPw;
}
public void setUserPw(String userPw) {
this.userPw = userPw;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserEmail() {
return userEmail;
}
public void setUserEmail(String userEmail) {
this.userEmail = userEmail;
}
public Date getRegDate() {
return regDate;
}
public void setRegDate(Date regDate) {
this.regDate = regDate;
}
}ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
package model;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class memberDAO {
// Connection Pool 사용
DataSource dataSource; //커넥션풀에서 Connection을 꺼내오는 객체의 참조변수
//constructor
public memberDAO() {
// TODO Auto-generated constructor stub
//커넥션 풀 작업 3단계
try {
Context context= new InitialContext();
Context envContext= (Context)context.lookup("java:comp/env");
dataSource= (DataSource)envContext.lookup("jdbc/oracle");
System.out.println("커넥션풀의 준비가 완료되었습니다.");
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}////.............................................
//회원정보 조회 기능 메소드..
public List<memberVO> getMemberList(String name) {
List<memberVO> memberList= new ArrayList<>();
try {
Connection conn=dataSource.getConnection();
PreparedStatement pstmt=null;
String sql= "SELECT * FROM EX07_MEMBER";
if(name !=null && name.length()!=0) {
sql += " WHERE USER_NAME=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, name);
}else {
pstmt=conn.prepareStatement(sql);
}
//쿼리문 실행
ResultSet rs=pstmt.executeQuery();
while(rs.next()) {
String userId= rs.getString("USER_ID");
String userPw= rs.getString("USER_PW");
String userName= rs.getString("USER_NAME");
String userEmail= rs.getString("USER_EMAIL");
Date regDate= rs.getDate("REG_DATE");
memberVO member= new memberVO();
member.setUserId(userId);
member.setUserPw(userPw);
member.setUserName(userName);
member.setUserEmail(userEmail);
member.setRegDate(regDate);
memberList.add(member);
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("dd");
}
return memberList;
}
}ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ