내코드

JDBC를 이용한 회원가입 구현

백PM ㅣ 2022. 10. 11. 22:42

JDBC를 이용하여 회원정보를 받아 DB에 저장하는 웹페이지를 만들어 보았습니다.

DB 스키마는 다음과 같습니다.

 

로그인 페이지

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

	<form action="joinForm.jsp" method="post" name="to_join"></form>
	<form action="loginProcess.jsp" method="post" name="login" id="login">
	<table align="center" border="1">
		<tr>
			<td colspan="2"><center><b>로그인</b></center></td>
		</tr>
		<tr>
			<td>로그인 :</td>
			<td><input type="text" name="id" id="id"></td>
		</tr>
		<tr>
			<td>비밀번호 :</td>
			<td><input type="password" name="pw" id="pw"></td>
		</tr>
		<tr>
			<td colspan="2" align="center"><input type="button" value="로그인" onclick="check()">&nbsp;&nbsp;<input type="button" value="회원가입" onclick="javascript:to_join.submit()"></td>
		</tr>
	</table>
</form>
<script>
	function check(){
		try{
		form = document.getElementById("login");
		id = document.getElementById("id");
		pw = document.getElementById("pw");
		
		if( id.value == null || pw.value == null || id.value == "" || pw.value == ""){
			alert("양식을 다 작성해주세요");
			return false;
		}
		}catch(e){
			alert(e.message)
		}
		
		form.submit();
	}
</script>
</body>
</html>

 

로그인 처리 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<% 
	request.setCharacterEncoding("utf8");
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	
	Connection conn = null;
	String sql = null;
	ResultSet rs = null;
	sql="SELECT ID FROM ACCOUNT WHERE ID = '"+id+"' AND PW = '"+pw+"'";

	String location = "Main";
	
	try{
		Context init = new InitialContext();
		DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
		conn = ds.getConnection();
		PreparedStatement stmt=conn.prepareStatement(sql);
		rs = stmt.executeQuery();
	}catch(Exception e){
		e.printStackTrace();
	}
	
	if(!rs.next()){
		request.setAttribute("error","아이디 또는 비밀번호가 틀렸습니다");
		location = "loginForm";
	} else {
		session.setAttribute("id",id);
	}
	%>
	<form action="<%=location %>.jsp" method="post" id="progress">
			<input type="hidden" name="id" value="<%=id %>">
			<input type="hidden" name="pw" value="<%=pw %>">
	</form>
	<script>document.getElementById("progress").submit()</script>

 

회원가입 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<% 
	response.setCharacterEncoding("UTF-8"); 
	Exception e = (Exception)request.getAttribute("error");
%>
<meta charset="UTF-8">
<title>회원가입</title>
<script src="validationCheck.js">
</script>
</head>
<body vlink="blue" background="bonobono.jpeg" >
<form id="register" action="javaText_v.jsp" method="post">
	<table align="center" width="700" height="300" border="1" cellspacing="0">
		<tr>
			<td style="text-align:center;" colspan="5" bgcolor="skyblue"><B>회원 기본 정보</B></td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>아이디:</B></td>
			<td colspan="4">
				<input type="text" maxlength="12" id="id" name="id"></input>
				<a> 4~12자의 영문 대소문자와 숫자로만 입력</a>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>비밀번호:</B></td>
			<td colspan="4">
				<input type="password" minlength="4" maxlength="12" id="pw" name="pw"></input>
				<a> 4~12자의 영문 대소문자와 숫자로만 입력</a>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>비밀번호확인:</B></td>
			<td colspan="4">
				<input type="password" minlength="4" maxlength="12" id="pw_config" ></input>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>메일주소:</B></td>
			<td colspan="4">
				<input type="text" id="email" name="email"></input>
				<a>예) id@domain.com</a>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>이름:</B></td>
			<td colspan="4">
				<input type="text" id="name" name="name"></input>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" colspan="5" bgcolor="skyblue"><B>개인 신상 정보</B></td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>전화번호:</B></td>
			<td colspan="4">
				<input type="text" minlength="4" maxlength="13" id="phone" name="tel"></input>
				<a>예) 010-1234-5678</a>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>생일:</B></td>
			<td colspan="4">
				<input type="text" minlength="4" id="year" name="year"></input> <a>년</a>
				<select name="month">
					<option value="Jan">1</option>
					<option value="Feb">2</option>
					<option value="Mar">3</option>
					<option value="Apr">4</option>
					<option value="May">5</option>
					<option value="Jun">6</option>
					<option value="Jul">7</option>
					<option value="Aug">8</option>
					<option value="Sep">9</option>
					<option value="Oct">10</option>
					<option value="Nov">11</option>
					<option value="Dec">12</option>
				</select> <a>월</a>
				<select name="day">
					<option value="1">1</option>
					<option value="2">2</option>
					<option value="3">3</option>
					<option value="4">4</option>
					<option value="5">5</option>
					<option value="6">6</option>
					<option value="7">7</option>
					<option value="8">8</option>
					<option value="9">9</option>
					<option value="10">10</option>
					<option value="11">11</option>
					<option value="12">12</option>
					<option value="13">13</option>
					<option value="14">14</option>
					<option value="15">15</option>
					<option value="16">16</option>
					<option value="17">17</option>
					<option value="18">18</option>
					<option value="19">19</option>
					<option value="20">20</option>
					<option value="21">21</option>
					<option value="22">22</option>
					<option value="23">23</option>
					<option value="24">24</option>
					<option value="25">25</option>
					<option value="26">26</option>
					<option value="27">27</option>
					<option value="28">28</option>
					<option value="29">29</option>
					<option value="30">30</option>
					<option value="31">31</option>
				</select><a>일</a>
			</td>
		</tr>
		<tr>
			<td style="text-align:center;" bgcolor="#D8D8D8"><B>관심분야:</B></td>
			<td colspan="4">
				<input type="checkbox" name="hobby" value="컴퓨터">컴퓨터
				<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>
			<td style="text-align:center;" bgcolor="#D8D8D8" rowspan="3"><B>자기소개:</B></td>
			<td rowspan="3" colspan="4">
				<textarea id="self_intro" rows="3" cols="30" name="selfIntro"></textarea>
			</td>
		</tr>
		<tr><td></td></tr>
		<tr><td></td></tr>
	</table>
	<table align = "center">
		<tr>
			<td>
				<input type="button" value="회원 가입" onclick="formTest()"></input>
				<input type="reset" value="다시 입력"></input>
			</td>
		</tr>
	</table>
	<%
		if(e != null){
			out.println("<a>회원등록 중 문제가 발생했습니다.</a><p>"+e.getMessage()+"</p>");
		}
	%>
</form>
	<audio controls>
  		<source src="https://vmlbox.com/wp-content/uploads/2022/04/Charlie-Puth-Light-Switch-Vmlboxcom.mp3" type="audio/ogg">
	</audio>
</body>
</html>

 

회원가입 처리 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<form action="joinForm.jsp" method="post" id="retreat">
		<input type="hidden" name="err"></form>
<% 
	request.setCharacterEncoding("utf8");
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	String name = request.getParameter("name");
	String email = request.getParameter("email");
	String regi_num = request.getParameter("regi_num");
	String self_intro = request.getParameter("self_intro");
	String year = request.getParameter("year");
	String month = request.getParameter("month");
	String day = request.getParameter("day");
	String[] hobbys = null;
	hobbys = request.getParameterValues("hobby");
	String birthday = year+"/"+month+"/"+day;
	String hobby = "";
	if(hobbys != null){
		for (String i : hobbys){
			hobby += i+" ";
		}
	}

	Connection conn = null;
	String sql = null;
	sql="INSERT INTO account(ID,PW,EMAIL,NAME,REGI_NUM,BIRTHDAY,HOBBY,INTRO) VALUES(?,?,?,?,?,?,?,?)";
	PreparedStatement pstmt = null;
	try{
		Context init = new InitialContext();
		DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
		conn = ds.getConnection();
		
		conn.setAutoCommit(false);

		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,id);
		pstmt.setString(2,pw);
		pstmt.setString(3,email);
		pstmt.setString(4,name);
		pstmt.setString(5,regi_num);
		pstmt.setString(6,birthday);
		pstmt.setString(7,hobby);
		pstmt.setString(8,self_intro);
		pstmt.executeUpdate();
	}catch(SQLIntegrityConstraintViolationException e){
		%><script>alert("이미 존재하는 아이디입니다");
		document.getElementById("retreat").submit();</script><% 
	}catch(Exception e){
		conn.rollback();
		pstmt.close();
		conn.setAutoCommit(true);
		%>
		
		<script>
			alert("회원등록에 실패했습니다.\n<%=e.getMessage()%>");
			document.getElementById("retreat").submit();
		</script>
		<%
	}
	%>
	<script>
			alert(<%=month %>);
	</script>
	<%
	conn.commit();
	pstmt.close();
	conn.setAutoCommit(true);
%>

<form action="loginForm.jsp" method="post" id="success"></form>
<script>document.getElementById("success").submit();</script>

 

로그인 시 접근할 메인 페이지

일반회원 로그인 시 화면

어드민 로그인 시 화면

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<jsp:include page="sessionCheck.jsp" />
<html>
<head>
<%
	String id = (String)session.getAttribute("id");
%>
<meta charset="UTF-8">
<title>메인 페이지</title>
</head>
<body>
<h1><%=id %>님 로그인</h1>
<hr>
<table width="300">
	<tr>
		<td width="150">
		<%
			if(id.equals("admin")){
		%>
		<a href="Member_list.jsp">회원목록</a>
		<%} %>
		</td>
		<td width="150">
			<a href="#" onclick="logout()">로그아웃</a>
			<form action="loginForm.jsp" method="post" id="logout"></form>
		</td>
	</tr>
</table>
<script>
	function logout(){
		sessionStorage.clear();
		document.getElementById("logout").submit();
	}
</script>
</body>
</html>

 

회원목록 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:include page="sessionCheck.jsp" />
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<html>
<head><title>회원 목록</title></head>
<body>
<center><h1><b>회원 목록<b></h1></center>
<% 
	request.setCharacterEncoding("utf8");
	Connection conn = null;
	String sql = null;
	ResultSet rs = null;
	sql="SELECT ID,NAME FROM ACCOUNT";
	
	try{
		Context init = new InitialContext();
		DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
		conn = ds.getConnection();
		PreparedStatement stmt=conn.prepareStatement(sql);
		rs = stmt.executeQuery();
	}catch(Exception e){
		e.printStackTrace();
	}
	
	out.println("<table align=\"center\" width=\"300\" border=\"1\">");
	int mem_num = 0;
	while(rs.next()){
		String id = rs.getString(1);
		String name = rs.getString(2);
		%>
		<tr>
			<td width="50"><%=++mem_num %></td>
			<form><input type="hidden" value="<%=id%>" id=""></form>
			<td width="200" align="center"><a href="#" onclick="look_info('<%=id%>')"><%=id %></a>&nbsp;&nbsp;<a><%=name %></a></td>
			<td width="50"><input type="button" value="삭제" onclick="delete_acnt('<%=id%>')"/></td>
		</tr>
		<%
	}
	out.println("</table>");
	%>
	<center><a href="Main.jsp">메인으로</a></center>
<form action="Member_info.jsp" method="post" id="look_info"><input type="hidden" name="id"></form>
<form action="Member_delete.jsp" method="post" id="delete_acnt"><input type="hidden" name="id"></form>
<script>
	function look_info(id){
		form = document.getElementById("look_info");
		form.id.value=id;
		form.submit();
	}
	function delete_acnt(id){
		form = document.getElementById("delete_acnt");
		form.id.value=id;
		form.submit();
	}
</script>
</body>
</html>

 

회원정보 조회 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:include page="sessionCheck.jsp" />
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<!DOCTYPE html>
<html>
<head>
<%
	String id = request.getParameter("id");

	request.setCharacterEncoding("utf8");
	Connection conn = null;
	String sql = null;
	ResultSet rs = null;
	sql="SELECT * FROM ACCOUNT WHERE ID = '"+id+"'";
	
	try{
		Context init = new InitialContext();
		DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
		conn = ds.getConnection();
		PreparedStatement stmt=conn.prepareStatement(sql);
		rs = stmt.executeQuery();
	}catch(Exception e){
		e.printStackTrace();
	}
	
	if(rs.next()){
%>
<meta charset="UTF-8">
<title>회원정보 열람</title>
</head>
<body>
	<table align="center" border="1">
	<tr>
		<td >아이디</td>
		<td ><%=rs.getString("ID") %></td>
	</tr>
	<tr>
		<td >비밀번호</td>
		<td ><%=rs.getString("PW") %></td>
	</tr>
	<tr>
		<td >이름</td>
		<td ><%=rs.getString("NAME") %></td>
	</tr>
	<tr>
		<td >이메일</td>
		<td ><%=rs.getString("EMAIL") %></td>
	</tr>
	<tr>
		<td >주민등록번호</td>
		<td ><%=rs.getString("REGI_NUM") %></td>
	</tr>
	<tr>
		<td >생일</td>
		<td ><%=rs.getString("BIRTHDAY") %></td>
	</tr>
	<tr>
		<td >관심분야</td>
		<td ><%=rs.getString("HOBBY") %></td>
	</tr>
	<tr>
		<td >자기소개</td>
		<td ><%=rs.getString("INTRO") %></td>
	</tr>
	</table>
	<%} %>
	<a href="Member_list.jsp">회원목록으로</a>
</body>
</html>

 

회원 삭제 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:include page="sessionCheck.jsp" />
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
	String id = request.getParameter("id");

	request.setCharacterEncoding("utf8");
	Connection conn = null;
	String sql = null;
	ResultSet rs = null;
	sql="DELETE FROM ACCOUNT WHERE ID = '"+id+"'";
	
	try{
		Context init = new InitialContext();
		DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
		conn = ds.getConnection();
		PreparedStatement stmt=conn.prepareStatement(sql);
		stmt.executeUpdate();
	}catch(Exception e){
		e.printStackTrace();
		conn.rollback();
	}
	conn.commit();
%>
<form action="Member_list.jsp" id="to_list"></form>
<script>Document.getElementById("to_list").submit()</script>
저작자표시 (새창열림)

'내코드' 카테고리의 다른 글

MVC2 패턴 게시판에 로그인 기능 확장  (0) 2022.10.17
게시판 비즈니스 로직(MVC2 패턴)  (1) 2022.10.13
오라클과 JSP 연동 연습  (0) 2022.10.07
내코드 다른글

티스토리툴바