[JSP] Member 구현

DBConn.java ▼▼▼

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConn
{
	private static Connection dbConn;
	
	public static Connection getConnection()
	{
		if (dbConn == null)
		{
			try
			{
				String url = "jdbc:oracle:thin:@localhost:1521:XE";
				String user = "scott";
				String pwd = "tiger";
				
				Class.forName("oracle.jdbc.driver.OracleDriver");
				dbConn = DriverManager.getConnection(url, user, pwd);
				
				
			} catch (Exception e)
			{
				System.out.println(e.toString());
			}
		
		}
		return dbConn;
		
	}
	
	public static Connection getConnection(String url, String user, String pwd)
	{
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			dbConn = DriverManager.getConnection(url, user, pwd);
			
			
		} catch (Exception e)
		{
			System.out.println(e.toString());
		}
		
		return dbConn;
	}
	
	public static void close()
	{
		if (dbConn != null)
		{
			try
			{
				if (!dbConn.isClosed())
				{
					dbConn.close();
				}
			} catch (Exception e)
			{
				System.out.println(e.toString());
			}
		}
		dbConn = null;
	}
}

MemberDAO.java  ▼▼▼

/*
   MemberDAO.java
   - 데이터베이스 액션 처리 전용 객체 활용
 */
package com.test;

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

import com.util.DBConn;

public class MemberDAO
{
	// 주요 속성 구성
	private Connection conn;
	
	// 데이터베이스 연결 → 생성자 형태로 정의
	public MemberDAO()
	{
		conn = DBConn.getConnection();	
	}
	
	public int add(MemberDTO dto) throws SQLException
	{
		int result = 0;
		
		String sql = "INSERT INTO TBL_MEMBER(SID, NAME, TEL)"
				   + " VALUES(MEMBERSEQ.NEXTVAL, ?, ?)";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, dto.getName());
		pstmt.setString(2, dto.getTel());
		
		result = pstmt.executeUpdate();
		
		pstmt.close();
		
		return result;
	}
	
	public ArrayList<MemberDTO> lists() throws SQLException
	{
		ArrayList<MemberDTO> result = new ArrayList<MemberDTO>();
		
		String sql = "SELECT SID, NAME, TEL FROM TBL_MEMBER";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next())
		{
			MemberDTO dto = new MemberDTO();
			dto.setSid(rs.getString("SID"));
			dto.setName(rs.getString("NAME"));
			dto.setTel(rs.getString("TEL"));
			
			result.add(dto);
		}
		
		rs.close();
		pstmt.close();
		return result;
	}
	
	public int count() throws SQLException
	{
		int result = 0;
		String sql = "SELECT COUNT(*) AS COUNT FROM TBL_MEMBER";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next())
		{
			result = rs.getInt("COUNT");
		}
		rs.close();
		pstmt.close();
		return result;
	}
	
	public void close()		
	{
		DBConn.close();
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
}

MemberDTO.java  ▼▼▼ getter / setter 구성

/*
  MemberDTO.java
  - 데이터 전송 객체 활용 
 */
package com.test;

public class MemberDTO
{
	private String sid, name, tel;

	public String getSid()
	{
		return sid;
	}

	public void setSid(String sid)
	{
		this.sid = sid;
	}

	public String getName()
	{
		return name;
	}

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

	public String getTel()
	{
		return tel;
	}

	public void setTel(String tel)
	{
		this.tel = tel;
	}
}

 

 

MemberList.jsp ▼▼▼

<%@page import="com.test.MemberDTO"%>
<%@page import="com.test.MemberDAO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	// 스크립 릿
	StringBuffer str = new StringBuffer();
	MemberDAO dao = null;
	
	String memberCount = "<span id='memberCount'>전체 인원 수 : ";
	
	try
	{
		dao = new MemberDAO();
		memberCount += dao.count() + "명</span>";
		
		str.append("<table class='table'>");
		
		str.append("<tr><th>번호</th><th>이름</th><th>전화번호</th></tr>");
		
		// MemberDAO 객체의 lists()메소드 호출
		// -- 반복문을 통해 <table>하위 엘리먼트 생성
		for(MemberDTO member : dao.lists())
		{
			str.append("<tr>");
			str.append("<td class='record'>" + member.getSid() + "</td>");
			str.append("<td class='record'>" + member.getName() + "</td>");
			str.append("<td class='record'>" + member.getTel() + "</td>");
			str.append("</tr>");
			
		}
		str.append("</table>");
	}
	catch (Exception e)
	{
		System.out.println(e.toString());
	}
	finally
	{
		try
		{
			// 데이터베이스 연결 종료
			dao.close();
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		
	}
	
	

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>MemberList.jsp</title>
<link rel="stylesheet" type="text/css" href="css/main.css">
<style type="text/css">
	.record {text-align: center;}
	input {width: 200px; border-radius: 6px;}
	button {width: 321px; height: 50px; font-family: 맑은 고딕; font-weigth: bold;}
	.errMsg {font-size: small; color: red; display: none;}
</style>
<script type="text/javascript">
	function formCheck()
	{
		alert("확인");
		var name = document.getElementById("userName");
		var errMsg = document.getElementById("nameMsg");
		errMsg.style.display = "none";
		if (name.value=="" || name.value.indexOf(" ") != -1)
		{
			errMsg.style.display ="inline";
			name.focus();
			return false;
		}
		
		return true;
		
	}
</script>
</head>
<body>

<div>
	<h1>데이터베이스 연동 회원관리 실습</h1>
	<hr />
</div>

<div>
	<p>DAO, DTO 개념 적용</p>
	<form action="MemberInsert.jsp" method="post" onsubmit="return formCheck()">
		<table class="table">
			<tr>
				<th>이름(*)</th>
				<td>
					<input type="text" id="userName" name="userName" class="txt" />
					<span class="errMsg" id="nameMsg">이름을 입력해야 합니다.</span>
				</td>
			</tr>
			<tr>
				<th>전화번호</th>
				<td><input type="text" id="userTel" name="userTel" class="txt" /></td>
			</tr>
			<tr>
				<td colspan="2">
				<button type="submit" class="btn" id="btnAdd">회원 추가</button>
				</td>
			</tr>
		</table>
	</form>
</div>
<br>
<!-- 
<div>
	<span id="memberCount">전체 인원 수 : 3명</span>
	<table class="table">
		<tr>
			<th>번호</th>
			<th>이름</th>
			<th>전화번호</th>
		</tr>
		<tr>
			<td class="record">1</td>
			<td class="record">고길동</td>
			<td class="record">010-1111-1111</td>
		</tr>
		<tr>
			<td class="record">2</td>
			<td class="record">도우너</td>
			<td class="record">010-2222-2222</td>
		</tr>
		<tr>
			<td class="record">3</td>
			<td class="record">마이콜</td>
			<td class="record">010-3333-3333</td>
		</tr>
		
	</table>
</div>
 -->
 <!-- 전체 인원 수 확인 -->
 <%=memberCount %>
 <%=str %>
 <!-- 번호 이름 전화번호 리스트 구성 -->
 
</body>
</html>

MemberInsert.jsp ▼▼▼

<%@page import="com.test.MemberDAO"%>
<%@page import="com.test.MemberDTO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	// MemberInsert.jsp
	
	// 한글깨짐방지
	request.setCharacterEncoding("UTF-8");

	// 데이터 수신
	String name = request.getParameter("userName");
	String tel = request.getParameter("userTel");
	
	MemberDAO dao = null;
	int result = 0;
	try
	{
		dao = new MemberDAO();
		
		// MemberDTO 구성
		MemberDTO dto = new MemberDTO();
		
		dto.setName(name);
		dto.setTel(tel);
		
		result = dao.add(dto);
		
		if(result < 1)
		{
			response.sendRedirect("Error.jsp");		
		}
		
	}
	catch(Exception e)
	{
		System.out.println(e.toString());
	}
	finally
	{
		try
		{
			dao.close();
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		
	}
	
	// URL 주소가 적혀있는 (기록되어 있는) 쪽지를 사용자에게 전달
	// 이주소를 요청해서 찾아가세요
	response.sendRedirect("MemberList.jsp");
	
	// 아래 보여주는 페이지(HTML) 삭제..
%>