230501 마흔 일곱 번째 수업_JSP

mary·2023년 5월 1일

국비 수업

목록 보기

47/72

ConnectionPool

--jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, oracle06.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>DBConnectionPool을 이용한 DB 검색</h1>
	<table border="1">
		<tr>
			<th>사번</th>
			<th>이름</th>
			<th>부서명</th>
		</tr>
	<%
		DBConnectionMgr pool = DBConnectionMgr.getInstance();
		Connection con = null; //초기화 작업
		try {
			con = pool.getConnection();
		 
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("SELECT * FROM EMP_02");
			
			while(rs.next()) {
	%>
		<tr>
			<td><%=rs.getString(1) %></td>
			<td><%=rs.getString(2) %></td>
			<td><%=rs.getString(3) %></td>
		</tr>
	<% 
			} 
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pool.freeConnection(con); //이거 해줘야 다른 사람들도 쓸 수 있음
			} catch(Exception e) { }
		}
	%>
	</table>
</body>
</html>

--java파일
package oracle06;

public class UsingBean7 {
	private String emp_id;
	private String emp_name;
	private String dept_title;
	
	public String getEmp_id() {
		return emp_id;
	}
	public void setEmp_id(String emp_id) {
		this.emp_id = emp_id;
	}
	public String getEmp_name() {
		return emp_name;
	}
	public void setEmp_name(String emp_name) {
		this.emp_name = emp_name;
	}
	public String getDept_title() {
		return dept_title;
	}
	public void setDept_title(String dept_title) {
		this.dept_title = dept_title;
	}
}

--Bean과 Connection 파일
package oracle06;

import java.sql.*;
import java.util.ArrayList;

public class UsingBeanDBCon7 {
	private DBConnectionMgr pool;
	
	public UsingBeanDBCon7() {
		try {
			pool = DBConnectionMgr.getInstance();
		} catch(Exception e) {
			System.out.println("Error: 커넥션 얻어오기 실패");
		}
	}
	
	public ArrayList<UsingBean7> getRegisterList() {
		ArrayList<UsingBean7> alist = new ArrayList<UsingBean7>();
		
		Connection con = null;
		
		try {
			con = pool.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("SELECT * FROM EMP_02");
			
			while(rs.next()) {
				UsingBean7 bean = new UsingBean7();
				bean.setEmp_id(rs.getString(1));
				bean.setEmp_name(rs.getString(2));
				bean.setDept_title(rs.getString(3));
				
				alist.add(bean); //안 쓰면 리스트 목록에 객체가 하나도 없음
			}	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return alist; //return해줘야 alist에 있는 거 가져옴
	}
}

--jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="oracle06.*, java.util.*" %>
<jsp:useBean id="beanDBcon" class="oracle06.UsingBeanDBCon7"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Bean과 DBConnectionPool을 이용한 DB검색</h1>
	<table border="1">
		<tr>
			<th>사번</th>
			<th>이름</th>
			<th>부서명</th>
		</tr>
<%
	ArrayList<UsingBean7> alist = beanDBcon.getRegisterList();
	int count = alist.size();
	for(int i=0; i<alist.size(); i++) {
		UsingBean7 bean = alist.get(i);
%>
		<tr>
			<td><%=bean.getEmp_id() %></td>
			<td><%=bean.getEmp_name() %></td>
			<td><%=bean.getDept_title() %></td>
		</tr>
<%
	}
%>
	</table>
	total records: <%=count %>
</body>
</html>

세션과 쿠키

세션과 쿠키는 상태를 지속시키기 위한 방법인데 서버에 저장하면 세션, 클라이언트에 저장하면 쿠키임
보안이 필요한 정보들은 세션, 중요하지 않은 정보는 쿠키로 함

--jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	//쿠키의 값에 띄어쓰기, 특수기호 안 됨. 한글 가능
	Cookie cookie = new Cookie("myCookie", "Apple");
	cookie.setMaxAge(60);
    //cookie.setValue("Banana"); setValue를 넣어 값을 바꿀 수도 있음
	response.addCookie(cookie);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Cookie를 이용한 세션연결</h1>
	쿠키를 만듭니다<p/>
	쿠키 내용은 <a href="01.2.testCookie.jsp">클릭하세요</a>
	
</body>
</html>

--두번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	Cookie[] cookies = request.getCookies();
	if(cookies != null) {
		for(int i=0; i < cookies.length; i++) {
			out.print("Cookie name : " + cookies[i].getName() + "<p/>");
			out.print("Cookie value : " + cookies[i].getValue() + "<p/><hr>");
		}
	}
%>
</body>
</html>

위에는 자동으로 들어온 것 밑에는 내가 넣어준 값

쿠키 여러개 만들기

--jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	Cookie cookie1 = new Cookie("NAME", "KH");
	Cookie cookie2 = new Cookie("GENDER", "MALE");
	Cookie cookie3 = new Cookie("AGE", "23");
	
	response.addCookie(cookie1);
	response.addCookie(cookie2);
	response.addCookie(cookie3);
	
	//response.addCookie(new Cookie("NAME", "KH")); 한 줄로 표현
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Cookie를 이용한 세션연결</h1>
	쿠키를 만듭니다<p/>
	쿠기 내용은 <a href="02.2.testCookies.jsp">클릭하세요</a>
</body>
</html>

--두번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%--
	Cookie[] = cookies = request.getCookies();
	if(cookies != null) {
		for(int i=0; i<cookies.length; i++) {
			out.print("Cookie name: " +  cookies[i].getName() + "<p/>");
			out.print("Cookie value: " + cookies[i].getValue() + "</p><hr>");
		}
	}
--%>
<%
	Cookie[] cookies = request.getCookies();
%>
<%!
	public String getCookieValue(Cookie[] cookies, String name) {
		if(cookies != null) {
			for(int i=0; i<cookies.length; i++) {
				if(cookies[i].getName().equals(name)) {
					return cookies[i].getValue();
				}
			}
		}
		return null;
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	이름: <%=getCookieValue(cookies, "NAME") %><p/>
	성별: <%=getCookieValue(cookies, "GENDER") %><p/>
	나이: <%=getCookieValue(cookies, "AGE") %><p/>
</body>
</html>

세션 사용

--jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	session.setAttribute("idKey", "kh");
	session.setAttribute("pwKey", "1234");
%>
세션이 생성됨<p/>
세션 정보는 <a href="03.2.testSession.jsp">클릭하세요</a>
</body>
</html>

--두번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	Enumeration en = session.getAttributeNames();
	while(en.hasMoreElements()) { //Enumeration의 메소드(객체 하나하나 가져올 때)
		String name = (String)en.nextElement();
		String value = (String)session.getAttribute(name);
		out.print("session name: " + name + "</p>");
		out.print("session vlaue: " + value + "</p>");
	}//while end 
%>
</body>
</html>

쿠키로 로그인 하기

--커넥션 풀에서 테이블 연결시켜주기
/**
 * Copyright(c) 2001 iSavvix Corporation (http://www.isavvix.com/)
 *
 *                        All rights reserved
 *
 * Permission to use, copy, modify and distribute this material for
 * any purpose and without fee is hereby granted, provided that the
 * above copyright notice and this permission notice appear in all
 * copies, and that the name of iSavvix Corporation not be used in
 * advertising or publicity pertaining to this material without the
 * specific, prior written permission of an authorized representative of
 * iSavvix Corporation.
 *
 * ISAVVIX CORPORATION MAKES NO REPRESENTATIONS AND EXTENDS NO WARRANTIES,
 * EXPRESS OR IMPLIED, WITH RESPECT TO THE SOFTWARE, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR ANY PARTICULAR PURPOSE, AND THE WARRANTY AGAINST
 * INFRINGEMENT OF PATENTS OR OTHER INTELLECTUAL PROPERTY RIGHTS.  THE
 * SOFTWARE IS PROVIDED "AS IS", AND IN NO EVENT SHALL ISAVVIX CORPORATION OR
 * ANY OF ITS AFFILIATES BE LIABLE FOR ANY DAMAGES, INCLUDING ANY
 * LOST PROFITS OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES RELATING
 * TO THE SOFTWARE.
 *
 */
package session07;

import java.sql.*;
import java.util.Properties;
import java.util.Vector;

/**
 * Manages a java.sql.Connection pool.
 *
 * @author  Anil Hemrajani
 */

public class DBConnectionMgr {
    private Vector connections = new Vector(10);
    private String _driver = "oracle.jdbc.driver.OracleDriver",
    _url = "jdbc:oracle:thin:@localhost:1521:xe",
    _user = "ddl", //데이터 조회할 테이블명
    _password = "1234";
    private boolean _traceOn = false;
    private boolean initialized = false;
    private int _openConnections = 10;
    private static DBConnectionMgr instance = null;

--java파일
package session07;

import java.sql.*;

public class RegisterMgr {
	private DBConnectionMgr pool;
	
	public RegisterMgr() {
		pool = DBConnectionMgr.getInstance();
	}
	
	public boolean loginRegister(String id, String pw) {
		//사용자가 넣은 아이디와 비밀번호 값이 맞는지 판별
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean loginCon = false;
		try {
			con = pool.getConnection();
			String query = "SELECT COUNT(*) FROM MEM WHERE mem_id = ? and mem_pwd = ?";
			//PreparedStatement는 가져올 컬럼 종류가 많을 경우 Statement대신 씀. '?'를 써서 미리 컴파일을 함
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.setString(2, pw);
			
			rs = pstmt.executeQuery();
			if(rs.next() && rs.getInt(1) > 0) // rs.getInt(1) == count값
				loginCon = true;
		}//try end
			catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}//finally end 
		return loginCon;
	}
}

--첫번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
		String id = "";
		Cookie[] cookies = request.getCookies();
		for(int i=0; i<cookies.length; i++) {
			if(cookies[i].getName().equals("idKey")) {
				id = cookies[i].getValue();
			}
		}
		if(!id.equals("")) { //로그인이 되어있는 상태
	%>
		<script>
			alert("로그인 상태입니다.");
			location.href="04.3.cookieLoginOK.jsp";
		</script>
	<%
		} else { //로그인이 되어있지 않은 상태면 form보여주기
	%>
	<h1>Cookie 로그인</h1>
	<form method="post" action="04.2.cookieLoginProc.jsp">
		ID: <input name="id" required>
		PW: <input type="password" name="pwd" required>
		<input type="submit" value="로그인">
		<input type="reset" value="초기화">
	</form>
	<% } //else end %>
</body>
</html>


--아이디와 비밀번호 판별하여 페이지 구분할 두번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="regMgr" class="session07.RegisterMgr"/>
<%
	String id = request.getParameter("id");
	String pw = request.getParameter("pwd");
	
	
	if(regMgr.loginRegister(id, pw)) { //return값이 boolean(아이디와 비밀번호가 맞는지)
		Cookie cookie = new Cookie("idKey", id);
		response.addCookie(cookie);
		//response.addCookie(new Cookie("idKey", id)); 한 줄로 표현
%>
	<script>
		alert("로그인이 되었습니다");
		location.href = "04.3.cookieLoginOK.jsp";
	</script> 
<%
		
	} else {
%>
	<script>
		alert("로그인이 되지 않았습니다");
		location.href = "04.1.cookieLogin.jsp";
	</script> 
<%
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	
</body>
</html>



--로그인 성공했을 때 세번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = "";
	Cookie[] cookies = request.getCookies();
	for(int i=0; i<cookies.length; i++) {
		if(cookies[i].getName().equals("idKey")) {
			id = cookies[i].getValue();
		}
	}//for end
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Cookie 로그인</h1>
	<h2>Login OK page</h2>
	<%=id %>님이 로그인 하였습니다.<p/>
	홈페이지에 오신 걸 환영합니다.<p/><p/>
	
	<a href="04.4.cookieLogout.jsp">로그아웃</a>
</body>
</html>


--로그아웃하는 페이지 네번째 jsp파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	Cookie[] cookies = request.getCookies();
	for(int i=0; i<cookies.length; i++) {
		if(cookies[i].getName().equals("idKey")) {
			cookies[i].setMaxAge(0);
			response.addCookie(cookies[i]);
		}
	}
%>
<script>
	alert("로그아웃 되었습니다");
	location.href = "04.1.cookieLogin.jsp";
</script>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

자바파일에서 PreparedSratement 사용법

결과

로그인 기능 구현하는 전체 회로도

세션으로 로그인 하기

커넥션풀과 자바파일은 동일하게 사용함

--jsp 첫번째 파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = (String)session.getAttribute("idKey");
	if(id != null) {
%>
<script>
	alert("로그인 상태입니다");
	location.href = "05.3.sessionLoginOK.jsp";
</script>
<% } else { %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Session 로그인</h1>
	<form method="post" action="05.2.sessionLoginProc.jsp">
		ID: <input name="id" required><p/>
		PW: <input type="password" name="pwd" required><p/>
		<input type="submit" value="로그인">
		<input type="reset" value="초기화">
	</form>
	<% } %>
</body>
</html>


--jsp 두번째 파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="regMgr" class="session07.RegisterMgr"/>
<%
	String id = request.getParameter("id");
	String pw = request.getParameter("pwd");
	
	
	if(regMgr.loginRegister(id, pw)) { 
		session.setAttribute("idKey", id);
%>
	<script>
		alert("로그인이 되었습니다");
		location.href = "05.3.sessionLoginOK.jsp";
	</script> 
<%
		
	} else {
%>
	<script>
		alert("로그인이 되지 않았습니다");
		location.href = "05.1.sessionLogin.jsp";
	</script> 
<%
	}
%>    

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	
</body>
</html>


--jsp 세번째 파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id = (String)session.getAttribute("idKey");
	//"idKey" 값을 찾아서 그에 해당하는 값을 변수 id에 넣어주는 것
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Session 로그인</h1>
	<h2>Login OK page</h2>
	<%=id %>님이 로그인 하였습니다.<p/>
	홈페이지에 오신 걸 환영합니다.<p/><p/>
	
	<a href="05.4.sessionLogout.jsp">로그아웃</a>
</body>
</html>


--jsp 네번째 파일
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	session.invalidate();
%>
<script type="text/javascript">
	alert("로그아웃 되었습니다");
	location.href = "05.1.sessionLogin.jsp";
</script>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	
</body>
</html>

*쿠키는 배열로 받아서 일일이 다 for문을 돌며 id값을 비교하지만 세션은 getAttribute로 바로 id값을 불러오기 때문에 for과정이 필요없음

mary

내 인생을 망치러 온 나의 구원, 개발

이전 포스트

230428 마흔 여섯 번째 수업_Servlet,Bean,JDBC

다음 포스트