Notice
Recent Posts
Recent Comments
Link
«   2024/11   »
일 월 화 수 목 금 토
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
Tags
more
Archives
Today
Total
관리 메뉴

개발자입니다

IT BANK) JSP 15일차 - JSP(회원관리 페이지(SQL)2) 본문

JSP/IT BANK - JSP

IT BANK) JSP 15일차 - JSP(회원관리 페이지(SQL)2)

끈기JK 2022. 9. 27. 17:08

220927

 

 

한번만 잘 해두면 그대로 쓸수 있다.

 

-session_quiz

session_quiz 폴더 생성

 

-SQL DB 생성

SQL> CREATE TABLE session_quiz(
  2  id varchar2(10),
  3  pw varchar2(10),
  4  username varchar2(15),
  5  address varchar2(20),
  6  mobile varchar2(15)
  7  );

SQL> INSERT INTO session_quiz VALUES('admin', '1234', '관리자', '서울시', '010-1234-1234');
SQL> commit;
INSERT INTO session_quiz VALUES('user1', '1111', '유저일', '서울시', '010-1111-1111');
INSERT INTO session_quiz VALUES('user2', '2222', '유저이', '서울시', '010-2222-2222');
INSERT INTO session_quiz VALUES('user3', '3333', '유저삼', '서울시', '010-3333-3333');
INSERT INTO session_quiz VALUES('user4', '4444', '유저사', '서울시', '010-4444-4444');
INSERT INTO session_quiz VALUES('user5', '5555', '유저오', '서울시', '010-5555-5555');
INSERT INTO session_quiz VALUES('user6', '6666', '유저육', '서울시', '010-6666-6666');
INSERT INTO session_quiz VALUES('user7', '7777', '유저칠', '서울시', '010-7777-7777');
INSERT INTO session_quiz VALUES('user8', '8888', '유저팔', '서울시', '010-8888-8888');
INSERT INTO session_quiz VALUES('user9', '9999', '유저구', '서울시', '010-9999-9999');

 

-HTML 구성

// header.jsp
<style type="text/css">
	a {text-decoration: none; color:black;}
	ul {padding: 20px;}
	ul li {display: inline; padding: 15px;}
	.main_div{height:150px;}
</style>

<div align="center">
	<h1>CARE LAB</h1>
</div>

<div align="right">
	<hr>
	<ul>
		<li><a href="index.jsp">HOME</a></li>
		<% if(session.getAttribute("id") == null){%>
			<li><a href="register.jsp">Register</a></li>
			<li><a href="login.jsp">Login</a></li>
		<%}else{ %>
			<li><a href="memberInfo.jsp">MemberInfo</a></li>
			<li><a href="logout.jsp">Logout</a></li>
			<li><a href="boardForm.jsp">Board</a></li>
		<%} %>
	</ul>
	<hr>
</div>
// main.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
</head>
<body>
	<div align="center" class="main_div">더할 나위 없다.</div>
</body>
</html>
// footer.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>footer</title>
</head>
<body>
	<div align="center">
		<hr>
		Copyright CARE Lab. ALL rights reversed.
	</div>
</body>
</html>
// index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>index</title>
</head>
<body>
	<%@ include file="/session_quiz/header.jsp" %>
	<%@ include file="/session_quiz/main.jsp" %>
	<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>

 

HTMLElement.focus() : 해당 Element에 커서 입력 대기 상태가 된다.

// check.js
function allCheck(){
	id = document.getElementById('id').value;
	pw = document.getElementById('pw').value;
	confirm = document.getElementById('confirm').value;
	userName = document.getElementById('userName').value;
	
	if(id == ""){
		alert('아이디는 필수 항목입니다.');
	}else if(pw == ""){
		alert('비밀번호는 필수 항목입니다.');
	}else if(confirm == ""){
		alert('비밀번호 확인은 필수 항목입니다.');
	}else if(userName == ""){
		alert('이름은 필수 항목입니다.');
	}else{
		document.getElementById('f').submit();
	}
}
function pwCheck(){
	pw = document.getElementById('pw');
	confirm = document.getElementById('confirm');
	if(pw.value == confirm.value){
		document.getElementById('label').innerHTML = '일치';
	}else {
		document.getElementById('label').innerHTML = '불일치';
		pw.value="";
		confirm.value="";
		pw.focus();
	}
}
function loginCheck(){
	id = document.getElementById('id').value;
	pw = document.getElementById('pw').value;
	
	if(id == ""){
		alert('아이디는 필수 항목입니다.');
	}else if(pw == ""){
		alert('비밀번호는 필수 항목입니다.');
	}else{
		document.getElementById('f').method = 'post';
		document.getElementById('f').submit();
	}
}
// login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>login</title>
<script src="check.js"></script>
</head>
<body>
	<%@ include file="/session_quiz/header.jsp" %>
	<div align="center">
		<h1>로그인</h1>
		<table>
		<tr><td>
			<form action="loginService.jsp" id="f">
				<input type="text" name="id" placeholder="아이디" id="id"> <br>
				<input type="password" name="pw" placeholder="비밀번호" id="pw"><br>
				<input type="button" value="로그인" onclick="loginCheck()"><br>
			</form>
		</td></tr>
		</table>
	</div>
	<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>

 

// MemberDTO.java
package session_quiz;

public class MemberDTO {
	private String id;
	private String pw;
	private String confirm;
	private String userName;
	private String address;
	private String mobile;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPw() {
		return pw;
	}
	public void setPw(String pw) {
		this.pw = pw;
	}
	public String getConfirm() {
		return confirm;
	}
	public void setConfirm(String confirm) {
		this.confirm = confirm;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	
}
// MemberDAO.java
package session_quiz;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MemberDAO {
	private Connection con;

	public MemberDAO() {
		// 생성자 호출 시 DB 연결을 함.
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "oracle";
		String password = "oracle";

		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void disconnection() {
		// DB 연결을 종료함.
		try {
			if(con != null)
				con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public MemberDTO selectId(String id) {
		String sql = "SELECT * FROM session_quiz WHERE id = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setId(rs.getString("id"));
				member.setPw(rs.getString("pw"));
				member.setUserName(rs.getString("username"));
				member.setAddress(rs.getString("address"));
				member.setMobile(rs.getString("mobile"));
				return member;
			} else {
				System.out.println("입력한 데이터는 존재하지 않습니다.");
			}
		} catch (Exception e) {

		}
		// 사용자가 매개변수로 전달한 아이디가 데이터베이스에 없다는 의미.
		return null;
	}

}
// loginService.jsp
<%@page import="session_quiz.MemberDTO"%>
<%@page import="session_quiz.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	
	// 서버측 입력값 검증
	if(id == "" || pw == "" ){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');location.href='login.jsp'</script>");
		return;
	}
	
	// 데이터베이스에서 아이디/패스워드 정보를 갖고 옴.
	MemberDAO memberDao = new MemberDAO();
	MemberDTO member = memberDao.selectId(id);
	
	if(member != null && member.getPw().equals(pw)){
		// 로그인 성공
		session.setAttribute("id", member.getId());
		session.setAttribute("userName", member.getUserName());
		session.setAttribute("address", member.getAddress());
		session.setAttribute("mobile", member.getMobile());
		response.sendRedirect("index.jsp");
		return;
	}
	// 로그인 실패 시 
%>
<script>alert('아이디 또는 비밀번호를 확인하세요'); location.href='login.jsp'</script>
// register.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>register</title>
<script src="check.js"></script>
</head>
<body>
	<%@ include file="/session_quiz/header.jsp" %>
	<div align="center">
		<h1>회원 등록</h1>
		<table>
		<tr><td>
			<form action="registerService.jsp" method="post" id="f">
				<input type="text" name="id" placeholder="아이디" id="id"> (*필수 항목) <br>
				<input type="password" name="pw" placeholder="비밀번호" id="pw"><br>
				<input type="password" name="confirm" placeholder="비밀번호 확인 " id="confirm" onchange="pwCheck()">
				<label id="label">(*필수 체크)</label><br>
				<input type="text" name="userName" id="userName" placeholder="이름" ><br>
				<input type="text" name="address" placeholder="주소" ><br>
				<input type="text" name="mobile" placeholder="전화번호" ><br>
				<input type="button" value="회원가입" onclick="allCheck()"><br>
			</form>
		</td></tr>
		</table>
	</div>
	<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>

 

// registerService.jsp
<%@page import="session_quiz.MemberDTO"%>
<%@page import="session_quiz.MemberDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
  
<%
	request.setCharacterEncoding("utf-8");
	String id = request.getParameter("id");
	String pw = request.getParameter("pw");
	String confirm = request.getParameter("confirm");
	String userName = request.getParameter("userName");
	String address = request.getParameter("address");
	String mobile = request.getParameter("mobile");
	
	// 서버측 입력값 검증
	if(id == "" || pw == "" || userName == ""){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');location.href='register.jsp'</script>");
		return;
	}
	
	if(pw.equals(confirm) == false){
		out.print("<script>alert('서버에서 검증하고 있는 필수 입력 항목입니다.');</script>");
		response.sendRedirect("register.jsp");
		return;
	}
	
	// Data Access Object
	MemberDAO memberDao = new MemberDAO();
	MemberDTO check = memberDao.selectId(id);
	String message, path;
	if(check == null){
		// 가입하려고 하는 아이디가 없기에 회원가입을 진행함.
		MemberDTO member = new MemberDTO();

		member.setId(id);
		member.setPw(pw);
		member.setUserName(userName);
		member.setAddress(address);
		member.setMobile(mobile);
		
		memberDao.insert(member);
		message = "회원가입 완료";
		path = "index.jsp";
	}else{
		// 가입하려고 하는 아이디가 있기에 중복 계정으로 가입을 할 수 없음.
		message = "회원가입 실패";
		path = "register.jsp";
	}
%>
<script>alert('<%=message%>'); location.href='<%=path%>'</script>
// MemberDAO.java 추가
	public void insert(MemberDTO member) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO session_quiz VALUES(?, ?, ?, ?, ?)";
		
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, member.getId());
			ps.setString(2, member.getPw());
			ps.setString(3, member.getUserName());
			ps.setString(4, member.getAddress());
			ps.setString(5, member.getMobile());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

 

// logout.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
	session.invalidate();
	response.sendRedirect("index.jsp");
%>

 

-SQL 문법 설명

ROWNUM 사용시 번호를 매겨서 rn이라는 이름으로 저장하게 된다.

SQL> SELECT ROWNUM rn FROM session_quiz;

        RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.
// 아래 코드는 () 내부를 A라고 지칭하라는 의미
(SELECT ROWNUM rn, id, username, mobile FROM session_quiz)A

// A.*은 A의 table에서 모든 컬럼을 다 가져온다는 의미
SELECT A.* FROM (SELECT ROWNUM rn, id, username, mobile FROM session_quiz)A

 

// memberInfo.jsp
<%@page import="session_quiz.MemberDAO"%>
<%@page import="session_quiz.MemberDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>memberInfo</title>
</head>
<body>
	<%@ include file="/session_quiz/header.jsp" %>
	<div align="center">
		<h1>회원 목록</h1>
		
		<%
			MemberDAO memberDao = new MemberDAO();
			ArrayList<MemberDTO> members = memberDao.list();
			if(members.isEmpty() == true){
				out.print("<h1> 등록된 데이터가 존재하지 않습니다. </h1>");
				return;
			}else{
		%>
		<table border=1>
			<thead>
				<tr>
					<th>아이디</th>
					<th>이름</th>
					<th>전화번호</th>
				</tr>
			</thead>
			<tbody>
			<%for(MemberDTO member : members){ %>
				<tr>
					<td onclick="location.href='userInfo.jsp?id=<%=member.getId()%>'">
						<%=member.getId() %>
					</td>
					<td><%=member.getUserName() %></td>
					<td><%=member.getMobile() %></td>
				</tr>
			<%} %>
			</tbody>
		</table>
		<%} %>
	</div>
	<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>
// MemberDAO.java 추가
public ArrayList<MemberDTO> list(){
    String sql = "SELECT * FROM session_quiz";
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<MemberDTO> members = new ArrayList<>();

    try {
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        while(rs.next()) {
            MemberDTO member = new MemberDTO();
            member.setId(rs.getString("id"));
            member.setPw(rs.getString("pw"));
            member.setUserName(rs.getString("username"));
            member.setAddress(rs.getString("address"));
            member.setUserName(rs.getString("mobile"));
            members.add(member);
        }
    } catch(Exception e) {
        e.printStackTrace();
    }
    return members;
}

 

저작자표시

'JSP > IT BANK - JSP' 카테고리의 다른 글

IT BANK) JSP 17일차 - JSP(회원관리 페이지(SQL)2)  (0) 2022.09.29
IT BANK) JSP 16일차 - JSP(회원관리 페이지(SQL)2)  (0) 2022.09.28
IT BANK) JSP 14일차 - JSP(회원관리 페이지(SQL))  (0) 2022.09.26
IT BANK) JSP 13일차 - JSP(Oracle 설치, 명령어)  (0) 2022.09.23
IT BANK) JSP 12일차 - JSP(회원관리 페이지)  (0) 2022.09.22
'JSP/IT BANK - JSP' Related Articles more