IT BANK) JSP 14일차 - JSP(회원관리 페이지(SQL))

220926

 

 

■ DB SQL 사용

 

sessionDB 폴더에 파일 복사

src/main/java 폴더에 sessioinDB 폴더 생성

// MemberDTO.java
package sessionDB;
/*
SQL> CREATE TABLE sessionDB(
  2  id varchar2(10),
  3  pw varchar2(10),
  4  name varchar2(15),
  5  email varchar2(30)
  6  );
Table created.

SQL> commit;
Commit complete.

SQL> DROP TABLE sessionDB;
Table dropped.

 */
public class MemberDTO {
	private String id;
	private String pw;
	private String name;
	private String email;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPw() {
		return pw;
	}
	public void setPw(String pw) {
		this.pw = pw;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
	
	
}

// MemberDAO.java
package sessionDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MemberDAO {
	private Connection con;

	public MemberDAO() {
		// 생성자 호출 시 DB 연결을 함.
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "oracle";
		String password = "oracle";

		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void disconnection() {
		// DB 연결을 종료함.
		try {
			if(con != null)
				con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public MemberDTO selectId(String id) {
		String sql = "SELECT * FROM sessionDB WHERE id = ?";

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setId(rs.getString("id"));
				member.setPw(rs.getString("pw"));
				member.setName(rs.getString("name"));
				member.setEmail(rs.getString("email"));
				return member;
			} else {
				System.out.println("입력한 데이터는 존재하지 않습니다.");
			}
		} catch (Exception e) {
		}
		// 사용자가 매개변수로 전달한 아이디가 데이터베이스에 없다는 의미.
		return null;
	}
	
	public void insert(MemberDTO member) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO sessionDB VALUES(?, ?, ?, ?)";
		
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, member.getId());
			ps.setString(2, member.getPw());
			ps.setString(3, member.getName());
			ps.setString(4, member.getEmail());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<MemberDTO> list(){
		String sql = "SELECT * FROM sessionDB";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<MemberDTO> members = new ArrayList<>();
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setId(rs.getString("id"));
				member.setPw(rs.getString("pw"));
				member.setName(rs.getString("name"));
				member.setEmail(rs.getString("email"));
				members.add(member);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return members;
	}
    
    public void update(MemberDTO member) {
        String sql = "UPDATE sessionDB SET pw=?, name=?, email=? WHERE id=?";
        PreparedStatement ps = null;

        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, member.getPw());
            ps.setString(2, member.getName());
            ps.setString(3, member.getEmail());
            ps.setString(4, member.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public void delete(String id) {
        String sql = "DELETE FROM sessionDB WHERE id=?";
        PreparedStatement ps = null;

        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

// index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>index</title>
<style type="text/css">
	a{text-decoration: none; color: black;}
</style>
</head>
<body>
<!-- http://localhost:8085/jspExam/sessionDB/index.jsp -->
	<h2>인덱스 페이지</h2>
	<a href="member.jsp">회원 가입</a> | 
	<a href="list.jsp">회원 목록</a> | 
	<a href="update.jsp">회원 수정</a> | 
	<a href="delete.jsp">회원 삭제</a> | 
	<a href="login.jsp">로그인</a> | 
	<a href="logout.jsp">로그아웃</a> | 
</body>
</html>

 

// member.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>member</title>
<script>
	function check(){
		id = document.getElementById('id');
		pw = document.getElementById('pw');
		confirmPw = document.getElementById('confirmPw');
 		if(id.value == "" || pw.value == ""){
			alert('필수 항목입니다.');
			return;
		}
		if(pw.value != confirmPw.value){
			alert('두 비밀번호가 일치하지 않습니다.');
			return;
		} 
		document.getElementById('f').submit();
	}
</script>
</head>
<body>
	<form action="memberService.jsp" method="post" id="f">
		<input type="text" name="id" id="id" placeholder="아이디"><br>
		<input type="password" name="pw" id="pw" placeholder="비밀번호"><br>
		<input type="password" name="confirmPw" id="confirmPw" placeholder="비밀번호 확인"><br>
		<input type="text" name="name" id="name" placeholder="이름"><br>
		<input type="text" name="email" id="email" placeholder="이메일"><br>
		<input type="button" value="회원 가입" onclick="check();">
		<input type="button" value="취소" onclick="location.href='index.jsp'">
	</form>
</body>
</html>

 

// memberService.jsp
<%@page import="sessionDB.MemberDTO"%>
<%@page import="sessionDB.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	request.setCharacterEncoding("utf-8");
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	String confirmPw = request.getParameter("confirmPw");
	String name = request.getParameter("name");
	String email = request.getParameter("email");
	
	// 서버측 입력값 검증
	if(id == "" || pw == "" || name == ""){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');location.href='member.jsp'</script>");
		return;
	}
	
	if(pw.equals(confirmPw) == false){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');</script>");
		response.sendRedirect("member.jsp");
		return;
	}
	
	// Data Access Object
	MemberDAO memberDao = new MemberDAO();
	MemberDTO check = memberDao.selectId(id);
	String message, path;
	if(check == null){
		// 가입하려고 하는 아이디가 없기에 회원가입을 진행함.
		MemberDTO member = new MemberDTO();
		member.setEmail(email);
		member.setId(id);
		member.setName(name);
		member.setPw(pw);
		memberDao.insert(member);
		message = "회원가입 완료";
		path = "index.jsp";
	}else{
		// 가입하려고 하는 아이디가 있기에 중복 계정으로 가입을 할 수 없음.
		message = "회원가입 실패";
		path = "member.jsp";
	}
	memberDao.disconnection();
%>
<script>alert('<%=message%>'); location.href='<%=path%>'</script>

// login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>login</title>
</head>
<body>
	<%
		String id = (String)session.getAttribute("id");
		if(id == null){
	%>
		<form action="loginService.jsp" method="post">
			<input type="text" placeholder="아이디" name="id"> <br>
			<input type="password" placeholder="비밀번호" name="pw" ><br> 
			<input type="submit" value="로그인" >
			<input type="reset" value="취소" >	
		</form>
	<%}else{ %>
		<%=id %>님 로그인 상태입니다. <br>
		<input type="button" value="index 페이지로 이동" onclick="location.href='index.jsp'">
	<%} %>
</body>
</html>

 

// loginService.jsp
<%@page import="sessionDB.MemberDTO"%>
<%@page import="sessionDB.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	
	// 서버측 입력값 검증
	if(id == "" || pw == "" ){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');location.href='login.jsp'</script>");
		return;
	}
	
	// 데이터베이스에서 아이디/패스워드 정보를 갖고 옴.
	MemberDAO memberDao = new MemberDAO();
	MemberDTO member = memberDao.selectId(id);
	memberDao.disconnection();
	if(member != null && member.getPw().equals(pw)){
		// 로그인 성공
		session.setAttribute("id", member.getId());
		session.setAttribute("name", member.getName());
		session.setAttribute("email", member.getEmail());
		response.sendRedirect("index.jsp");
		return;
	}
	// 로그인 실패 시 
%>
<script>alert('아이디 또는 비밀번호를 확인하세요'); location.href='login.jsp'</script>

// list.jsp
<%@page import="sessionDB.MemberDTO"%>
<%@page import="sessionDB.MemberDAO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>list</title>
</head>
<body>
	<% 
		MemberDAO memberDao = new MemberDAO();
		ArrayList<MemberDTO>members = memberDao.list();
	%>
	
	<table border=1>
		<tr>
			<th>아이디</th>
			<th>비밀번호</th>
			<th>이름</th>
			<th>이메일</th>
		</tr>
		<%
			for(MemberDTO m : members){
		%>		
			<tr>
				<td><%=m.getId() %></td>
				<td><%=m.getPw() %></td>
				<td><%=m.getName() %></td>
				<td><%=m.getEmail() %></td>
			</tr>
		<%} %>
	</table>
	<a href="index.jsp" style="text-decoration: none"> 인덱스 페이지로 이동합니다. </a>
</body>
</html>

 

// update.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>update</title>
<script>
	function check(){
		id = document.getElementById('id');
		pw = document.getElementById('pw');
		confirmPw = document.getElementById('confirmPw');
 		if(id.value == "" || pw.value == ""){
			alert('필수 항목입니다.');
			return;
		}
		if(pw.value != confirmPw.value){
			alert('두 비밀번호가 일치하지 않습니다.');
			return;
		} 
		document.getElementById('f').submit();
	}
</script>
</head>
<body>
<%
	String id = (String)session.getAttribute("id");
	if(id == null){
		out.print("<script>alert('로그인 후 이용하세요.'); location.href='login.jsp';</script>");
		return;
	}
%>
	<form action="updateService.jsp" method="post" id="f">
		<input type="text" name="id" id="id" value='<%=session.getAttribute("id") %>' disabled="disabled"><br>
		<input type="password" name="pw" id="pw" placeholder="비밀번호"><br>
		<input type="password" name="confirmPw" id="confirmPw" placeholder="비밀번호 확인"><br>
		<input type="text" name="name" id="name"  value='<%=session.getAttribute("name")%>'><br>
		<input type="text" name="email" id="email"  value='<%=session.getAttribute("email")%>'><br>
		<input type="button" value="회원 수정" onclick="check();">
		<input type="button" value="취소" onclick="location.href='index.jsp'">
	</form>
</body>
</html>

 

// updateService.jsp
<%@page import="sessionDB.MemberDTO"%>
<%@page import="sessionDB.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	request.setCharacterEncoding("utf-8");
	String id = (String)session.getAttribute("id");
	if(id == null){
		response.sendRedirect("login.jsp");
		return;
	}
	
	String pw = request.getParameter("pw");
	String confirmPw = request.getParameter("confirmPw");
	String name = request.getParameter("name");
	String email = request.getParameter("email");
	
	// 서버측 입력값 검증
	if(pw == "" || name == ""){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');location.href='update.jsp'</script>");
		return;
	}
	
	if(pw.equals(confirmPw) == false){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');</script>");
		response.sendRedirect("update.jsp");
		return;
	}
	
	// Data Access Object
	MemberDAO memberDao = new MemberDAO();

	// 수정하려고 하는 아이디가 있으면 MemberDAO에 데이터 전달해서 수정
	MemberDTO member = new MemberDTO();
	member.setEmail(email);
	member.setId(id);
	member.setName(name);
	member.setPw(pw);
	memberDao.update(member);
	String message = "회원 정보 수정 완료";
	String path = "index.jsp";
	memberDao.disconnection();
%>
<script>alert('<%=message%>'); location.href='<%=path%>'</script>

// delete.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>delete</title>
</head>
<body>
<%
	String id = (String)session.getAttribute("id");
	if(id == null){
		out.print("<script>alert('로그인 후 이용하세요.'); location.href='login.jsp';</script>");
		return;
	}
%>
	<form action="deleteService.jsp" method="post">
		<input type="text" value="<%=id %>" readonly="readonly"> <br>
		<input type="password" placeholder="비밀번호" name="pw" ><br> 
		<input type="password" placeholder="비밀번호 확인" name="confirmPw" ><br> 
		<input type="submit" value="회원 탈퇴" >
		<input type="button" value="취소" onclick="location.href='index.jsp'">
	</form>
</body>
</html>

 

// deleteService.jsp
<%@page import="sessionDB.MemberDTO"%>
<%@page import="sessionDB.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = (String)session.getAttribute("id");
	if(id == null){
		out.print("<script>alert('로그인 후 이용하세요.'); location.href='login.jsp';</script>");
		return;
	}
	
	request.setCharacterEncoding("utf-8");
	String pw = request.getParameter("pw");
	String confirmPw = request.getParameter("confirmPw");
	
	// 입력 값 검증
	if(pw == ""){
		out.print("<script>alert(' 필수 항목이다.'); location.href='delete.jsp';</script>");
		return;
	}
	
	// 입력 값 검증.
	if(pw.equals(confirmPw) == false){
		response.sendRedirect("delete.jsp");
		return ;
	}
	
	MemberDAO memberDao = new MemberDAO();
	MemberDTO member = memberDao.selectId(id);
	if(member.getPw().equals(pw) == false){
		out.print("<script>alert('비밀번호가 일치하지 않습니다.'); location.href='delete.jsp';</script>");
		return;
	}
	
	memberDao.delete(id);
	session.invalidate();
%>
<script>alert('회원 삭제 완료'); location.href='index.jsp';</script>

// logout.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
	session.invalidate();
	response.sendRedirect("index.jsp");
%>

 

 

SQL - DB 생성

SQL> CREATE TABLE sessionDB(
  2  id varchar2(10),
  3  pw varchar2(10),
  4  name varchar2(15),
  5  email varchar2(30)
  6  );

Table created.

SQL> commit;

Commit complete.

 

 

MemberDAO 생성자 호출시 DB 연결된다. 그래서 disconnection() 호출해서 연결 끊는다.