JSP | 웹스토어 DB연동 로그인, 회원가입 페이지 예제 ☆ | JSTL sql태그 | 연속 로그인 실패 처리
웹스토어 로그인, 회원가입 페이지 예제 DB 만들기 member.sql use webstoredb; drop table if exists member; create table member( id varchar(10) not null, password varchar(10) not null, name varchar(10)..
kjh95.tistory.com
회원가입 페이지
addMember.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 가입 - Webstore</title>
<link rel="stylesheet" href="../resources/css/bootstrap.min.css">
</head>
<body>
<jsp:include page="../menu.jsp"/>
<div class="jumbotron">
<div class="container">
<h1 class="display-3">회원 가입</h1>
</div>
</div>
<div class="container">
<form action="processAddMember.jsp" name="newMember" class="form horizontal" method="post"
onsubmit="return checkForm()">
<div class="form-group row">
<label class="col-sm-2">아이디</label>
<div class="col-sm-3">
<input name="id" type="text" class="form-control" placeholder="아이디">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호</label>
<div class="col-sm-3">
<input name="password" type="password" class="form-control" placeholder="비밀번호">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호 확인</label>
<div class="col-sm-3">
<input name="password_confirm" type="password" class="form-control" placeholder="비밀번호 확인">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이름</label>
<div class="col-sm-3">
<input name="name" type="text" class="form-control" placeholder="이름">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">성별</label>
<div class="col-sm-10">
<input name="gender" type="radio" value="남"/>남
<input name="gender" type="radio" value="여"/>여
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">생일</label>
<div class="col-sm-4">
<input name="birthyy" type="text" maxlength="4" placeholder="년도(4자리)" size="6">
<select name="birthmm">
<option value="">월</option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
</select>
<input type="text" name="birthdd" maxlength="2" placeholder="일" size="4">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이메일</label>
<div class="col-sm-10">
<input name="mail1" type="text" maxlength="50">@
<select name="mail2">
<option>ggmail.com</option>
<option>nnate.com</option>
<option>kkakao.com</option>
<option>nnaver.com</option>
</select>
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">전화번호</label>
<div class="col-sm-5">
<input name="phone" type="text" class="form-control" placeholder="전화번호(-생략)">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">주소</label>
<div class="col-sm-5">
<input name="address" type="text" class="form-control" placeholder="주소">
</div>
</div>
<div class="form-group row">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" value="가입" >
<input type="reset" class="btn btn-primary" value="취소" >
</div>
</div>
</form>
</div>
<script type="text/javascript">
function checkForm() {
if(!document.newMember.id.value) {
alert("아이디를 입력하세요!");
return false;
}
if(!document.newMember.password.value) {
alert("비밀번호를 입력하세요!");
return false;
}
if(document.newMember.password.value != document.newMember.password_confirm.value) {
alert("비밀번호를 동일하게 입력하세요!");
return false;
}
if(!document.newMember.name.value) {
alert("이름을 입력하세요!");
return false;
}
}
</script>
</body>
</html>
회원가입 처리
processAddMember.jsp
<%@page import="java.util.Date"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- jstl을 사용하기 위해서 taglib라는 지시자 사용 -->
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
String password = request.getParameter("password");
String name = request.getParameter("name");
String gender = request.getParameter("gender");
String year = request.getParameter("birthyy");
String month = request.getParameter("birthmm");
String day = request.getParameter("birthdd");
String birth = year +"/"+ month +"/"+ day; /* 생년월일을 3개로 나눠서 받고 다시 문자열로 합침 */
String mail1 = request.getParameter("mail1");
String mail2 = request.getParameter("mail2");
String mail = mail1 +"@"+ mail2; /* 메일도 2개로 나눠서 받고 다시 문자열로 합침 */
String phone = request.getParameter("phone");
String address = request.getParameter("address");
SimpleDateFormat sDFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String registDay = sDFormat.format(new Date()); /* 가입버튼 누를때 그 시점으로 저장함 */
%>
<!-- jstl 태그라이브러리의 sql태그를 이용하여 DB에 접속 -->
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webstoredb?serverTimezon=UTC"
driver="com.mysql.cj.jdbc.Driver"
user="root"
password="7496"/>
<!-- jstl태그라이브러리 sql태그를 이용하여 DB에 데이터를 입력
update는 수정, 입력, 삭제까지 처리함 -->
<sql:update dataSource="${dataSource }" var="resultSet">
insert into member values(?, ?, ?, ?, ?, ?, ?, ?, ?);
<sql:param value="<%=id %>"/>
<sql:param value="<%=password %>"/>
<sql:param value="<%=name %>"/>
<sql:param value="<%=gender %>"/>
<sql:param value="<%=birth %>"/>
<sql:param value="<%=mail %>"/>
<sql:param value="<%=phone %>"/>
<sql:param value="<%=address %>"/>
<sql:param value="<%=registDay %>"/>
</sql:update>
<c:if test="${resultSet >= 1}">
<c:redirect url="resultMember.jsp?msg=1"/>
</c:if>
로그아웃 기능
logoutMember.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
session.invalidate(); //세션 삭제하면 로그아웃 됨.
response.sendRedirect("resultMember.jsp?msg=3");
%>
회원 수정 페이지
select 태그에 선택한 값은 script 이용해서 선택한 option의 value를 가져온다.
UpdateMember.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
String sessionId = (String)session.getAttribute("sessionId");
%>
<!-- jstl 태그라이브러리의 sql태그를 이용하여 DB에 접속 -->
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webstoredb?serverTimezone=UTC"
driver="com.mysql.cj.jdbc.Driver"
user="root"
password="7496"/>
<!-- sql쿼리문 실행. executeQuery()기능과 유사 -->
<sql:query dataSource="${dataSource}" var="resultSet">
select * from member where id = ?
<sql:param value="<%=sessionId %>"/>
</sql:query>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원수정 - Webstore</title>
<link rel="stylesheet" href="../resources/css/bootstrap.min.css">
</head>
<body onload="init()">
<!-- onload속성 : 윈도우객체 뿐만 아니라, 객체가 다 로드되었을 떄 사용하는 속성 -->
<jsp:include page="../menu.jsp"/>
<div class="jumbotron">
<div class="container">
<h1 class="display-3">회원수정</h1>
</div>
</div>
<!-- db에서 String타입으로 되어있는 데이터를 @를 기준으로 split() 사용해서 값을 잘라냄 -->
<c:forEach var="row" items="${resultSet.rows}">
<c:set var="mail" value="${row.mail}"/>
<c:set var="mail1" value="${mail.split('@')[0]}"/>
<c:set var="mail2" value="${mail.split('@')[1]}"/>
<c:set var="birth" value="${row.birth}"/>
<c:set var="year" value="${birth.split('/')[0]}"/>
<c:set var="month" value="${birth.split('/')[1]}"/>
<c:set var="day" value="${birth.split('/')[2]}"/>
<c:set var="gender" value="${row.gender}"/>
<div class="container">
<form action="processUpdateMember.jsp" name="newMember" class="form horizontal" method="post"
onsubmit="return checkForm()">
<div class="form-group row">
<label class="col-sm-2">아이디</label>
<div class="col-sm-3">
<input name="id" type="text" class="form-control" placeholder="아이디"
value="<c:out value='${row.id }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호</label>
<div class="col-sm-3">
<input name="password" type="password" class="form-control" placeholder="비밀번호"
value="<c:out value='${row.password }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호 확인</label>
<div class="col-sm-3">
<input name="password_confirm" type="password" class="form-control" placeholder="비밀번호 확인">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이름</label>
<div class="col-sm-3">
<input name="name" type="text" class="form-control" placeholder="이름"
value="<c:out value='${row.name }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">성별</label>
<div class="col-sm-10">
<input name="gender" type="radio"
value="남" <c:if test="${gender.equals('남') }"> <c:out value="checked" /> </c:if> /> 남
<input name="gender" type="radio"
value="여" <c:if test="${gender.equals('여') }"> <c:out value="checked" /> </c:if> />여
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">생일</label>
<div class="col-sm-4">
<input name="birthyy" type="text" maxlength="4" placeholder="년도(4자리)" size="6"
value="${year }">
<select name="birthmm" id="birthmm">
<option value="">월</option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
</select>
<input type="text" name="birthdd" maxlength="2" placeholder="일" size="4" value="${day}" >
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이메일</label>
<div class="col-sm-10">
<input name="mail1" type="text" maxlength="50" value="${mail1 }" >@
<select name="mail2" id="mail2">
<option>ggmail.com</option>
<option>nnate.com</option>
<option>kkakao.com</option>
<option>nnaver.com</option>
</select>
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">전화번호</label>
<div class="col-sm-5">
<input name="phone" type="text" class="form-control" placeholder="전화번호(-생략)"
value="${row.phone }">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">주소</label>
<div class="col-sm-5">
<input name="address" type="text" class="form-control" placeholder="주소"
value="${row.address }">
</div>
</div>
<div class="form-group row">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" value="수정반영" >
<input type="button" class="btn btn-danger" value="회원탈퇴" >
</div>
</div>
</form>
</div>
</c:forEach>
<script type="text/javascript">
function init() {
setComboMailValue("${mail2}");
setComboBirthValue("${month}");
}
function setComboMailValue(val) {
//select태그에서 선택한 option의 value를 가져옴
var selectMail = document.getElementById("mail2");
//(DB)매개변수로 넘어온 val이라는 값과
//(jsp화면)select태그의 값이 동일하다면,
// 그부분이 select되었다고 true로 설정.
for(var i=0, j=selectMail.length; i<j; i++) {
if(selectMail.options[i].value == val) {
selectMail.options[i].selected = true;
break;
}
}
}
function setComboBirthValue(val) {
var selectBirth = document.getElementById("birthmm");
for(var i=0, j=selectBirth.length; i<j; i++) {
if(selectBirth.options[i].value == val) {
selectBirth.options[i].selected = true;
break;
}
}
}
function checkForm() {
if(!document.newMember.id.value) {
alert("아이디를 입력하세요!");
return false;
}
if(!document.newMember.password.value) {
alert("비밀번호를 입력하세요!");
return false;
}
if(document.newMember.password.value != document.newMember.password_confirm.value) {
alert("비밀번호를 동일하게 입력하세요!");
return false;
}
if(!document.newMember.name.value) {
alert("이름을 입력하세요!");
return false;
}
}
</script>
</body>
</html>
회원 수정 처리
processUpdateMember.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
String password = request.getParameter("password");
String name = request.getParameter("name");
String gender = request.getParameter("gender");
String year = request.getParameter("birthyy");
String month = request.getParameter("birthmm");
String day = request.getParameter("birthdd");
String birth = year +"/"+ month +"/"+ day; /* 생년월일을 3개로 나눠서 받고 다시 문자열로 합침 */
String mail1 = request.getParameter("mail1");
String mail2 = request.getParameter("mail2");
String mail = mail1 +"@"+ mail2; /* 메일도 2개로 나눠서 받고 다시 문자열로 합침 */
String phone = request.getParameter("phone");
String address = request.getParameter("address");
%>
<!-- jstl 태그라이브러리의 sql태그를 이용하여 DB에 접속 -->
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webstoredb?serverTimezone=UTC"
driver="com.mysql.cj.jdbc.Driver"
user="root"
password="7496"/>
<sql:update dataSource="${dataSource}" var="resultSet">
update member set password=?, name=?, gender=?, birth=?, mail=?, phone=?, address=? where id=?;
<sql:param value="<%=password %>"/>
<sql:param value="<%=name %>"/>
<sql:param value="<%=gender %>"/>
<sql:param value="<%=birth %>"/>
<sql:param value="<%=mail %>"/>
<sql:param value="<%=phone %>"/>
<sql:param value="<%=address %>"/>
<sql:param value="<%=id %>"/>
</sql:update>
<c:if test="${resultSet >= 1}">
<c:redirect url="resultMember.jsp?msg=0"></c:redirect>
</c:if> 
회원탈퇴 구현
js 추가 deleteMemberConfirm()
UpdateMember.jsp 수정
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
String sessionId = (String)session.getAttribute("sessionId");
%>
<!-- jstl 태그라이브러리의 sql태그를 이용하여 DB에 접속 -->
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webstoredb?serverTimezone=UTC"
driver="com.mysql.cj.jdbc.Driver"
user="root"
password="7496"/>
<!-- sql쿼리문 실행. executeQuery()기능과 유사 -->
<sql:query dataSource="${dataSource}" var="resultSet">
select * from member where id = ?
<sql:param value="<%=sessionId %>"/>
</sql:query>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원수정 - Webstore</title>
<link rel="stylesheet" href="../resources/css/bootstrap.min.css">
</head>
<body onload="init()">
<!-- onload속성 : 윈도우객체 뿐만 아니라, 객체가 다 로드되었을 떄 사용하는 속성 -->
<jsp:include page="../menu.jsp"/>
<div class="jumbotron">
<div class="container">
<h1 class="display-3">회원수정</h1>
</div>
</div>
<!-- db에서 String타입으로 되어있는 데이터를 @를 기준으로 split() 사용해서 값을 잘라냄 -->
<c:forEach var="row" items="${resultSet.rows}">
<c:set var="mail" value="${row.mail}"/>
<c:set var="mail1" value="${mail.split('@')[0]}"/>
<c:set var="mail2" value="${mail.split('@')[1]}"/>
<c:set var="birth" value="${row.birth}"/>
<c:set var="year" value="${birth.split('/')[0]}"/>
<c:set var="month" value="${birth.split('/')[1]}"/>
<c:set var="day" value="${birth.split('/')[2]}"/>
<c:set var="gender" value="${row.gender}"/>
<div class="container">
<form action="processUpdateMember.jsp" name="newMember" class="form horizontal" method="post"
onsubmit="return checkForm()">
<div class="form-group row">
<label class="col-sm-2">아이디</label>
<div class="col-sm-3">
<input name="id" type="text" class="form-control" placeholder="아이디"
value="<c:out value='${row.id }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호</label>
<div class="col-sm-3">
<input name="password" type="password" class="form-control" placeholder="비밀번호"
value="<c:out value='${row.password }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">비밀번호 확인</label>
<div class="col-sm-3">
<input name="password_confirm" type="password" class="form-control" placeholder="비밀번호 확인">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이름</label>
<div class="col-sm-3">
<input name="name" type="text" class="form-control" placeholder="이름"
value="<c:out value='${row.name }' />">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">성별</label>
<div class="col-sm-10">
<input name="gender" type="radio"
value="남" <c:if test="${gender.equals('남') }"> <c:out value="checked" /> </c:if> /> 남
<input name="gender" type="radio"
value="여" <c:if test="${gender.equals('여') }"> <c:out value="checked" /> </c:if> />여
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">생일</label>
<div class="col-sm-4">
<input name="birthyy" type="text" maxlength="4" placeholder="년도(4자리)" size="6"
value="${year }">
<select name="birthmm" id="birthmm">
<option value="">월</option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
</select>
<input type="text" name="birthdd" maxlength="2" placeholder="일" size="4" value="${day}" >
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">이메일</label>
<div class="col-sm-10">
<input name="mail1" type="text" maxlength="50" value="${mail1 }" >@
<select name="mail2" id="mail2">
<option>ggmail.com</option>
<option>nnate.com</option>
<option>kkakao.com</option>
<option>nnaver.com</option>
</select>
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">전화번호</label>
<div class="col-sm-5">
<input name="phone" type="text" class="form-control" placeholder="전화번호(-생략)"
value="${row.phone }">
</div>
</div>
<div class="form-group row">
<label class="col-sm-2">주소</label>
<div class="col-sm-5">
<input name="address" type="text" class="form-control" placeholder="주소"
value="${row.address }">
</div>
</div>
<div class="form-group row">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" value="수정반영" >
<input type="button" class="btn btn-danger" value="회원탈퇴"
onclick="return deleteMemberConfirm()">
</div>
</div>
</form>
</div>
</c:forEach>
<script type="text/javascript">
function init() {
setComboMailValue("${mail2}");
setComboBirthValue("${month}");
}
function setComboMailValue(val) {
//select태그에서 선택한 option의 value를 가져옴
var selectMail = document.getElementById("mail2");
//(DB)매개변수로 넘어온 val이라는 값과
//(jsp화면)select태그의 값이 동일하다면,
// 그부분이 select되었다고 true로 설정.
for(var i=0, j=selectMail.length; i<j; i++) {
if(selectMail.options[i].value == val) {
selectMail.options[i].selected = true;
break;
}
}
}
function setComboBirthValue(val) {
var selectBirth = document.getElementById("birthmm");
for(var i=0, j=selectBirth.length; i<j; i++) {
if(selectBirth.options[i].value == val) {
selectBirth.options[i].selected = true;
break;
}
}
}
function checkForm() {
if(!document.newMember.id.value) {
alert("아이디를 입력하세요!");
return false;
}
if(!document.newMember.password.value) {
alert("비밀번호를 입력하세요!");
return false;
}
if(document.newMember.password.value != document.newMember.password_confirm.value) {
alert("비밀번호를 동일하게 입력하세요!");
return false;
}
if(!document.newMember.name.value) {
alert("이름을 입력하세요!");
return false;
}
}
function deleteMemberConfirm() {
var result = confirm('정말 회원탈퇴를 하시겠습니까?');
if(result) {
location.href = "deleteMember.jsp";
alert("탈퇴되었습니다.");
}
else {
alert("취소되었습니다.");
return;
}
}
</script>
</body>
</html>
삭제 처리
deleteMember.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%
String sessionId = (String)session.getAttribute("sessionId");
%>
<!-- DB접속 -->
<!-- jstl 태그라이브러리의 sql태그를 이용하여 DB에 접속 -->
<sql:setDataSource var="dataSource"
url="jdbc:mysql://localhost:3306/webstoredb?serverTimezone=UTC"
driver="com.mysql.cj.jdbc.Driver"
user="root"
password="7496"/>
<!-- 쿼리 실행 -->
<sql:update dataSource="${dataSource}" var="resultSet">
delete from member where id = ?
<sql:param value="<%=sessionId %>" />
</sql:update>
<!-- 페이지 이동 -->
<c:if test="${resultSet >= 1 }">
<c:import url="logoutMember.jsp" />
<c:redirect url="resultMember.jsp" />
</c:if>
로그아웃 확인됨
drive.google.com/file/d/1ecdtoWUYQRgcgorvtJWdUNODegJITWnS/view?usp=sharing
Google Drive - 모든 파일을 한 곳에서
하나의 계정으로 모든 Google 서비스를 Google Drive로 이동하려면 로그인하세요.
accounts.google.com
WebStore(c).zip
