JSP 강의 정리 16일차

[1] 테스트 환경

hardware

cpu : intel i7-7700, gpu : intel hd 630, ram : ddr4 16GB, mainboard : samsung DB400S7A-Z51, secondary storage : 256GB SSD, 1TB HDD

OS

windows pro 64bits

software

jdk version 1.8.0_301 eclipse version 2021-09 (4.21.0) apache-tomcat version 9.0.54

 

[2] 강의 정리

 

Servers>

context.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
--><!-- The contents of this file will be loaded for each web application --><Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
    
    
    <Resource auth="Container" 
          name="jdbc/oracle" 
          driverClassName="oracle.jdbc.driver.OracleDriver" 
          type="javax.sql.DataSource" 
          url="jdbc:oracle:thin:@localhost:1521:xe" 
          username="C##JSPUSER"
          password="jsp123" 
          loginTimeout="10" 
          maxActive="50" 
          maxIdle="20"
          maxWait="2000" 
          testOnBorrow="true" />
	<!--
	    auth : 컨테이너를 자원 관리자로 기술 (인증주체)
	    name : 자원(JDBC)의 이름, (변경 가능)
	    driverClassName : 연결할 데이터베이스에 따른 드라이버 클래스 이름
	    type : 웹에서 이 리소스를 사용할 때 DataSource 타입으로 반환
	    loginTimeout : 연결 끊어지는 시간
	    maxActive : 최대 연결 가능한 Connection수 (기본 20개)
	    maxIdle : Connection pool 유지를 위해 최대 대기 connection 숫자
	    maxWait : 사용 가능한 커넥션이 없을 때 커넥션 회수를 기다리는 시간 (1000 = 1초)
	    testOnBorrow : db에 test를 해볼 것인지
	-->
    
    
    
</Context>

 

KGMemberDTO.java

package com.jsp.dbcp;

public class KGMemberDTO {
	
	private String id;
	private String pw;
	private String name;
	private int age;
	private String phone;
	
	public KGMemberDTO() {}

	public KGMemberDTO(String id, String pw, String name, int age, String phone) {
		super();
		this.id = id;
		this.pw = pw;
		this.name = name;
		this.age = age;
		this.phone = phone;
	}

	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 getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}	
}

 

KGMemberDAO.java

package com.jsp.dbcp;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class KGMemberDAO {
	
	
	private DataSource ds;
	// Connection pool을 관리하는 객체
	
	public KGMemberDAO() {
		
		try {
			
			Context context = new InitialContext();
			// JNDI 서비스를 제공하는 객체를 생성한다.
			// JNDI : Java Naming & Directory Interface 
			// "이름" 을 가지고 데이터베이스 정보(객체)를 얻을 수 있는 API
			
			ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public ArrayList<KGMemberDTO> memberList(){
		
		ArrayList<KGMemberDTO> list = new ArrayList<KGMemberDTO>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			
			conn = ds.getConnection();
			// DataSource 를 통해 Connection 객체를 얻는다.
			pstmt = conn.prepareStatement("select * from KGMember");
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				
				String id = rs.getString("id");
				String pw = rs.getString("pw");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String phone = rs.getString("phone");
				
				list.add(new KGMemberDTO(id, pw, name, age, phone));
				
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			try {
				
				if(rs != null)rs.close();
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
				
			}catch(Exception e2) {}
			
		}
		
		return list;
	}
	
	
	
}

 

memberList.jsp

<%@page import="com.jsp.dbcp.KGMemberDTO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.jsp.dbcp.KGMemberDAO"%>
<%@ 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>
	
	<%
		KGMemberDAO dao = new KGMemberDAO();
	
		ArrayList<KGMemberDTO> list = dao.memberList();
		
		for(int i = 0; i < list.size(); i++){
			
			KGMemberDTO dto = list.get(i);
			
			out.print("아이디 : " + dto.getId() + ", 비밀번호 : " + dto.getPw()
			+ ", 이름 : " + dto.getName() + ", 나이 : " + dto.getAge() 
			+ ", 핸드폰 : " + dto.getPhone() + "<br>");
			
		}
	
	
	%>
	
</body>
</html>

 

()

sql 문

create table members (
    id varchar2(20) primary key,
    pw varchar2(20) not null,
    name nvarchar2(20) not null,
    email varchar2(30),
    regDate timestamp default systimestamp,
    address nvarchar2(30)
);

 

MembersDTO.java

package com.jsp.members;

import java.sql.Timestamp;

public class MembersDTO {
	
	private String id;
	private String pw;
	private String name;
	private String email;
	private Timestamp regDate;
	private String address;
	
	public MembersDTO() {}

	public MembersDTO(String id, String pw, String name, String email, Timestamp regDate, String address) {
		super();
		this.id = id;
		this.pw = pw;
		this.name = name;
		this.email = email;
		this.regDate = regDate;
		this.address = address;
	}

	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 getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Timestamp getRegDate() {
		return regDate;
	}

	public void setRegDate(Timestamp regDate) {
		this.regDate = regDate;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}
	
	
}

 

MembersDAO.java

package com.jsp.members;

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class MembersDAO {
	
	private DataSource ds;
	
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private MembersDAO() {
		try {
			
			Context context = new InitialContext();
			ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private static MembersDAO instance = new MembersDAO();
	
	public static MembersDAO getInstance() {
		return instance;
	}
	
	private void close(Connection conn) {
		
		try {
			if(conn != null) {
				conn.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private void close(PreparedStatement pstmt) {
		
		try {
			if(pstmt != null) {
				pstmt.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private void close(ResultSet rs) {
		
		try {
			if(rs != null) {
				rs.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	// 회원가입 후 멤버의 정보를 넣어주는 메소드
	public int insertMember(MembersDTO dto) {
		int result = 0;
		
		String query = "insert into members(id, pw, name, email, address) "
				+ "values(?, ?, ?, ?, ?)";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, dto.getId());
			pstmt.setString(2, dto.getPw());
			pstmt.setString(3, dto.getName());
			pstmt.setString(4, dto.getEmail());
			pstmt.setString(5, dto.getAddress());
			
			result = pstmt.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(pstmt);
			close(conn);
		}
		
		return result;
	}
	
	// members 테이블에 같은 아이디가 있는지 확인해주는 메소드
	// (아이디가 존재한다면 1, 존재하지 않는다면 0) 
	public int confirmId(String id) {
		int result = 0;
		String query = "select id from members where id = ?";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				// 멤버가 존재할때
				result = 1;
			}else {
				// 멤버가 존재하지 않을때
				result = 0;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return result;
	}
	
	
	// 로그인 체크 메소드(id, pw 의 데이터를 받아서 DB연결 후 id로 pw 데이터 얻기)
	// (아이디가 존재하지 않으면 -1, 존재하고 비밀번호가 틀린경우 0, 존재하고 비밀번호가 맞는경우 1)
	public int userCheck(String id, String pw) {
		int result = 0;
		
		String query = "select pw from members where id = ?";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) { // 아이디가 존재할때
				
				String dbPw = rs.getString("pw");
				
				if(dbPw.equals(pw)) {
					result = 1; // 로그인 OK
				}else {
					result = 0; // 비밀번호 X
				}
				
			}else { // 아이디가 존재하지 않을때
				result = -1;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return result;
	}
	
	// 회원의 정보를 얻는메소드
	public MembersDTO getMember(String id) {
		
		MembersDTO dto = null;
		String query = "select * from members where id = ?";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				
				String dbId = rs.getString("id");
				String pw = rs.getString("pw");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Timestamp regDate = rs.getTimestamp("regDate");
				String address = rs.getString("address");
				
				dto = new MembersDTO(dbId, pw, name, email, regDate, address);
				
			}
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return dto;
	}
	
	// 회원의 정보를 수정하는 메소드
	public int updateMember(MembersDTO dto) {
		int result = 0;
		
		String query = "update members set pw = ?, email = ?, address = ? "
				+ "where id = ?";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, dto.getPw());
			pstmt.setString(2, dto.getEmail());
			pstmt.setString(3, dto.getAddress());
			pstmt.setString(4, dto.getId());
			
			result = pstmt.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			close(pstmt);
			close(conn);
		}
		
		return result;
	}
	
	// 회원탈퇴(회원삭제) 하는 메소드
	public int deleteMember(String id) {
		int result = 0;
		String query = "delete from members where id = ?";
		
		try {
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, id);
			
			result = pstmt.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			close(pstmt);
			close(conn);
			
		}
		
		return result;
	}

}