member_servlet_mvc.zip----------수정처리--------------------------
시작 : modify_form.jsp - 주민번호 입력 부분 수정
Controller : ModifyMemberServlet
Service : MemberManageService.modifyMember(MemberDTO)
DAO : DAO.updateMember(MemberDTO)
응답 : 성공 : member_list.jsp
실패 : login안되서 - login_form.jsp
exception - error.jsp
<%@page import="member.dto.MemberDTO"%>
<%@ 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>
<jsp:include page="/menu.jsp"/>
<h2>회원정보수정</h2><hr><p>
<form action="/${initParam.context_root }/forward.jsp" method="post">
<input type="hidden" name="action" value="modifyMember">
<input type="hidden" name="id" value="${sessionScope.memberDTO.id }">
<table width="500">
<tr>
<td>아이디</td><td>${sessionScope.memberDTO.id }</td>
</tr>
<tr>
<td>비밀번호</td><td><input type="text" name="password"></td>
</tr>
<tr>
<td>이름</td><td><input type="text" name="name" value="${sessionScope.memberDTO.name }"></td>
</tr>
<tr>
<td>주민번호</td><td><input type="text" name="registerNumber1" value="${sessionScope.memberDTO.registerNumber1 }">-<input type="text" name="registerNumber2" value="${sessionScope.memberDTO.registerNumber2 }"></td>
</tr>
<tr>
<td>마일리지 ${sessionScope.memberDTO.mileage }</td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="정보수정"><input type="reset" value="다시작성"></td>
</tr>
</table>
</form>
</body>
</html>
<%@page import="member.dto.MemberDTO"%>
<%@ 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:useBean id="mdto" class="member.dto.MemberDTO" scope="request"/>
<jsp:setProperty property="*" name="mdto"/>
<!--<jsp:setProperty property="mileage" value="1000" name="mdto"/>-->
<jsp:forward page="/${param.action }"/>
<body>
</body>
</html>
package member.servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import member.dto.MemberDTO;
import memver.model.service.MemberManageService;
public class ModifyMemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public ModifyMemberServlet() {
super();
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
// 1. 파라미터 조회
HttpSession httpSession = request.getSession();
MemberDTO login_info = (MemberDTO) httpSession.getAttribute("memberDTO");
String resUrl = null;
if(login_info!= null){//로그인이 된 경우
try {
MemberManageService memberManageService = MemberManageService.getInstance();
MemberDTO mdto = (MemberDTO) request.getAttribute("mdto");
//update 시 오류가 날수 있기 때문에 DB를 먼저 update 하고 session의 값을 바꿔 줘야 한다.
memberManageService.modifyMember(mdto);
mdto.setMileage(login_info.getMileage());
httpSession.setAttribute("memberDTO", mdto);
resUrl = "/res/member_details.jsp";
} catch (SQLException e) {//예외 에러
e.printStackTrace();
request.setAttribute("error_message", e.getMessage());
resUrl = "/res/error.jsp";
}
}else{//로그인이 안 된경우
request.setAttribute("error_message", "로그인 부터 해주세요.");
resUrl = "/login_form.jsp";
}
RequestDispatcher requestDispatcher = request.getRequestDispatcher(resUrl);
requestDispatcher.forward(request, response);
}
}
<%@page import="member.dto.MemberDTO"%>
<%@ 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>
<jsp:include page="/menu.jsp"/>
<h2>회원정보보기</h2><p>
아이디 : ${sessionScope.memberDTO.id }<br>
이름 : ${sessionScope.memberDTO.name }<br>
주민번호 : ${sessionScope.memberDTO.registerNumber1 }-${sessionScope.memberDTO.registerNumber2 }<br>
마일리지 : ${sessionScope.memberDTO.mileage }<br>
<p>
<a href="/${initParam.context_root }/modifyForm">회원정보수정</a>
<a href="/${initParam.context_root }/removeMember">회원 탈퇴</a>
</p>
</body>
</html>
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 void modifyMember(MemberDTO mto) throws SQLException{
dao.updateMember(mto);
}
public void removeMemberById(String id)throws SQLException {
dao.deleteMemberById(id);
}
}
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 void updateMember(MemberDTO mto) throws SQLException{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "update member set password=?, name=?, register_Number1=?, register_number2=? where id=?";
try{
BasicDataSource dataSource = dbUtil.getDataSource();
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, mto.getPassword());
pstmt.setString(2, mto.getName());
pstmt.setString(3, mto.getRegisterNumber1());
pstmt.setString(4, mto.getRegisterNumber2());
pstmt.setString(5, mto.getId());
int cnt = pstmt.executeUpdate();
System.out.println(cnt+"개의 행이 수정되었습니다.");
}finally{
dbUtil.close(pstmt, conn);
}
}
public void deleteMemberById(String id) throws SQLException{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from member where id=?";
try{
BasicDataSource dataSource = dbUtil.getDataSource();
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
int cnt = pstmt.executeUpdate();
System.out.println(cnt+"개의 행이 삭제되었습니다.");
}finally{
dbUtil.close(pstmt, conn);
}
}
}
--------탈퇴처리----------------------------
시작 : member_detail.jsp 에서 탈퇴 링크 클릭시
Controller : RemoveMemberServlet(DB삭제처리, session 종료)
Service : removeMemberById(id) : void
DAO : deleteMemberById(id) : void
응답 : 성공 : login_form.jsp
실패 : 로그인이 안된 경우 : login_form.jsp(에러메세지 가지고 이동)
exception 이 나서 실패한경우 : error.jsp
<%@page import="member.dto.MemberDTO"%>
<%@ 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>
<jsp:include page="/menu.jsp"/>
<h2>회원정보보기</h2><p>
아이디 : ${sessionScope.memberDTO.id }<br>
이름 : ${sessionScope.memberDTO.name }<br>
주민번호 : ${sessionScope.memberDTO.registerNumber1 }-${sessionScope.memberDTO.registerNumber2 }<br>
마일리지 : ${sessionScope.memberDTO.mileage }<br>
<p>
<a href="/${initParam.context_root }/modifyForm">회원정보수정</a>
<a href="/${initParam.context_root }/removeMember">회원 탈퇴</a>
</p>
</body>
</html>
package member.servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import member.dto.MemberDTO;
import memver.model.service.MemberManageService;
public class RemoveMemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public RemoveMemberServlet() {
super();
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
HttpSession httpSession = request.getSession();
MemberDTO memberDTO = (MemberDTO) httpSession.getAttribute("memberDTO");
MemberManageService memberManageService = MemberManageService.getInstance();
String resUrl = null;
if (memberDTO != null) {
try {
memberManageService.removeMemberById(memberDTO.getId());
httpSession.invalidate();
resUrl = "/login_form.jsp";
} catch (SQLException e) {
e.printStackTrace();
request.setAttribute("error_message", e.getMessage());
resUrl = "/res/error.jsp";
}
} else {
request.setAttribute("error_message", "로그인 정보가 없습니다.");
resUrl = "/login_form.jsp";
}
RequestDispatcher requestDispatcher = request
.getRequestDispatcher(resUrl);
requestDispatcher.forward(request, response);
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:if test="${sessionScope.memberDTO != null }">
<jsp:forward page="/res/member_details.jsp"/>
</c:if>
<!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>
<c:if test="${requestScope.error_message!=null }">
<font color="red">${requestScope.error_message }</font>
</c:if><br>
<body>
<jsp:include page="/menu.jsp"/>
<form action="/${initParam.context_root }/login" method="post">
<table>
<tr>
<td><h2>로그인</h2><p></td>
</tr>
<tr>
<td>id</td><td><input type="text" name="id"></td>
</tr>
<tr>
<td>password</td><td><input type="password" name="password"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="로그인"><input type="reset" value="초기화"></td>
</tr>
</table>
</form>
</body>
</html>
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 void modifyMember(MemberDTO mto) throws SQLException{
dao.updateMember(mto);
}
public void removeMemberById(String id)throws SQLException {
dao.deleteMemberById(id);
}
}
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 void updateMember(MemberDTO mto) throws SQLException{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "update member set password=?, name=?, register_Number1=?, register_number2=? where id=?";
try{
BasicDataSource dataSource = dbUtil.getDataSource();
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, mto.getPassword());
pstmt.setString(2, mto.getName());
pstmt.setString(3, mto.getRegisterNumber1());
pstmt.setString(4, mto.getRegisterNumber2());
pstmt.setString(5, mto.getId());
int cnt = pstmt.executeUpdate();
System.out.println(cnt+"개의 행이 수정되었습니다.");
}finally{
dbUtil.close(pstmt, conn);
}
}
public void deleteMemberById(String id) throws SQLException{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from member where id=?";
try{
BasicDataSource dataSource = dbUtil.getDataSource();
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
int cnt = pstmt.executeUpdate();
System.out.println(cnt+"개의 행이 삭제되었습니다.");
}finally{
dbUtil.close(pstmt, conn);
}
}
}
* 결과보기
'프로그래밍 > JSP Programming' 카테고리의 다른 글
| 2012-5-15 MVC를 활용한 회원관리 프로그램(FrontController 사용) (0) | 2012.05.15 |
|---|---|
| 2012-5-14 FrontController 패턴 (0) | 2012.05.14 |
| 2012-5-13 MVC를 활용한 회원관리 전체 소스 (0) | 2012.05.13 |
| 2012-5-13 MVC패턴을 활용한 이름으로 회원 조회하기(like 사용) (0) | 2012.05.13 |
| 2012-5-11 MVC를 활용한 회원 등록조회하기2 (0) | 2012.05.11 |
