[JSP & Servlet] 5장 MVC 아키텍처 (5)

5.10 DAO 만들기

1. Model 역할 분리 전

- 서블릿이 컨트롤러와 모델 역할을 모두 하는 형태임

- 이제 모델 역할을 분리할 것임

2. Model 역할 분리

- Dao에서 결과 값을 리턴받는 형식임

3. 클라이언트 요청을 처리하는 흐름도

- Dao에서 쿼리를 날리면 Member 객체를 생성한다음에 서블릿에서 화면 생성을 위임

- 결과 화면을 출력

- 로그인 서블릿을 DAO를 분리해서 구성하는 작업을 통해 웹 애플리케이션을 만듬

4. DAO객체 실습 ( _20_MVC_DAO )

MemberDAO.java

package spms.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import spms.vo.Member;

public class MemberDAO {
	Connection connection;
	
	//DAO객체는 servlet이 아니기 때문에 servletcontext에 있는 커넥션 직접 접근 불가능
	//memberlistServlet에서 커넥션을 객체를 DAO에 주입해줄 것
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	public List<Member> selectlist() throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		
		String sqlSelect = "SELECT * FROM MEMBERS ORDER BY MNO ASC";
		
		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelect);
			
			ArrayList<Member> members = new ArrayList<Member>();
			
			while(rs.next()) {
				members.add(new Member()
						.setNo(rs.getInt("MNO"))
						.setName(rs.getString("MNAME"))
						.setEmail(rs.getString("EMAIL"))
						.setCreateDate(rs.getDate("CRE_DATE")));	
			}
			return members;
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

MemberListServlet.java

- 서블릿은 다리 역할만 해주게 됨

package spms.servlets;

import java.io.IOException;
import java.sql.Connection;

import javax.servlet.GenericServlet;
import javax.servlet.RequestDispatcher;
import javax.servlet.Servlet;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;

import spms.dao.MemberDAO;

/**
 * Servlet implementation class MemberListServlet
 */
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {
	/**
	 * @see Servlet#service(ServletRequest request, ServletResponse response)
	 */
	@Override
	public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		ServletContext sc = this.getServletContext();
		
		try {
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			//DAO객체 connection 주입
			memberDAO.setConnection(conn);
			
			//request에 회원 목록 데이터 보관
			request.setAttribute("memberList", memberDAO.selectlist());
			
			//JSP로 출력 -> 화면생성을 jsp로 위임할 때 RequestDispatcher 객체 사용
			RequestDispatcher rd = request.getRequestDispatcher(
					"/member/MemberList.jsp"
			);
			//rd.include(request, response);
			rd.forward(request, response);
		} catch(Exception e) {
			//throw new ServletException(e);
			
			request.setAttribute("error", e);
			RequestDispatcher rd = request.getRequestDispatcher("/Error.jsp");
			rd.forward(request, response);
		}
	}

}

실행 결과

5. Add 서블릿과 DAO객체 실습 ( _20_MVC_DAO )

MemberDAO.java - insert

	// MemberAddServlet에서 입력 폼으로 입력받은 데이터를 member객체로 담아서
	//DAO로 전달할 예정
	public int insert(Member member) throws Exception {
		int result = 0;
		PreparedStatement stmt = null;
		final String sqlInsert = "INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)" + 
									"VALUES(?, ?, ?, NOW(), NOW())";
		
		try {
			stmt = connection.prepareStatement(sqlInsert);
			stmt.setString(1, member.getEmail());
			stmt.setString(2, member.getPassword());
			stmt.setString(3, member.getName());
			//insert 성공하면 1 int 값 리턴
			//정상적으로 들어갔는지를 int 리턴 값을 가지고 서블릿에서 처리
			stmt.executeUpdate();
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		
		return result;
	}

MemberDAO.java - select

	//해당 멤버 데이터 조회
	public Member selectOne(int no) throws Exception {
		Member member = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		final String sqlSelectOne = "Select * FROM MEMBERS WHERE MNO=" + no;
		
		try {
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sqlSelectOne);
			if(rs.next()) {
				member = new Member()
									.setNo(rs.getInt("MNO"))
									.setEmail(rs.getString("EMAIL"))
									.setName(rs.getString("MNAME"))
									.setCreateDate(rs.getDate("CRE_DATE"));
									
			} else {
				throw new Exception("해당 번호의 회원을 찾을 수 없습니다.");
			}
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return member; //member객체에 담아서 리턴해줌
	}

MemberAddServlet.java


/**
 * Servlet implementation class MemberAddServlet
 */
@WebServlet("/member/add")
//HttpServlet은 GenericServlet을 상속받은 클래스
//service() 메소드를 호출방식에 따라 doGet(), doPost(), doPut(), doDelete()로 분리해놓음
public class MemberAddServlet extends HttpServlet {
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	//url에 주소 직접 입력 후 접속, 링크로 걸려있는 주소로 접속 -> get방식
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		RequestDispatcher rd = request.getRequestDispatcher("/member/MemberForm.jsp");
		rd.forward(request, response);
	}
	
	//입력폼에 입력된 정보를 submit할때 method 방식이 post이므로
	//doPost에서 데이터 입력 처리
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ServletContext sc = this.getServletContext();
		
		try {
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			memberDAO.setConnection(conn);
			
			//input폼에서 받은 파라미터들을 Member객체에 담음
			int result = memberDAO.insert(new Member()
							.setEmail(request.getParameter("email"))
							.setPassword(request.getParameter("password"))
							.setName(request.getParameter("name")));
	
			//sendRedirect 밑의 결과 값 출력 안하고 바로 /member/list로 이동
			//쿼리가 성공했을 경우 list페이지로
			if(result == 1) {
				response.sendRedirect("list");
			} //실패했을 경우 Error페이지로
			else {
				RequestDispatcher rd = request.getRequestDispatcher("Error.jsp");
				rd.forward(request, response);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
}

실행 화면 - 로그인하기

실행 화면 - 회원 등록

6. Delete 서블릿과 DAO객체 실습 ( _20_MVC_DAO )

MemberDAO.java - delete

	public int delete(int no) throws Exception {
		int result = 0;
		Statement stmt = null;
		final String sqlDelete = "DELETE FROM MEMBER WHERE MNO=" + no;
		
		try {
			stmt = connection.createStatement();
			result = stmt.executeUpdate(sqlDelete); // result에 sql 결과 값을 받아서 처리해줌
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

MemberDeleteServlet.java


/**
 * Servlet implementation class MemberDeleteServlet
 */
@WebServlet("/member/delete")
public class MemberDeleteServlet extends HttpServlet {
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		try {
			ServletContext sc = this.getServletContext();
			
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			memberDAO.setConnection(conn);
			
			int result = memberDAO.delete(Integer.parseInt(request.getParameter("no")));
			
			if(result == 1) {
				response.sendRedirect("list");
			} else {
				RequestDispatcher rd = request.getRequestDispatcher("/Error.jsp");
				rd.forward(request, response);
			}			
		} catch(Exception e) {
			e.printStackTrace();
			request.setAttribute("error", e);
			RequestDispatcher rd = request.getRequestDispatcher("/error.jsp");
			rd.forward(request, response);
		}
	}
}

실행 화면 - 회원 삭제

* 회원 목록에서 바로 삭제 버튼을 눌러도 회원 삭제 가능

7. Update 서블릿과 DAO객체 실습 ( _20_MVC_DAO )

MemberDAO.java - update

	//해당 멤버 데이터 수정
	public int update(Member member) throws Exception {
		int result = 0;
		PreparedStatement stmt = null;
		final String sqlInsert = "UPDATE MEMBERS SET EMAIL=?, MNAME=?, MOD_DATE=NOW() WHERE MNO=?";
		
		try {
			stmt = connection.prepareStatement(sqlInsert);
			stmt.setString(1, member.getEmail());
			stmt.setString(2, member.getName());
			stmt.setInt(3, member.getNo());
			result = stmt.executeUpdate();
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

MemberUpdateServlet.java

/**
 * Servlet implementation class MemberUpdateServlet
 */
@WebServlet("/member/update")
public class MemberUpdateServlet extends HttpServlet {
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		//서블릿 컨텍스트 객체 생성
		ServletContext sc = this.getServletContext();

		
		try {
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			memberDAO.setConnection(conn);
			
			Member member = memberDAO.selectOne(Integer.parseInt(request.getParameter("no")));
			
			request.setAttribute("member", member);
			
			
			RequestDispatcher rd = request.getRequestDispatcher("/member/MemberUpdateForm.jsp");
			rd.forward(request, response);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		//서블릿 컨텍스트 객체 생성
		ServletContext sc = this.getServletContext();

		try {
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			memberDAO.setConnection(conn);
			
			//member객체 안에 있는 내용으로 업데이트 실행
			int result = memberDAO.update(new Member()
						 .setNo(Integer.parseInt(request.getParameter("no")))
						 .setName(request.getParameter("name"))
						 .setEmail(request.getParameter("email")));
			
			if(result == 1) {
				response.sendRedirect("list");
			} //실패했을 경우 Error페이지로
			else {
				RequestDispatcher rd = request.getRequestDispatcher("Error.jsp");
				rd.forward(request, response);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
}

실행 화면 - 회원 정보 수정

8. 로그인 서블릿과 DAO객체 실습 ( _20_MVC_DAO )

MemberDAO.java - exist

	//exist메소드를 통해 해당 멤버가 있는지 확인
	public Member exist(String email, String password) throws Exception {
		Member member = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		// 해당 멤버가 있는지 조회
		final String sqlExist = "SELECT * FROM MEMBERS WHERE EMAIL=? AND PWD=?";
		
		try { //해당 멤버가 있으면 그 멤버 객체를 return
			stmt = connection.prepareStatement(sqlExist);
			stmt.setString(1, email);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if(rs.next()) {
				member = new Member()
									.setName(rs.getString("MNAME"))
									.setEmail(rs.getString("EMAIL"));
									
			} else { //해당 멤버가 없으면 null을 반환해서 로그인 실패 페이지로 이동
				return null;
			}
		} catch(Exception e) {
			throw e;
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return member;
	}

MemberLoginServlet.java

	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub

		String sqlLogin = "SELECT MNAME, EMAIL FROM MEMBERS WHERE EMAIL=? AND PWD=?";
		
		try {
			ServletContext sc = this.getServletContext();
			Connection conn = (Connection)sc.getAttribute("conn");
			
			MemberDAO memberDAO = new MemberDAO();
			memberDAO.setConnection(conn);
			
			Member member = memberDAO.exist(request.getParameter("email"), request.getParameter("password"));
			
			
			//로그인 성공 시
			if(member != null) {
				HttpSession session = request.getSession();
				session.setAttribute("Member", member);
				response.sendRedirect("../member/list");
			} //로그인 실패시
			else {
				RequestDispatcher rd = request.getRequestDispatcher("/auth/LoginFail.jsp");
				rd.forward(request, response);
			}
		} catch(Exception e) {
			throw new ServletException(e);
		}
	}

실행 화면 - 로그인

저작자표시 비영리 변경금지

'👨‍💻 2. 웹개발_Back end > 2-4 JSP & Servlet' 카테고리의 다른 글

[JSP & Servlet] 5장 MVC 아키텍처 (7) - DB 커넥션풀 (0)	2021.10.17
[JSP & Servlet] 5장 MVC 아키텍처 (6) - ServletContextListener를 이용한 객체 공유 (0)	2021.10.15
[JSP & Servlet] 5장 MVC 아키텍처 (4) - 뷰와 서블릿 분리 및 JSP 컨텍스트와 Action Tag (1)	2021.10.14
[JSP & Servlet] 5장 MVC 아키텍처 (3) - 데이터 보관소 (0)	2021.10.14
[JSP & Servlet] 5장 MVC 아키텍처 (2) - 뷰 분리 및 포워딩과 인클루딩 (0)	2021.10.14