본문 바로가기
학습/JSP

16. 종합(회원목록, 추가, 삭제, 로그인)

by Elfen Lied 2022. 11. 14.
반응형

- 오라클 데이터베이스

-- 테이블 삭제
DROP TABLE T_MEMBER;

-- 회원테이블 생성
CREATE TABLE T_MEMBER(
    MNO INT PRIMARY KEY,
    ID VARCHAR2(10) NOT NULL UNIQUE,
    PWD VARCHAR2(10),
    NAME VARCHAR2(50),
    EMAIL VARCHAR2(50),
    JOINDATE DATE DEFAULT SYSDATE
);

-- PRIMARY KEY : 기본키
-- VARCHAR2(10) : 문자열 자료형, 길이10(영문10자, 한글3자)
-- DATE : 날짜 자료형,
-- DEFAULT SYSDATE : 날짜를 입력하지않으면 현재 시각으로 설정

-- 시퀀스
CREATE SEQUENCE MNO_SEQ;
DROP SEQUENCE MNO_SEQ;


INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'kim', '1212', '김범수', 'kim@gmail');
INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'na', '2345', '나얼', 'na@gmail');
INSERT INTO T_MEMBER(MNO, ID, PWD, NAME, EMAIL)
VALUES(MNO_SEQ.NEXTVAL, 'park', '6666', '박효신', 'park@gmail');

SELECT * FROM T_MEMBER;
SELECT U_ID, U_NAME FROM T_MEMBER;

-- 테이블의 모든 로우 개수
SELECT COUNT(*) FROM T_MEMBER;

SELECT * FROM T_MEMBER WHERE ID='park' AND PWD='6666';

-- id가 'park'이고 pwd가 '6666'인 로우의 개수
SELECT count(*) FROM T_MEMBER WHERE id='park' AND pwd='6666';

-- id가 'park' 또는 pwd가 '6666'인 로우
SELECT count(*) FROM T_MEMBER WHERE id='park' OR pwd='6666';

-- 별칭(alias)
SELECT count(*) AS 결과 FROM T_MEMBER WHERE id='park' AND pwd='6666';

SELECT decode(count(*),1,'true','false') AS result FROM T_MEMBER WHERE ID='lee' AND PWD='3456';

DELETE FROM T_MEMBER WHERE U_ID='kim';

COMMIT;

회원 테이블 생성과 시퀀스 그리고 커밋 해줌

미리 데이터를 넣어도 상관없다.

 

패키지 3개 만들어 준다. 

dao, model, servlet

전체 파일트리 참고

 

jar 파일 참고

 

- MemberVO

lombok 사용함.

package model;

import java.sql.Date;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Setter
@Getter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class MemberVO {
	private int mno;
	private String id;
	private String pwd;
	private String name;
	private String email;
	private Date joinDate;
	
}

 

 

- MemberDao

interface로 만듬

package dao;

import java.util.List;

import model.MemberVO;

public interface MemberDao {
	
	// 회원목록
	List<MemberVO> memberList();
	
	// 회원추가
	void addMember(MemberVO vo);
	
	// 회원삭제
	void delMember(int mno);
	
	// 로그인 인증검사
	boolean isExisted(MemberVO vo);
}

 

 

- MemberDaoImpl

MemberDao를 만든뒤 생성

package dao;

import java.sql.Connection;
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;

import model.MemberVO;

public class MemberDaoImpl implements MemberDao {

	private static MemberDaoImpl dao = new MemberDaoImpl();

	private DataSource dataSource;

	private MemberDaoImpl() {
		try {
			Context context = new InitialContext();
			Context env = (Context) context.lookup("java:/comp/env");
			dataSource = (DataSource) env.lookup("jdbc/oracle");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	public static MemberDaoImpl getInstance() { // 싱글톤은 스테틱으로
		return dao;
	}

	@Override
	public List<MemberVO> memberList() { // 회원목록
		List<MemberVO> list = new ArrayList<MemberVO>();
		String query = "SELECT * FROM T_MEMBER";
		try(			
			Connection conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
		) {
			while (rs.next()) {
				MemberVO vo = new MemberVO();
					vo.setMno(rs.getInt("mno"));
					vo.setId(rs.getString("id"));
					vo.setPwd(rs.getString("pwd"));
					vo.setName(rs.getString("name"));
					vo.setEmail(rs.getString("email"));
					vo.setJoinDate(rs.getDate("joinDate"));
				list.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void addMember(MemberVO vo) { // 회원 추가
		String query = "INSERT INTO T_MEMBER(mno, id, pwd, name, email) VALUES(mno_seq.nextval,?, ?, ?,?)";
		try(
			Connection conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
		) {
			pstmt.setString(1, vo.getId());
			pstmt.setString(2, vo.getPwd());
			pstmt.setString(3, vo.getName());
			pstmt.setString(4, vo.getEmail());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void delMember(int mno) { // 회원 삭제
		String query = "DELETE FROM T_MEMBER WHERE mno=?";
		try(
			Connection conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
		) {
			pstmt.setInt(1, mno);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean isExisted(MemberVO vo) {// 로그인 인증검사
		boolean result = false;
		String query = "SELECT decode(count(*),1,'true','false') as result "
				+ "FROM T_MEMBER WHERE ID=? AND PWD=?";
		try(
			Connection conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(query);
			
		) {
			pstmt.setString(1, vo.getId());
			pstmt.setString(2, vo.getPwd());
			try(ResultSet rs = pstmt.executeQuery();) {
				while (rs.next()) {
					result = Boolean.parseBoolean(rs.getString("result"));
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

}

 

 

- MemberServlet

package servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.MemberDao;
import model.MemberVO;

@WebServlet("/member")
public class MemberServlet extends HttpServlet {

	private MemberDao dao;

	@Override
	public void init() throws ServletException {
		ServletContext ctx = getServletContext();
		MemberDao dao = (MemberDao) ctx.getAttribute("memberDao");
		this.dao = dao;
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		
		// 서블릿이 어떤 기능을 수행할지 결정
		String command = request.getParameter("command");
		
		if(command != null && command.equals("addMember")) { // 회원추가
			
			MemberVO vo = new MemberVO();
			vo.setId(request.getParameter("id"));
			vo.setPwd(request.getParameter("pwd"));
			vo.setName(request.getParameter("name"));
			vo.setEmail(request.getParameter("email"));
			dao.addMember(vo);
			response.sendRedirect("/member_pro01/member");

		} else if(command != null && command.equals("delMember")) { // 회원삭제
			String inputMno = request.getParameter("mno");
			
			// 예외처리 
			try {
				int mno = Integer.parseInt(inputMno);
				dao.delMember(mno);
				response.sendRedirect("/member_pro01/member");
			} catch (Exception e) {
				request.getRequestDispatcher("/exception/error.jsp").forward(request, response);
			}
		
		} else { // 회원조회
			List<MemberVO> memberList = dao.memberList();
			request.setAttribute("memberList", memberList);
			RequestDispatcher rd = request.getRequestDispatcher("/member/memberList.jsp");
			rd.forward(request, response);
		}
	}

}

 

 

- AppServlet

package servlet;

import java.io.IOException;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.MemberDaoImpl;

@WebServlet(urlPatterns = {"/AppServlet"}, loadOnStartup = 1)
public class AppServlet extends HttpServlet {

	@Override
	public void init() throws ServletException {
		ServletContext ctx = getServletContext();
		ctx.setAttribute("memberDao", MemberDaoImpl.getInstance()); // MemberDao구현체를 서블릿 컨텍스트에 바인딩
		
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}

}

 

 

- LoginServlet

package servlet;

import java.io.IOException;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import dao.MemberDao;
import model.MemberVO;

@WebServlet("/login")
public class LoginServlet extends HttpServlet {

	private MemberDao dao;
	
	@Override
	public void init() throws ServletException {
		ServletContext ctx = getServletContext();
		MemberDao dao = (MemberDao) ctx.getAttribute("memberDao");
		this.dao = dao;
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		MemberVO vo = new MemberVO();
		vo.setId(request.getParameter("user_id"));
		vo.setPwd(request.getParameter("user_pw"));
		boolean result = dao.isExisted(vo); // 로그인 결과
		
		if(result) {
			// 세션 객체에 아이디 데이터 바인딩
			HttpSession session = request.getSession();
			session.setAttribute("isLogon", true);
			session.setAttribute("login.id", vo.getId());
			response.sendRedirect("/member_pro01/member");
		} else {
			// 다시 로그인 요청
			response.sendRedirect("/member_pro01/member/login.html");
		}
	}

}

 

 

폴더 2개 만들기

member, exception

 

 

- exception 폴더에 error.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>잘못된 접근입니다. 관리자에게 문의하세요.</h1>
</body>
</html>

 

 

- member 폴더에 memberList.jsp

<%@page import="model.MemberVO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	List<MemberVO> memberList = (List<MemberVO>) request.getAttribute("memberList");
	String id = (String) session.getAttribute("login.id");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%if(id != null) { %>
		<h2><%= id %>님이 로그인 중</h2>
	<% } %>
	
	<table border="1">
		<tr>
			<th>번호</th>
			<th>아이디</th>
			<th>비밀번호</th>
			<th>이름</th>
			<th>이메일</th>
			<th>회원삭제</th>
		</tr>
		<% for(MemberVO vo : memberList) {%>
		<tr>
			<td><%= vo.getMno() %></td>
			<td><%= vo.getId() %></td>
			<td><%= vo.getPwd() %></td>
			<td><%= vo.getName() %></td>
			<td><%= vo.getEmail() %></td>
			<td><a href="/member_pro01/member?command=delMember&mno=<%= vo.getMno() %>">삭제</a></td>
		</tr>
		<% } %>
	</table>
	<a href="member/joinMember.html">회원가입하기</a><br>
	
	<%if(id == null) { %>
		<a href="member/login.html">로그인</a>
	<% } %>
	
</body>
</html>

 

 

- member 폴더에 joinMember.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원가입</h1>
	<form action="/member_pro01/member" method="post">
		<input type="hidden" name="command" value="addMember">
		아이디 : <input type="text" name="id"><br>
		비밀번호 : <input type="password" name="pwd"><br>
		이름 : <input type="text" name="name"><br>
		이메일 : <input type="text" name="email"><br>
		<button>회원가입</button>
	</form>
</body>
</html>

 

 

- member 폴더에 login.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="/member_pro01/login" method="post">
	아이디 : <input type="text" name="user_id"><br>
	비밀번호 : <input type="password" name="user_pw"><br>
	<button>로그인</button>
</form>
</body>
</html>

 

 

MemberServlet에서 실행

 

회원가입하기

 

가입한 뒤 데이터가 들어간 상태

 

로그인 하기 ( choi로 로그인 해봄)

"아이디"님이 로그인 중 이라고 뜬다.

 

삭제 하기 (8번 hey 를 삭제해봄)

 

 

세션을 지우고 새로고침해서 로그인중이 사라지는지 확인

로그인 세션은 삭제되고,

기본 쿠키가 새로 만들어지고,

로그인이 아닌 상태로 변함

반응형
저작자표시 (새창열림)

'학습 > JSP' 카테고리의 다른 글

17. 여러가지 URL 패턴 적용  (0) 2022.11.16
19. 여러 가지 서블릿 관련 Listener API  (0) 2022.11.15
15. 세션  (0) 2022.11.10
14. 쿠키  (0) 2022.11.09
13. <hidden>태그와 URL Rewriting 이용해 웹 페이지 연동  (0) 2022.11.09

관련글

댓글

티스토리툴바