2012-5-13 MVC패턴을 활용한 이름으로 회원 조회하기(like 사용)

<%@ 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>
<jsp:include page="/menu.jsp"/>
<body>
<font size="2" color="red">${requestScope.error_message }</font><br>
마일리지 검색하기<hr><p>
<form action="/${initParam.context_root }/searchByMileageRange" method="post">
범위 : <input type = "text" name="startMileage">~<input type = "text" name="endMileage">
<input type="submit" value="검색">
</form><br><br>
이름으로 검색하기<hr><p>
<form action="/${initParam.context_root }/searchByName" method="post">
검색할 이름 : <input type="text" name="searchName">
<input type="submit" value="검색">
</form>
</body>
</html>

package member.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import member.dto.MemberDTO;
import memver.model.service.MemberManageService;

public class SearchByName extends HttpServlet {
private static final long serialVersionUID = 1L;

    public SearchByName() {
        super();

    }

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  doPost(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  String searchName = null;
  MemberManageService memberManageService = MemberManageService.getInstance();
  ArrayList<MemberDTO> list;
  String resUrl = null;
  try{
   searchName = request.getParameter("searchName");
  }catch(NullPointerException e){
   e.printStackTrace();
   request.setAttribute("error_message", e.getMessage()+"값을 입력해주세요");

   RequestDispatcher dispatcher = request.getRequestDispatcher("/search_form.jsp");
   dispatcher.forward(request, response);
   return;
  }

  try{
   list = memberManageService.selectMemberByName(searchName);
   request.setAttribute("searchNameList", list);
   request.setAttribute("title", "검색 이름으로 조회 결과");
   request.setAttribute("search_value", searchName);
   System.out.println("searchName : "+searchName);
   resUrl = "/res/search_result.jsp";
  } catch (SQLException e) {
   e.printStackTrace();
   request.setAttribute("error_message", e.getMessage());
   resUrl = "/res/error.jsp";
  }
  RequestDispatcher dispatcher = request.getRequestDispatcher(resUrl);
  dispatcher.forward(request, response);
}

}

package memver.model.service;

import java.sql.SQLException;
import java.util.ArrayList;

import member.dto.MemberDTO;
import member.exception.DuplicatedIdException;
import member.exception.DuplicatedRegisterNumberException;
import member.model.dao.MemberDAO;

public class MemberManageService {
//회원 관리 비지니스 로직을 처리하는 Business Service 클래스
private static MemberManageService instance = new MemberManageService();
private MemberDAO dao;
private MemberManageService(){
dao = MemberDAO.getInstance();
}
public static MemberManageService getInstance(){
return instance;
}
//가입 처리 메소드
public void registerMember(MemberDTO mto)throws SQLException, DuplicatedIdException, DuplicatedRegisterNumberException{

  //1. 등록된 ID가 있는 지 체크
  //   - 이미 등록된 ID라면 DuplicatedIdException를 발생시킨다.
  if(dao.selectMemberById(mto.getId())!=null){
   throw new DuplicatedIdException(mto.getId()+"는 이미등록된 아이디 입니다.");

  }
  //2. 등록된 주민번호가 있는 지 체크
  //   - 이미 등록된 주민번호라면 DuplicatedRegisterNumberException 발생시킨다.
  if(dao.selectMemberByregisterNumber(mto.getRegisterNumber1(), mto.getRegisterNumber2())!=null){
   throw new DuplicatedRegisterNumberException("는 이미 가입된 주민번호 입니다.");
  }
  //3. 디비에 회원정보 등록 처리
  dao.insertMember(mto);



}
//전체 회원목록 조회
public ArrayList<MemberDTO> getMemberList() throws SQLException{
  return dao.selectAllMember();
}
//마일리지 범위로 조회
public ArrayList<MemberDTO> selectMemberByMileageRange(int startMileage, int endMileage)throws SQLException {
  return dao.selectMemberByMileageRange(startMileage, endMileage);
}
//ID로 회원 목록조회
public MemberDTO getMemberById(String id) throws SQLException{
  return dao.selectMemberById(id);
}

//이름으로 회원 목록 조회
public ArrayList<MemberDTO> selectMemberByName(String searchName) throws SQLException{

return dao.selectMemberName(searchName);
}
}

package member.model.dao;

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

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import member.dto.MemberDTO;
import member.util.DatabaseUtility;

public class MemberDAO {
private static MemberDAO instance = new MemberDAO();
private DatabaseUtility dbUtil;
private MemberDAO(){
  dbUtil = DatabaseUtility.getInstance();
}
public static MemberDAO getInstance(){
  return instance;
}
/*
* id로 회원 정보를 조회하야 return 하는 메소드
* @param id 조회할 회원 ID
* @return
* @throws SQLException
*/
public MemberDTO selectMemberById(String id)throws SQLException{
  //조회된 결과가 없다면 mto에 null값이 넘어간다.
  MemberDTO mto= null;
  Connection conn=null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
  String sql = "select id, password, name, register_number1, register_number2, mileage from member where id=?";
  try{
   BasicDataSource ds = dbUtil.getDataSource();
   conn = ds.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, id);
   rset = pstmt.executeQuery();
   if(rset.next()){
    mto = new MemberDTO(rset.getString(1),rset.getString(2),rset.getString(3),rset.getString(4),rset.getString(5),rset.getInt(6));
   }
  }finally{
   dbUtil.close(rset, pstmt, conn);
  }
  return mto;
}
/*
* 주민번호로 회원 정보를 조회하는 메소드
* @param registerNumber1 주민번호 앞6자리
* @param registerNumber2 주민번호 뒤7자리
* @return
* @thows SQLException
*/
public MemberDTO selectMemberByregisterNumber(String registerNumber1, String registerNumber2)throws SQLException{
  MemberDTO mto= null;
  BasicDataSource ds = dbUtil.getDataSource();
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
  String sql = "select id, password, name, register_number1, register_number2, mileage from member where register_Number1=? and register_Number2=?";
  try{
   conn = ds.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, registerNumber1);
   pstmt.setString(2, registerNumber2);
   rset = pstmt.executeQuery();
   if(rset.next()){
    mto = new MemberDTO(rset.getString(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getInt(6));
   }
  }finally{
   dbUtil.close(rset, pstmt, conn);
  }
  return mto;
}
/*
* 회원정보를 DB에 insert하는 메소드
* @param mto 등록할 회원 정보
* @throws SQLEception
*/
public void insertMember(MemberDTO mto)throws SQLException{
  BasicDataSource ds = dbUtil.getDataSource();
  Connection conn = null;
  PreparedStatement pstmt = null;
  String sql = "insert into member (id, password, name, register_number1, register_number2, mileage) values(?,?,?,?,?,?)";

  try{
  conn = ds.getConnection();
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, mto.getId());
  pstmt.setString(2, mto.getPassword());
  pstmt.setString(3, mto.getName());
  pstmt.setString(4, mto.getRegisterNumber1());
  pstmt.setString(5, mto.getRegisterNumber2());
  pstmt.setInt(6, mto.getMileage());
  int cnt = pstmt.executeUpdate();
  System.out.println(cnt+"개의 행이 삽입되었습니다.");
  }finally{
   dbUtil.close(pstmt, conn);
  }
}
public ArrayList<MemberDTO> selectAllMember() throws SQLException{
  ArrayList<MemberDTO> list = new ArrayList<MemberDTO>();

  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
  String sql = "select id, password, name, register_number1, register_number2, mileage from member";
  try{
   BasicDataSource dataSource = dbUtil.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   rset = pstmt.executeQuery();

   while(rset.next()){
    list.add(new MemberDTO(rset.getString(1), rset.getString(2),
      rset.getString(3), rset.getString(4), rset.getString(5), rset.getInt(6)));

   }
  }finally{
   dbUtil.close(rset, pstmt, conn);
  }

  return list;
}
public ArrayList<MemberDTO> selectMemberByMileageRange(int startMileage, int endMileage)throws SQLException {
  ArrayList<MemberDTO> searchMileageRangeMemberList = new ArrayList<MemberDTO>();
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
//  String sql = "select id, password, name, register_number1, register_number2, mileage from member where mileage>=? and mileage<=?";
  String sql = "select id, password, name, register_number1, register_number2, mileage from member where mileage between ? and ?";
  try{
   BasicDataSource dataSource = dbUtil.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, startMileage);
   pstmt.setInt(2, endMileage);
   rset = pstmt.executeQuery();
   while(rset.next()){
    searchMileageRangeMemberList.add(new MemberDTO(rset.getString(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getInt(6)));
   }
  }finally{
   dbUtil.close(rset, pstmt, conn);
  }
  return searchMileageRangeMemberList;
}
public ArrayList<MemberDTO> selectMemberName(String searchName) throws SQLException{
  ArrayList<MemberDTO> searchNameMemberList = new ArrayList<MemberDTO>();
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rset = null;
  String sql = "select id, password, name, register_number1, register_number2, mileage from member where name like '%'||?||'%'";
  try{
   BasicDataSource dataSource = dbUtil.getDataSource();
   conn = dataSource.getConnection();
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, searchName);
   rset = pstmt.executeQuery();
   while(rset.next()){
    searchNameMemberList.add(new MemberDTO(rset.getString(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getString(5), rset.getInt(6)));
    System.out.println(searchNameMemberList);
   }
  }finally{
   dbUtil.close(rset, pstmt, conn);
  }
  return searchNameMemberList;
}
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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>

<jsp:include page="/menu.jsp"/>
<h2> ${requestScope.title } </h2><hr><p>
조회범위 : ${requestScope.search_value }
<table width="500" border="1" cellspacing="0">

<tr align="center">
<td>순번</td><td>아이디</td><td>이름</td><td>주민번호</td><td>마일리지</td>
</tr>
<c:forEach items="${requestScope.searchMileageRangeMemberList }" var="member_list" varStatus="cnt">
<tr align="center">
<td>${cnt.count }</td><td>${member_list.id }</td><td><a href="/servlet_member/SearchMemberByID?id=${member_list.id }">${member_list.name }</a></td><td>${member_list.registerNumber1 }-${member_list.registerNumber2 }</td><td>${member_list.mileage }</td>
</tr>
</c:forEach>
<c:forEach items="${requestScope.searchNameList }" var="member_list" varStatus="cnt">
<tr align="center">
<td>${cnt.count }</td><td>${member_list.id }</td><td><a href="/servlet_member/SearchMemberByID?id=${member_list.id }">${member_list.name }</a></td><td>${member_list.registerNumber1 }-${member_list.registerNumber2 }</td><td>${member_list.mileage }</td>
</tr>
</c:forEach>
</table>
</body>
</html>