jsp - Bean, Dao 를 사용한 회원관리

*MemberBean.java

package com.bean;

import java.sql.Timestamp;

public class MemberBean {

private String id;

private String passwd;

private String name;

private int age;

private String gender;

private String email;

private Timestamp reg_date;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public String getGender() {

return gender;

}

public void setGender(String gender) {

this.gender = gender;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Timestamp getReg_date() {

return reg_date;

}

public void setReg_date(Timestamp reg_date) {

this.reg_date = reg_date;

}

}

*MemberDao.java

package com.dao;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

import com.bean.MemberBean;

import com.db.MyOracle;

//DB의 member 테이블을 조작해주는 클래스

public class MemberDao {

// private Connection getConnection() throws Exception {

// Connection con = null;

// // DB 연결정보

// final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

// final String USER = "jspid";

// final String PWD = "jsppass";

//

// // 1 드라이버 로딩

// Class.forName("oracle.jdbc.driver.OracleDriver");

// // 2 DB연결

// con = DriverManager.getConnection(URL, USER, PWD);

// return con;

// } // getConnection()의 끝

//

// private void closeJDBC(Connection con, 

// PreparedStatement pstmt, ResultSet rs) {

// if (rs != null) {

// try {

// rs.close();

// } catch (SQLException e) {

// e.printStackTrace();

// }

// }

// if (pstmt != null) {

// try {

// pstmt.close();

// } catch (SQLException e) {

// e.printStackTrace();

// }

// }

// if (con != null) {

// try {

// con.close();

// } catch (SQLException e) {

// e.printStackTrace();

// }

// }

// } // closeJDBC()의 끝

// insert 메소드

public void insertMember(MemberBean memberBean) throws Exception {

// JDBC 참조변수

Connection con = null;

PreparedStatement pstmt = null;

try {

// 1. 드라이버 로딩  2. DB연결

con = MyOracle.getConnection();

// 3. insert

String sql = "insert into member (id, passwd, name, reg_date, age, gender, email) values (?,?,?,?,?,?,?)";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getId());

pstmt.setString(2, memberBean.getPasswd());

pstmt.setString(3, memberBean.getName());

pstmt.setTimestamp(4, memberBean.getReg_date());

pstmt.setInt(5, memberBean.getAge());

pstmt.setString(6, memberBean.getGender());

pstmt.setString(7, memberBean.getEmail());

// 실행

pstmt.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

} finally {

// JDBC 자원닫기

MyOracle.closeJDBC(con, pstmt, null);

} // finally

} // insertMember()의 끝

// 사용자 로그인 체크하는 메소드. id, passwd값 가짐

public int userCheck (MemberBean memberBean) throws Exception {

// JDBC 참조변수

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

int check = -1;  // 부정적인 값을 기본값으로 둠.

try {

con = MyOracle.getConnection();

// 3. id에 해당하는 passwd 가져오기

String sql = "SELECT passwd FROM member WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getId());

// 4. 실행  rs에 저장

rs = pstmt.executeQuery();

// 5.

//   rs에 데이터(행)가 있으면 아이디있음

//       패스워드비교 맞으면 로그인인증(세션값생성 "id")

//       패스워드비교 틀리면 "패스워드틀림" 로그인페이지로 이동

//   rs에 데이터(행)가 없으면 "아이디없음" 로그인페이지로 이동

if (rs.next()) {

// 아이디있음

if (memberBean.getPasswd().equals(rs.getString("passwd"))) {

check = 1; // 아이디, 패스워드 일치

} else {

check = 0;  // 패스워드가 불일치.

}

} else {

check = -1;  // 아이디 불일치.

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return check;

} // userCheck()

// 전체회원목록 가져오기

public List<MemberBean> getMembers() {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

List<MemberBean> list = new ArrayList<>();

try {

con = MyOracle.getConnection();

// 3. sql 전체회원 가져오기

String sql = "select * from member";

pstmt = con.prepareStatement(sql);

// 4. rs 저장

rs = pstmt.executeQuery();

// rs 데이터 있으면 자바빈 객체생성

// rs => 자바빈 멤버변수 저장

// 자바빈 => 리스트 한칸 추가

while (rs.next()) {

MemberBean memberBean = new MemberBean();

memberBean.setId(rs.getString("id"));

memberBean.setPasswd(rs.getString("passwd"));

memberBean.setName(rs.getNString("name"));

memberBean.setReg_date(rs.getTimestamp("reg_date"));

memberBean.setAge(rs.getInt("age"));

memberBean.setGender(rs.getString("gender"));

memberBean.setEmail(rs.getString("email"));

list.add(memberBean);  // 배열리스트에 추가

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return list;

} // getMembers()

// 회원 한사람의 정보 가져오기

public MemberBean getMember(String id) {

// JDBC 변수

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

MemberBean memberBean = null;

try {

con = MyOracle.getConnection();

// 3. sql  id에 해당하는 모든정보 가져오기

String sql = "SELECT * FROM member WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, id);

// 4. rs 실행 저장

rs = pstmt.executeQuery();

// 5. rs => 자바빈에 저장

if (rs.next()) {

memberBean = new MemberBean();

memberBean.setId(rs.getString("id"));

memberBean.setPasswd(rs.getString("passwd"));

memberBean.setName(rs.getString("name"));

memberBean.setReg_date(rs.getTimestamp("reg_date"));

memberBean.setAge(rs.getInt("age"));

memberBean.setGender(rs.getString("gender"));

memberBean.setEmail(rs.getString("email"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return memberBean;

} // getMember() 의 끝

public int updateMember(MemberBean memberBean) {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

int check = 0;  // 실패값으로 초기화

try {

con = MyOracle.getConnection();

// 3. id에 해당하는 passwd 가져오기

String sql = "SELECT passwd FROM member WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getId());

// 4. 실행 rs 저장

rs = pstmt.executeQuery();

// 5. rs 데이터 있으면 아이디 있음

//        패스워드 비교 맞으면  update  check = 1

//                  틀리면                 check = 0

if (rs.next()) {

if (memberBean.getPasswd().equals(rs.getString("passwd"))) {

pstmt.close(); // select용 문장객체 닫기

pstmt = null;

sql = "UPDATE member SET name=?,age=?,gender=?,email=? WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getName());

pstmt.setInt(2, memberBean.getAge());

pstmt.setString(3, memberBean.getGender());

pstmt.setString(4, memberBean.getEmail());

pstmt.setString(5, memberBean.getId());

// 실행

pstmt.executeUpdate();

check = 1;

} else {

check = 0;

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return check;

} // updateMember()

// 회원삭제. id,passwd 자바빈

public int deleteMember(MemberBean memberBean) {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

int check = 0;

try {

con = MyOracle.getConnection();

// 3. id에 해당하는 passwd 가져오기

String sql = "SELECT passwd FROM member WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getId());

// 4. 실행   rs 저장

rs = pstmt.executeQuery();

// 5. 데이터가 있으면 패스워드비교 맞으면   delete   check = 1

//                         틀리면                    check = 0

if (rs.next()) {

if (memberBean.getPasswd().equals(rs.getString("passwd"))) {

pstmt.close();

pstmt = null;

sql = "DELETE FROM member WHERE id=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, memberBean.getId());

// 실행

pstmt.executeUpdate();

check = 1;

} else {

check = 0;

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return check;

} // deleteMember()

} // class MemberDao

*MyOracle.java

package com.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class MyOracle {

// 드라이버 클래스 이름

public static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";

// DB 연결정보

public static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

public static final String USER = "jspid";

public static final String PWD = "jsppass";

// DB연결 메소드

public static Connection getConnection() throws Exception {

Connection con = null;

// DB 연결정보

final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

final String USER = "jspid";

final String PWD = "jsppass";

// 1 드라이버 로딩

Class.forName(DRIVER_NAME);

// 2 DB연결

con = DriverManager.getConnection(URL, USER, PWD);

return con;

} // getConnection()의 끝

public static void closeJDBC(Connection con, 

PreparedStatement pstmt, ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (con != null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

} // closeJDBC()의 끝

}

*joinForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<h1>회원가입</h1>

<hr>

<form action="joinPro.jsp" method="post">

아이디 : <input type="text" name="id"><br> 

패스워드 : <input type="password" name="passwd"><br>

이름 : <input type="text" name="name"><br>

나이 : <input type="number" name="age" min="0" max="200"><br>

성별 : <input type="radio" name="gender" value="남">남성 

<input type="radio" name="gender" value="여">여성<br>

이메일 : <input type="email" name="email"><br>

<input type="submit" value="회원가입">

</form>

</body>

</html>

*joinPro.jsp

<%@page import="com.dao.MemberDao"%>

<%@page import="com.db.MyOracle"%>

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%-- 한글처리 --%>

<% request.setCharacterEncoding("utf-8"); %>

<jsp:useBean id="memberBean" class="com.bean.MemberBean" />

<jsp:setProperty property="*" name="memberBean" />

<%

//날짜생성

Timestamp reg_date = new Timestamp(System.currentTimeMillis());

// DB작업 객체생성 MemberDao

MemberDao memberDao = new MemberDao();

// 메소드 호출

memberDao.insertMember(memberBean);

%>

<script>

alert('회원가입 성공');

location.href = 'loginForm.jsp';

</script>

*loginForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<h1>로그인</h1>

<hr>

<form action="loginPro.jsp" method="post">

아이디: <input type="text" name="id"><br>

패스워드: <input type="password" name="passwd"><br>

<input type="submit" value="로그인">

<input type="button" value="회원가입" onclick="location.href='joinForm.jsp'">

</form>

</body>

</html>

*loginPro.jsp

<%@page import="com.dao.MemberDao"%>

<%@page import="java.sql.DriverManager"%>

<%@page import="com.db.MyOracle"%>

<%@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"%>

<jsp:useBean id="memberBean" class="com.bean.MemberBean" />

<jsp:setProperty property="*" name="memberBean" />

<%

// DB 객체 생성

MemberDao memberDao = new MemberDao();

int check = memberDao.userCheck(memberBean);

// check == 1 로그인인증   main.jsp로 이동 

// check == 0 "패스워드 틀림"  뒤로 이동

// check == -1 "아이디 없음"  뒤로 이동

 

if (check == 1) {

session.setAttribute("id", memberBean.getId());

response.sendRedirect("main.jsp");

} else if (check == 0) {

%>

<script>

alert('패스워드 틀림');

history.back();

</script>

<%

} else { // 아이디없음  뒤로이동

%>

<script>

alert('아이디 없음');

history.back();

</script>

<%

}

%>

*main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<%@include file="loginCheck.jspf" %>

<h1>메인페이지</h1>

<hr>

<%=id %>님이 로그인 하셨습니다.<br>

<input type="button" value="로그아웃" onclick="location.href='logout.jsp'"><br>

<a href="info.jsp">회원정보조회</a><br>

<a href="update.jsp">회원정보수정</a><br>

<a href="delete.jsp">회원정보삭제</a><br>

<%

if (id.equals("admin")) {

%>

<a href="list.jsp">전체회원목록</a><br>

<%

}

%>

</body>

</html>

*loginCheck.jspf

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%

// 세션값 가져오기

String id = (String) session.getAttribute("id");

// 세션값 없으면(null이면) loginForm.jsp로 이동

if (id == null) {

response.sendRedirect("loginForm.jsp");

return;

}

%>

*logout.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%

// 세션초기화

session.invalidate();

// "로그아웃됨"  loginForm.jsp로 이동

%>

<script>

alert('로그아웃');

location.href = 'loginForm.jsp';

</script>

*info.jsp

<%@page import="com.bean.MemberBean"%>

<%@page import="com.dao.MemberDao"%>

<%@page import="java.sql.*"%>

<%@page import="com.db.MyOracle"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<%@include file="loginCheck.jspf" %>

<%

//DB객체생성

MemberDao memberDao = new MemberDao();

// getMember(id) 메소드호출

MemberBean memberBean = memberDao.getMember(id);

%>

<h1>회원정보 조회</h1>

<hr>

아이디: <%=memberBean.getId() %><br>

패스워드: <%=memberBean.getPasswd() %><br>

이름: <%=memberBean.getName() %><br>

가입날짜: <%=memberBean.getReg_date() %><br>

나이: <%=memberBean.getAge() %><br>

성별: <%=memberBean.getGender() %><br>

이메일: <%=memberBean.getEmail() %><br>

<br>

<a href="main.jsp">메인화면</a>

</body>

</html>

*update.jsp

<%@page import="com.bean.MemberBean"%>

<%@page import="com.dao.MemberDao"%>

<%@page import="com.db.MyOracle"%>

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<%@include file="loginCheck.jspf" %>

<%

//DB객체생성

MemberDao memberDao = new MemberDao();

//getMember(id) 메소드호출

MemberBean memberBean = memberDao.getMember(id);

%>

<h1>회원수정</h1>

<hr>

<form action="updatePro.jsp" method="post">

아이디: <input type="text" name="id" value="<%=memberBean.getId() %>" readonly><br>

패스워드: <input type="password" name="passwd"><br>

이름: <input type="text" name="name" value="<%=memberBean.getName() %>"><br>

나이: <input type="number" name="age" min="0" max="200" value="<%=memberBean.getAge() %>"><br>

성별: <input type="radio" name="gender" value="남" 

     <% if(memberBean.getGender() != null && memberBean.getGender().equals("남")) { %> checked <% } %>>남성

     <input type="radio" name="gender" value="여" 

     <% if(memberBean.getGender() != null && memberBean.getGender().equals("여")) { %> checked <% } %>>여성<br>

이메일: <input type="email" name="email" value="<%=memberBean.getEmail() %>">

<input type="submit" value="회원수정">

</form>

</body>

</html>

*updatePro.jsp

<%@page import="com.dao.MemberDao"%>

<%@page import="com.db.MyOracle"%>

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%-- 로그인 체크파일 최상단에 포함. 세션값 id변수 사용가능 --%>

<%@ include file="loginCheck.jspf" %>

<%-- post 한글처리 --%>

<% request.setCharacterEncoding("utf-8"); %>

<%-- 액션태그 useBean 객체생성 memberBean --%>

<jsp:useBean id="memberBean" class="com.bean.MemberBean"/>

<%-- 액션태그 setProperty 폼=>자바빈 멤버변수에 저장 --%>

<jsp:setProperty property="*" name="memberBean" />

<%

// DB객체생성

MemberDao memberDao = new MemberDao();

// int check = 메소드호출  updateMember(memberBean)

int check = memberDao.updateMember(memberBean);

// check == 1   "수정성공"   main.jsp이동

// check == 0   "패스워드틀림"  뒤로이동 

if (check == 1) {

%>

<script>

alert('수정성공');

location.href = 'main.jsp';

</script>

<%

} else { // check == 0

%>

<script>

alert('패스워드틀림');

history.back();

</script>

<%

}

%>

*delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@ include file="loginCheck.jspf" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<h1>회원삭제</h1>

<form action="deletePro.jsp" method="post">

아이디 : <input type="text" name="id" value="<%=id%>" readonly="readonly"><br>

패스워드 : <input type="password" name="passwd"><br>

<input type="submit" value="회원삭제">

</form>

</body>

</html>

*deletePro.jsp

<%@page import="com.dao.MemberDao"%>

<%@page import="com.db.MyOracle"%>

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@include file="loginCheck.jspf" %>

<%-- 액션태그 useBean 객체생성 memberBean --%>

<jsp:useBean id="memberBean" class="com.bean.MemberBean"/>

<%-- 액션태그 setProperty 폼=>자바빈 멤버변수에 저장. id, passwd 파라미터 --%>

<jsp:setProperty property="*" name="memberBean" />

<%

// DB객체생성

MemberDao memberDao = new MemberDao();

// int check = 메소드호출 deleteMember(memberBean)

int check = memberDao.deleteMember(memberBean);

// check == 1  "삭제성공"  loginForm.jsp이동

// check == 0  "패스워드틀림"   뒤로이동

if (check == 1) {

// 세션값 초기화

session.invalidate();

%>

<script>

alert('삭제성공');

location.href = 'loginForm.jsp';

</script>

<%

} else { // check == 0

%>

<script>

alert('패스워드틀림');

history.back();

</script>

<%

}

%>

*list.jsp

<%@page import="com.bean.MemberBean"%>

<%@page import="java.util.List"%>

<%@page import="com.dao.MemberDao"%>

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.PreparedStatement"%>

<%@page import="java.sql.Connection"%>

<%@page import="com.db.MyOracle"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<h1>전체회원목록</h1>

<hr>

<%

// 세션값 가져오기

String id = (String) session.getAttribute("id");

// 세션값 없으면(null이면) loginForm.jsp로 이동

if (id == null || !id.equals("admin")) {

response.sendRedirect("main.jsp");

return;

}

// DB 객체생성 memberDao

MemberDao memberDao = new MemberDao();

List<MemberBean> list = null;

list = memberDao.getMembers();

%>

<table border="1">

<tr>

<th>아이디</th><th>패스워드</th><th>이름</th>

<th>성별</th><th>나이</th><th>이메일</th><th>가입일자</th>

</tr>

<%

if (list.size() > 0) {

for (int i=0; i<list.size(); i++) {  // 한 행식 출력

MemberBean memberBean = list.get(i);

%>

<tr>

<td><%=memberBean.getId() %></td>

<td><%=memberBean.getPasswd() %></td>

<td><%=memberBean.getName() %></td>

<td><%=memberBean.getGender() %></td>

<td><%=memberBean.getAge() %></td>

<td><%=memberBean.getEmail() %></td>

<td><%=memberBean.getReg_date() %></td>

</tr>

<%

}

} else {  // list.size() == 0

%>

<tr>

<td colspan="7">데이터가 없습니다</td>

</tr>

<%

}

%>

</table></body>

<h3><a href="main.jsp">메인화면</a></h3>

</html>