DAO 이용한 회원관리

JSP

DAO 이용한 회원관리

H_eh 2022. 7. 15. 13:38

회원가입

회원가입 폼 - MemberJoin.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>MemberJoin</title>
</head>

<body bgcolor="aliceblue">
	<form action="JoinProc.jsp" method="post">
	<table width="500" border="1">
	<tr height="50">
		<td width="150" align="center"> 아이디</td>
		<td width="350" align="center"><input type="text" name="id" size="40"></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 패스워드</td>
		<td width="350" align="center"><input type="password" name="pass1" size="40"></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 패스워드확인</td>
		<td width="350" align="center"><input type="password" name="pass2" size="40"></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 이메일</td>
		<td width="350" align="center"><input type="email" name="email" size="40"></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 전화번호</td>
		<td width="350" align="center"><input type="tel" name="tel" size="40"></td>
	</tr>
		
	<tr height="50">
		<td width="150" align="center"> 당신의관심분야</td>
		<td width="350" align="center">
			<input type="checkbox" name="hobby" value="캠핑">캠핑   
			<input type="checkbox" name="hobby" value="등산">등산   
			<input type="checkbox" name="hobby" value="영화">영화   
			<input type="checkbox" name="hobby" value="독서">독서   
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 당신의직업은</td>
		<td width="350" align="center">
			<select name="job">
				<option value="교사">교사</option>
				<option value="변호사">변호사</option>
				<option value="의사">의사</option>
				<option value="기술사">기술사</option>
			</select>
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 당신의연령은</td>
		<td width="350" align="center">
			<input type="radio" name="age" value="10">10대   
			<input type="radio" name="age" value="20">20대   
			<input type="radio" name="age" value="30">30대   
			<input type="radio" name="age" value="40">40대   
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 하고싶은말</td>
		<td width="350" align="center">
			<textarea rows="5" cols="40" name="info"></textarea>
		</td>
	</tr>
	
	<tr height="50">
		<td align="center" colspan="2">
			<input type="submit" value="회원가입">
			<input type="reset" value="취소">
		</td>
	</tr>
	</table>
	</form>
</body>

</html>

JoinProc.jsp

<%@page import="bean.MemberDAO"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>JoinProc</title>
</head>
<body>

<%
	request.setCharacterEncoding("UTF-8");
	
	//복수 선택 값을 배열에 저장
	String []hobby = request.getParameterValues("hobby");
	String str = "";
	
	//배열에 있는 내용을 하나의 문자열로 저장
	for(int i=0; i<hobby.length; i++){
		str += hobby[i]+" ";
	}
%>

<jsp:useBean id="memBean" class="bean.MemberBean">
	<jsp:setProperty property="*" name="memBean"/>
</jsp:useBean>

<%
	memBean.setHobby(str);

	MemberDAO mdao = new MemberDAO();
	mdao.insertMember(memBean);
	
	//회원가입이 성공했다면 회원정보를 보여주는 페이지로 이동
	response.sendRedirect("MemberArray.jsp");
%>

<h1>오라클에 입력 완료</h1>

</body>
</html>

회원 등록 : DAO

public void insertMember(MemberBean memBean) {
    try {
        getCon();

        String sql = "insert into Mem values(?,?,?,?,?,?,?,?)";

        pstmt = con.prepareStatement(sql);

        //4. 바인드변수에 맞게 데이터를 맵핑
        pstmt.setString(1, memBean.getId());
        pstmt.setString(2, memBean.getPass1());
        pstmt.setString(3, memBean.getEmail());
        pstmt.setString(4, memBean.getTel());
        pstmt.setString(5, memBean.getHobby());
        pstmt.setString(6, memBean.getJob());
        pstmt.setString(7, memBean.getAge());
        pstmt.setString(8, memBean.getInfo());

        pstmt.executeUpdate();
        con.close();	//자원반납

    } catch (Exception e) {
        e.printStackTrace();
    }
}

회원가입 성공 후 전체 회원목록 출력 페이지로 이동 - MemberArray.jsp

<%@page import="bean.*"%>
<%@page import="java.util.*"%>
<%@page import="bean.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>MemberArray</title>
</head>
<body>
<%
	request.setCharacterEncoding("UTF-8");

	//모든 회원 정보 가져옴
	MemberDAO mdao = new MemberDAO();
	//가변길이인 vector를 이용하여 데이터를 저장한 후
	Vector<MemberBean> vec = mdao.allSelectMember();
	
	MemberBean mb=null;
%>

	<h2>전체 회원 목록</h2>
	
	<table width="600" border="1">
	<tr height="20">
		<td width="150" align="center"> 아이디</td>
		<td width="150" align="center"> 이메일</td>
		<td width="150" align="center"> 관심분야</td>
		<td width="150" align="center"> 직업</td>
	</tr>
<%
	for(int i=0; i<vec.size(); i++){
		mb = vec.get(i);
%>		
	<tr height="50">
		<td width="350" align="center">
			<a href="MemberIn.jsp?id=<%=mb.getId() %>"><%=mb.getId() %></a>
		</td>
		<td width="350" align="center"><%=mb.getEmail() %></td>
		<td width="350" align="center"><%=mb.getHobby() %></td>
		<td width="350" align="center"><%=mb.getJob() %></td>
	</tr>
		
<%
	}
%>
	</table>

</body>
</html>

전체회원목록 조회 : DAO

public Vector<MemberBean> allSelectMember() {
    Vector<MemberBean> getlist = new Vector<>();

    try {
        getCon();
        String sql = "select * from mem";

        pstmt = con.prepareStatement(sql);
        rs = pstmt.executeQuery();
        //쿼리 실행 시킨 결과를 리턴해서 자바 객체에저장

        while(rs.next()) {	//저장된 데이터만큼까지 반복문을 돌림
            //컬럼으로 나눠진 데이터를 빈클래스에 저장해야함

            MemberBean bean = new MemberBean();

            bean.setId(rs.getString(1));
            bean.setPass1(rs.getString(2));
            bean.setEmail(rs.getString(3));
            bean.setTel(rs.getString(4));
            bean.setHobby(rs.getString(5));
            bean.setJob(rs.getString(6));
            bean.setAge(rs.getString(7));
            bean.setInfo(rs.getString(8));

            getlist.add(bean);
        }
        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

    return getlist;
}

회원 정보 수정

전체 회원 목록(MemberArray.jsp) 페이지에서 id 클릭 시,
그 회원의 정보 페이지 출력 - MemberIn.jsp

<%@page import="bean.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>MemberIn</title>
</head>
<body>
<%
	request.setCharacterEncoding("UTF-8");
	String id = request.getParameter("id");	// 받아온 id 값
	
	MemberDAO mdao = new MemberDAO();
	MemberBean mb = mdao.oneMember(id);
%>
	<h2><%=id %> 회원 정보</h2>
	<table width="600" border="1">
	<tr height="20">
		<td width="300" align="center"><%=mb.getId() %></td>
	</tr>
	<tr height="50">
		<td width="300" align="center">
			<%=mb.getPass1() %><br>
			<%=mb.getEmail() %><br>
			<%=mb.getTel() %><br>
			<%=mb.getHobby() %><br>
			<%=mb.getJob() %><br>
			<%=mb.getAge() %><br>
			<%=mb.getInfo() %>
		</td>
	</tr>
	
	<tr height="50">
		<td width="300" align="center">
			<button onclick="location.href='MemberUpdate.jsp?id=<%=mb.getId() %>'">회원정보수정</button>
			<button onclick="location.href='MemberDel.jsp?id=<%=mb.getId() %>'">회원탈퇴</button>
		</td>
	</tr>
	<tr height="50">
		<td width="300" align="center">
			<button onclick="location.href='MemberArray.jsp'">회원 전체목록</button>
			<button onclick="location.href='memberJoin.jsp'">회원가입</button>
		</td>
	</tr>
	</table>
	
</body>
</html>

회원의 정보를 수정할 수 있는 폼 - MemberUpdate.jsp
기존 정보를 출력하고, 새롭게 입력 가능하다.
DB의 기존 비밀번호와 일치하는지 검증하기 위해 비밀번호를 입력받는다

<%@page import="bean.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>MemberUpdate</title>
</head>
<body bgcolor="beige">
<%
	request.setCharacterEncoding("UTF-8");
	String id = request.getParameter("id");	// 받아온 id 값
	
	MemberDAO mdao = new MemberDAO();
	MemberBean mb = mdao.oneMember(id);
%>
	<h2><%=id %> 회원 정보 수정하기</h2>
	
	<form action="UpdateProc.jsp" method="post">
	<table width="500" border="1">
	<tr height="50">
		<td width="150" align="center"> 아이디</td>
		<td width="350" align="center"><%=mb.getId() %></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 이메일</td>
		<td width="350" align="center">
		<input type="email" name="email" value="<%=mb.getEmail() %>"></td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 전화번호</td>
		<td width="350" align="center">
			<input type="tel" name="tel" value="<%=mb.getTel() %>">
		</td>
	</tr>
		
	<tr height="50">
		<td width="150" align="center"> 당신의관심분야</td>
		<td width="350" align="center">
			<input type="text" name="hobby" value="<%=mb.getHobby() %>">   
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 당신의연령은</td>
		<td width="350" align="center">
			<input type="text" name="age" value="<%=mb.getAge() %>">  
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 하고싶은말</td>
		<td width="350" align="center">
			<textarea rows="5" cols="40" name="info" ><%=mb.getAge() %></textarea>
		</td>
	</tr>
	
	<tr height="50">
		<td width="150" align="center"> 패스워드</td>
		<td width="350" align="center">
			<input type="password" name="pass1">
		</td>
	</tr>
	
	<tr height="50">
		<td align="center" colspan="2">
			<input type="hidden" name="id" value="<%=mb.getId() %>">
			<input type="submit" value="회원 정보 수정">
		</td>
	</tr>
	</table>
	</form>
	
	<hr>
	<button onclick="location.href='MemberArray.jsp'">회원 전체 목록보기</button>
</body>
</html>

특정 회원(1명) 정보 조회 : DAO

public MemberBean oneMember(String id) {

    MemberBean bean = new MemberBean();

    try {
        getCon();
        String sql = "select * from mem where id= ?";

        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, id);

        rs = pstmt.executeQuery();

        if(rs.next()) {
            bean.setId(rs.getString(1));
            bean.setPass1(rs.getString(2));
            bean.setEmail(rs.getString(3));
            bean.setTel(rs.getString(4));
            bean.setHobby(rs.getString(5));
            bean.setJob(rs.getString(6));
            bean.setAge(rs.getString(7));
            bean.setInfo(rs.getString(8));
        }
        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

    return bean;
}

수정된 정보 업데이트 처리 - UpdateProc.jsp
입력받은 비번과 DB의 기존 비번이 일치하면 수정사항을 업데이트한다
수정 성공 시 회원전체목록으로 페이지가 이동된다

<%@page import="bean.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>UpdateProc</title>
</head>
<body>
<%
	request.setCharacterEncoding("UTF-8");
	String id = request.getParameter("id");
%>
<jsp:useBean id="mbean" class="bean.MemberBean">
	<jsp:setProperty property="*" name="mbean"/>
</jsp:useBean>
<%
	MemberDAO mdao = new MemberDAO();
	
	//DB에서 가져온 패스워드를 가져온다
	String pass = mdao.getPass(id);
	
	if( mbean.getPass1().equals(pass)){
		mdao.updateMember(mbean);
		response.sendRedirect("MemberArray.jsp");
		// DB의 비번과 입력한 비번이 같으면 mem 테이블 수정
	} else {
%>
<script type="text/javascript">
	alert("패스워드가 틀렸습니다.");
	history.go(-1);
</script>
<%
	}
%>

	<h2><%=mbean.getId() %> 회원의 정보 수정 하였습니다.</h2>
	<%=pass %> 와 입력한 비번:<%= mbean.getPass1() %>
	
</body>
</html>

받아온 id의 비밀번호 반환 코드 : dao

public String getPass(String id) {
    String pass="";
    try {
        getCon();

        String sql = "select pass1 from mem where id = ?";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, id);

        rs = pstmt.executeQuery();

        if(rs.next()) {
            pass = rs.getString(1);
        }

        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

    return pass;
}

회원정보 수정 : dao

public void updateMember(MemberBean mbean) {

    try {
        getCon();
        String sql = "update mem set "
                + "email = ?, tel=?, hobby=?, age=?, info=? "
                + "where id=?";

        pstmt = con.prepareStatement(sql);

        pstmt.setString(1, mbean.getEmail());
        pstmt.setString(2, mbean.getTel());
        pstmt.setString(3, mbean.getHobby());
        pstmt.setString(4, mbean.getAge());
        pstmt.setString(5, mbean.getInfo());
        pstmt.setString(6, mbean.getId());

        pstmt.executeUpdate();
        con.close();


    } catch (Exception e) {
        e.printStackTrace();
    }
}

회원탈퇴

회원탈퇴 버튼 클릭 시 이동되는 페이지 - MemberDel.jsp
id를 받아오고 비밀번호를 입력받는다

<%@page import="bean.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>MemberDel</title>
</head>
<body>
<%
	request.setCharacterEncoding("UTF-8");
%>
	<h2>회원삭제</h2>
	<form action="DeleteProc.jsp" method="post">
	<table>
		<tr>
			<td>아이디</td>
			<td><%=request.getParameter("id") %></td>
		</tr>
		<tr>
			<td>비밀번호</td>
			<td>
				<input type="password" name="pass1">
			</td>
		</tr>
		
		<tr>
			<td colspan="2">
				<input type="hidden" name="id" value="<%=request.getParameter("id")%>">
				<input type="submit" value="회원 삭제">
			</td>
		</tr>
	</table>
	</form>
	<hr>
	
	<button onclick="location.href='MemberArray.jsp'">전체 회원</button>
</body>
</html>

회원탈퇴 처리 - DeleteProc.jsp
입력받은 비번과 기본 비번이 동일하면 탈퇴처리를 하고 회원전체목록 페이지로 이동한다

<%@page import="bean.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>DeleteProc</title>
</head>
<body>
<%
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="mbean" class="bean.MemberBean">
	<jsp:setProperty name="mbean" property="*"/>
</jsp:useBean>
<%
	MemberDAO mdao = new MemberDAO();
	String pass = mdao.getPass(mbean.getId());	//기존(DB속) 비번
	
	if(mbean.getPass1().equals(pass)){	//입력한 비번과 기존 비번이 동일하면
		mdao.deleteMember(mbean.getId());
		response.sendRedirect("MemberArray.jsp");	//성공시 전체목록으로 이동
	} else {
%>
	<script type="text/javascript">
		alert("비밀번호가 틀렸습니다. 탈퇴 실패");
		history.go(-1);
	</script>
<%
	}
%>

</body>
</html>

회원 탈퇴 : dao

public void deleteMember(String id) {

    getCon();
    try {
        String sql = "delete from mem where id=?";

        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, id);

        pstmt.executeUpdate();
        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

회원정보 빈 - MemberBean.java

package bean;

public class MemberBean {
	
	private String id;
	private String pass1;
	private String pass2;
	private String email;
	private String tel;
	private String hobby;
	private String job;
	private String age;
	private String info;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPass1() {
		return pass1;
	}
	public void setPass1(String pass1) {
		this.pass1 = pass1;
	}
	public String getPass2() {
		return pass2;
	}
	public void setPass2(String pass2) {
		this.pass2 = pass2;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public String getHobby() {
		return hobby;
	}
	public void setHobby(String hobby) {
		this.hobby = hobby;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public String getInfo() {
		return info;
	}
	public void setInfo(String info) {
		this.info = info;
	}
	
}

DB 연결을 위한 DAO - MemberDAO.java

package bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class MemberDAO {
	
	String id = "아이디";
	String pw= "비번";
	String url="jdbc:oracle:thin:@localhost:1521/xepdb1";
	
	Connection con;				//DB에 접근할 수 있도록 하는 객체
	PreparedStatement pstmt;	//쿼리 실행 객체
	ResultSet rs;		//DB 테이블 결과를 리턴받아 자바에 저장해주는 객체
	
	public void getCon() {
		try {
			//1. 해당 데이터 베이스를 사용한다고 선언(로딩)
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.해당 데이터 베이스에 접속
			con =  DriverManager.getConnection(url, id, pw);
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	//회원 가입
	public void insertMember(MemberBean memBean) {
		try {
			getCon();
			
			String sql = "insert into Mem values(?,?,?,?,?,?,?,?)";
			
			pstmt = con.prepareStatement(sql);
			
			//4. 바인드변수에 맞게 데이터를 맵핑
			pstmt.setString(1, memBean.getId());
			pstmt.setString(2, memBean.getPass1());
			pstmt.setString(3, memBean.getEmail());
			pstmt.setString(4, memBean.getTel());
			pstmt.setString(5, memBean.getHobby());
			pstmt.setString(6, memBean.getJob());
			pstmt.setString(7, memBean.getAge());
			pstmt.setString(8, memBean.getInfo());
			
			pstmt.executeUpdate();
			con.close();	//자원반납
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	//전체 회원 목록 출력
	public Vector<MemberBean> allSelectMember() {
		Vector<MemberBean> getlist = new Vector<>();
		
		try {
			getCon();
			String sql = "select * from mem";
			
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			//쿼리 실행 시킨 결과를 리턴해서 자바 객체에저장

			while(rs.next()) {	//저장된 데이터만큼까지 반복문을 돌림
				//컬럼으로 나눠진 데이터를 빈클래스에 저장해야함
				
				MemberBean bean = new MemberBean();
				
				bean.setId(rs.getString(1));
				bean.setPass1(rs.getString(2));
				bean.setEmail(rs.getString(3));
				bean.setTel(rs.getString(4));
				bean.setHobby(rs.getString(5));
				bean.setJob(rs.getString(6));
				bean.setAge(rs.getString(7));
				bean.setInfo(rs.getString(8));
				
				getlist.add(bean);
			}
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return getlist;
	}
	
	
	// 특정 회원 정보 조회
	public MemberBean oneMember(String id) {

		MemberBean bean = new MemberBean();
		
		try {
			getCon();
			String sql = "select * from mem where id= ?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				bean.setId(rs.getString(1));
				bean.setPass1(rs.getString(2));
				bean.setEmail(rs.getString(3));
				bean.setTel(rs.getString(4));
				bean.setHobby(rs.getString(5));
				bean.setJob(rs.getString(6));
				bean.setAge(rs.getString(7));
				bean.setInfo(rs.getString(8));
			}
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return bean;
	}
	
	
	//받아온 id의 패스워드 반환
	public String getPass(String id) {
		String pass="";
		try {
			getCon();
			
			String sql = "select pass1 from mem where id = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				pass = rs.getString(1);
			}
			
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return pass;
	}
	
	
	//회원정보 수정하기
	public void updateMember(MemberBean mbean) {
		
		try {
			getCon();
			String sql = "update mem set "
					+ "email = ?, tel=?, hobby=?, age=?, info=? "
					+ "where id=?";

			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, mbean.getEmail());
			pstmt.setString(2, mbean.getTel());
			pstmt.setString(3, mbean.getHobby());
			pstmt.setString(4, mbean.getAge());
			pstmt.setString(5, mbean.getInfo());
			pstmt.setString(6, mbean.getId());
			
			pstmt.executeUpdate();
			con.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	//회원 탈퇴
	public void deleteMember(String id) {
		
		getCon();
		try {
			String sql = "delete from mem where id=?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			
			pstmt.executeUpdate();
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

728x90

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