[JSP] JSP + JDBC : 회원가입, 로그인, 비밀번호 변경, 회원 탈퇴 페이지 만들기
05 Nov 2021 -
7 minute read
할 때마다 까먹는 JDBC 😭 JSP와 DB를 연동해서 회원가입, 로그인, 비밀번호 변경, 회원 탈퇴 + 회원 목록 조회 페이지를 만들어 보았다! 👊
메뉴 선택하기
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>메인</title>
<style>
#wrap {
padding: 20px;
margin: 20px auto;
width: 500px;
background-color: #EEEEEE;
text-align: center;
}
a {
text-decoration: none;
font-size: 20px;
font-weight: bold;
color: #172774;
background-color: #EEEEEE;
padding: 15px;
margin: 10px;
}
a:hover {
color: #EEEEEE;
background-color: #172774;
}
</style>
</head>
<body>
<div id="wrap">
<a href="Login.jsp">로그인</a>
<a href="Join.jsp">회원가입</a>
<a href="Select.jsp">회원목록조회</a>
</div>
</body>
</html>
MySQL DB
create database uMember;
use uMember;
create table member(
uid char(10) primary key,
upw char(10) not null,
uname char(10) not null,
addr char(20) not null,
phonenum char(20) not null,
email char(20) not null
);
desc member;
select * from member;
DB 연결을 위한 Connection
-
Connection.jsp
<%@page import="java.sql.DriverManager"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>DB연결 Connection</title> </head> <body> <% Connection conn = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/uMember?"; url += "useSSL=false&"; url += "serverTimezone=Asia/Seoul&"; url += "useUnicode=true&"; url += "characterEncoding=UTF-8&"; url += "allowPublicKeyRetrieval=true"; String id = "root"; String pw = "1234"; conn = DriverManager.getConnection(url, id, pw); } catch(ClassNotFoundException e) { out.println(e.getMessage()); } %> </body> </html>
회원가입
-
Join.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>회원가입</title> <style type="text/css"> h1 { margin: 20px auto; text-align: center; } form { margin: 0 auto; width: 400px; padding: 20px; border: 1px solid black; font-weight: bold; } </style> </head> <body> <h1>회원가입</h1> <form action="Insert.jsp" method="post"> ✅ 아이디 : <input type="text" name="uid"> <br><br> ✅ 비밀번호 : <input type="password" name="upw"> <br><br> ✅ 이름 : <input type="text" name="uname"> <br><br> ✅ 주소 : <input type="text" name="addr"> <br><br> ✅ 핸드폰번호 : <input type="text" name="phonenum"> <br><br> ✅ 이메일 : <input type="email" name="email"> <br><br> <input type="submit" value="가입하기"> </form> </body> </html> -
Insert.jsp
<%@page import="java.sql.SQLException"%> <%@page import="java.sql.PreparedStatement"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>가입완료</title> </head> <body> <%@ include file="Connection.jsp" %> <% request.setCharacterEncoding("UTF-8"); String uid = request.getParameter("uid"); String upw = request.getParameter("upw"); String uname = request.getParameter("uname"); String addr = request.getParameter("addr"); String phonenum = request.getParameter("phonenum"); String email = request.getParameter("email"); PreparedStatement pstmt = null; try { String sql = "insert into member values (?, ?, ?, ?, ?, ?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, uid); pstmt.setString(2, upw); pstmt.setString(3, uname); pstmt.setString(4, addr); pstmt.setString(5, phonenum); pstmt.setString(6, email); int res = pstmt.executeUpdate(); if (res == 1){ out.println("<h1>가입이 완료되었습니다. 🤗</h1>"); out.println("<a href='Login.jsp'>로그인 화면으로 가기</a>"); } else { out.println("오류가 발생했습니다. 다시 입력해주세요."); } } catch(SQLException e) { out.println(e.getMessage()); } finally { if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); } %> </body> </html>
로그인
-
Login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>로그인</title> <style type="text/css"> h1 { margin: 20px auto; text-align: center; } form { margin: 0 auto; width: 400px; padding: 20px; border: 1px solid black; font-weight: bold; } </style> </head> <body> <h1>로그인</h1> <form action="LoginChk.jsp" method="post"> ✅ 아이디 : <input type="text" name="uid"> <br><br> ✅ 비밀번호 : <input type="password" name="upw"> <br><br> <input type="submit" value="로그인"> </form> </body> </html> -
LoginChk.jsp
<%@page import="java.sql.SQLException"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>로그인</title> </head> <body> <%@ include file="Connection.jsp" %> <% request.setCharacterEncoding("UTF-8"); String uid = request.getParameter("uid"); String upw = request.getParameter("upw"); PreparedStatement pstmt = null; ResultSet rs = null; try { String sql = "select upw from member where uid = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, uid); rs = pstmt.executeQuery(); if (rs.next()){ if (rs.getString(1).contentEquals(upw)){ out.println("<h1>" + uid + "님께서 로그인 하셨습니다. 😄</h1>"); out.println("<a href='Update.jsp'>비밀번호 수정</a>"); out.println("<a href='DeleteForm.jsp'>회원 탈퇴</a>"); } else { out.println("<h1>비밀번호를 확인해주세요. 🤔</h1>"); } } else { out.println("<h1>아이디를 확인해주세요. 🤔</h1>"); } } catch(SQLException e){ out.println(e.getMessage()); } finally { if(pstmt != null) pstmt.close(); if(rs != null) rs.close(); if(conn != null) conn.close(); } %> </body> </html>
비밀번호 변경
-
Update.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>회원정보 수정</title> <style type="text/css"> form { margin: 0 auto; width: 400px; padding: 20px; border: 1px solid black; font-weight: bold; } </style> </head> <body> <form action="UpdateInfo.jsp" method="post"> ✅ 아이디 : <input type="text" name="uid"> <br><br> ✅ 변경할 비밀번호 : <input type="password" name="upw"> <br><br> <input type="submit" value="수정하기"> </form> </body> </html> -
UpdateInfo.jsp
<%@page import="java.sql.Statement"%> <%@page import="java.sql.SQLException"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>회원 정보 수정</title> </head> <body> <%@ include file="Connection.jsp" %> <% request.setCharacterEncoding("UTF-8"); String uid = request.getParameter("uid"); String upw = request.getParameter("upw"); Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; try { stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "select upw from member where uid = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, uid); rs = pstmt.executeQuery(); if(rs.next()) { if(pstmt != null) pstmt.close(); String sqlpw = "update member set upw = ? where uid = ?"; pstmt = conn.prepareStatement(sqlpw); pstmt.setString(1, upw); pstmt.setString(2, uid); pstmt.executeUpdate(); out.println("<h1>" + uid + "님의 비밀번호가 변경되었습니다. 👍</h1>"); out.println("<a href='Login.jsp'>다시 로그인하기</a>"); } else { out.println("<h1>아이디와 비밀번호를 다시 확인해주세요.</h1>"); } } catch(SQLException e){ out.println(e.getMessage()); } finally { if(pstmt != null) pstmt.close(); if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); } %> </body> </html>
회원 탈퇴
-
DeleteForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>탈퇴</title> <style> div#wrap { margin: 0 auto; border: 1px solid #000; padding: 20px; width: 500px; } </style> </head> <body> <div id="wrap"> <h3>탈퇴를 위해 아이디와 비밀번호를 다시 입력해주세요. 😥</h3> <form action="Delete.jsp" method="post"> ✅ 아이디 : <input type="text" name="uid"> <br><br> ✅ 비밀번호 : <input type="password" name="upw"> <br><br> <input type="submit" value="탈퇴하기"> </form> </div> </body> </html> -
Delete.jsp
<%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.Statement"%> <%@page import="java.sql.SQLException"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>회원탈퇴</title> </head> <body> <%@ include file="Connection.jsp" %> <% request.setCharacterEncoding("UTF-8"); String uid = request.getParameter("uid"); String upw = request.getParameter("upw"); Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; try { stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "select upw from member where uid = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, uid); rs = pstmt.executeQuery(); if(rs.next()) { if(pstmt != null) pstmt.close(); String sqldelete = "delete from member where uid = ? and upw = ?"; pstmt = conn.prepareStatement(sqldelete); pstmt.setString(1, uid); pstmt.setString(2, upw); pstmt.executeUpdate(); out.println("정상적으로 탈퇴가 완료되었습니다."); } else { out.println("아이디와 비밀번호를 다시 확인해주세요."); } } catch(SQLException e){ out.println(e.getMessage()); } finally { if(pstmt != null) pstmt.close(); if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); } %> </body> </html>
회원 목록 조회
-
Select.jsp
<%@page import="java.sql.SQLException"%> <%@page import="java.sql.Statement"%> <%@page import="java.sql.ResultSet"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html lang="ko"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>회원 조회</title> <style type="text/css"> table { border-collapse: collapse; } table tr th, table tr td{ width: 100px; height: 15px; border: 1px solid black; padding: 10px; } </style> </head> <body> <%@ include file="Connection.jsp" %> <table> <tr> <th>아이디</th> <th>이름</th> <th>주소</th> <th>핸드폰번호</th> <th>이메일</th> </tr> <% ResultSet rs = null; Statement stmt = null; try { stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "select uid, uname, addr, phonenum, email from member"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ String uid = rs.getString("uid"); String uname = rs.getString("uname"); String addr = rs.getString("addr"); String phonenum = rs.getString("phonenum"); String email = rs.getString("email"); %> <tr> <td><%=uid %></td> <td><%=uname %></td> <td><%=addr %></td> <td><%=phonenum %></td> <td><%=email %></td> </tr> <% } } catch(SQLException e){ out.println(e.getMessage()); } finally { rs.close(); stmt.close(); conn.close(); } %> </table> </body> </html>