[JSP] Member Score 구현

MemberScoreDTO.java  ▼▼▼

/* ====================================
 MemberScoreDTO.java
 - 객체 전용(데이터 보관 및 전송) → JAVA Bean	
 ==================================== */
package com.test;

public class MemberScoreDTO
{
	// VIEW_MEMBERSCORE 조회 항목을 기준으로
	// 프로퍼티(property) 구성
	private String sid, name;	// 번호 이름
	private int kor, eng, mat;  // 국어, 영어, 수학
	private int tot, rank;		// 총점, 석차(등수)
	private double avg;			// 평균
	
	// getter / setter 구성
	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 int getKor()
	{
		return kor;
	}
	public void setKor(int kor)
	{
		this.kor = kor;
	}
	public int getEng()
	{
		return eng;
	}
	public void setEng(int eng)
	{
		this.eng = eng;
	}
	public int getMat()
	{
		return mat;
	}
	public void setMat(int mat)
	{
		this.mat = mat;
	}
	public int getTot()
	{
		return tot;
	}
	public void setTot(int tot)
	{
		this.tot = tot;
	}
	public int getRank()
	{
		return rank;
	}
	public void setRank(int rank)
	{
		this.rank = rank;
	}
	public double getAvg()
	{
		return avg;
	}
	public void setAvg(double avg)
	{
		this.avg = avg;
	}
	
}

MemberScoreDAO.java  ▼▼▼

/* =================================
	MemberScoreDAO.java
	- 데이터베이스 액션 처리 전용 클래스
	(TBL_MEMBERSCORE 테이블 전용 DAO)
 ================================= */
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 MemberScoreDAO
{
	// 주요 속성 구성
	private Connection conn;
	
	// 데이터베이스 연결 담당 메소드
	public Connection connection()
	{
		conn = DBConn.getConnection();
		return conn;
	}
	
	// 데이터 입력 담당 메소드(성적 데이터 입력)
	public int add(MemberScoreDTO dto) throws SQLException
	{
		int result = 0;
		String sql = "INSERT INTO TBL_MEMBERSCORE(SID, KOR, ENG, MAT)"
				   + " VALUES(?, ?, ?, ?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, dto.getSid());
		pstmt.setInt(2, dto.getKor());
		pstmt.setInt(3, dto.getEng());
		pstmt.setInt(4, dto.getMat());
		
		result = pstmt.executeUpdate();
		pstmt.close();
		return result;
	}
	
	// 성적 전체 리스트 출력 담당 메소드
	public ArrayList<MemberScoreDTO> lists() throws SQLException
	{
		ArrayList<MemberScoreDTO> result = new ArrayList<MemberScoreDTO>();
		
		String sql = "SELECT SID, NAME, KOR, ENG, MAT"
				   + ", (KOR+ENG+MAT) AS TOT"
				   + ", (KOR+ENG+MAT)/3 AS AVG"
				   + ", RANK() OVER(ORDER BY (KOR+ENG+MAT) DESC) AS RANK"
				   + " FROM VIEW_MEMBERSCORE"
				   + " ORDER BY SID";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next())
		{
			MemberScoreDTO dto = new MemberScoreDTO();
			dto.setSid(rs.getString("SID"));
			dto.setName(rs.getString("NAME"));
			dto.setKor(rs.getInt("KOR"));
			dto.setEng(rs.getInt("ENG"));
			dto.setMat(rs.getInt("MAT"));
			dto.setTot(rs.getInt("TOT"));
			dto.setAvg(rs.getDouble("AVG"));
			dto.setRank(rs.getInt("RANK"));
			
			result.add(dto);
		}
		rs.close();
		pstmt.close();
		return result;
	}
	
	// 성적 수정
	public int scoreUpdate(MemberScoreDTO dto) throws SQLException
	{
		int result = 0;
		String sql = "UPDATE TBL_MEMBERSCORE"
				   + " SET KOR=?, ENG=?, MAT=?"
				   + " WHERE SID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, dto.getKor());
		pstmt.setInt(2, dto.getEng());
		pstmt.setInt(3, dto.getMat());
		pstmt.setString(4, dto.getSid());
		
		result = pstmt.executeUpdate();
		pstmt.close();

		return result;
	}
	
	// 성적 삭제
	public int scoreDelete(String sid) throws SQLException
	{
		int result = 0;
		String sql = "DELETE"
				   + " FROM TBL_MEMBERSCORE"
				   + " WHERE SID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, sid);
		
		result = pstmt.executeUpdate();
		pstmt.close();
		return result;
	}
	
	// 성적 검색
	public MemberScoreDTO searchScore(String sid) throws SQLException
	{
		MemberScoreDTO result = new MemberScoreDTO();
		String sql = "SELECT SID, NAME, KOR, ENG, MAT"
				+ " FROM VIEW_MEMBERSCORE"
				+ " WHERE SID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, sid);
		
		ResultSet rs = pstmt.executeQuery();
		while (rs.next())
		{
			result.setName(rs.getString("NAME"));
			result.setKor(rs.getInt("KOR"));
			result.setEng(rs.getInt("ENG"));
			result.setMat(rs.getInt("MAT"));
			
		}
		rs.close();
		pstmt.close();
		return result;
	}
	
	
	
	// 데이터베이스 연결 종료(해제) 담당 메소드
	public void close()
	{
		DBConn.close();
	}
}

MemberScoreInsert.jsp  ▼▼▼

<%@page import="com.test.MemberScoreDTO"%>
<%@page import="com.test.MemberScoreDAO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	//MemberScoreInsert.jsp
	String sid = request.getParameter("sid");
	int kor = Integer.parseInt(request.getParameter("uKor"));
	int eng = Integer.parseInt(request.getParameter("uEng"));
	int mat = Integer.parseInt(request.getParameter("uMat"));
	
	MemberScoreDAO dao = new MemberScoreDAO();
	try
	{
		dao.connection();
		MemberScoreDTO dto = new MemberScoreDTO();
		
		dto.setSid(sid);
		dto.setKor(kor);
		dto.setEng(eng);
		dto.setMat(mat);
		
		dao.add(dto);
		
	}
	catch(Exception e)
	{
		System.out.println(e.toString());
	}
	finally
	{
		try
		{
			dao.close();
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
	response.sendRedirect("MemberScoreSelect.jsp");
	
%>

MemberScoreUpdate.jsp ▼▼▼

<%@page import="com.test.MemberScoreDTO"%>
<%@page import="com.test.MemberScoreDAO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	
	request.setCharacterEncoding("UTF-8");

	// 이전페이지 (MemberUpdateForm.jsp) 로부터 sid, kor, eng, mat 수신
	String sid = request.getParameter("sid");


	int kor = Integer.parseInt(request.getParameter("uKor"));
	int eng = Integer.parseInt(request.getParameter("uEng"));
	int mat = Integer.parseInt(request.getParameter("uMat"));
	
	MemberScoreDAO dao = new MemberScoreDAO();
	try
	{
		dao.connection();
		MemberScoreDTO dto = new MemberScoreDTO();
		dto.setSid(sid);
		dto.setKor(kor);
		dto.setEng(eng);
		dto.setMat(mat);
		
		dao.scoreUpdate(dto);
	}
	catch(Exception e)
	{
		System.out.println(e.toString());
	}
	finally
	{
		try
		{
			dao.close();
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
	response.sendRedirect("MemberScoreSelect.jsp");	
	
%>

MemberScoreDelete.jsp  ▼▼▼

<%@page import="com.test.MemberScoreDAO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	// MemberScoreDelete.jsp;
	String sid = request.getParameter("sid");	
	MemberScoreDAO dao = new MemberScoreDAO();
	
	try
	{
		dao.connection();
		dao.scoreDelete(sid);
		
	}
	catch(Exception e)
	{
		System.out.println(e.toString());
	}
	finally
	{
		try
		{
			dao.close();	
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}	
	}

	
	response.sendRedirect("MemberScoreSelect.jsp");
%>

Notice.jsp  ▼▼▼ 삭제불가한 경우 유도해주는 페이지

<%@ page contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Notice.jsp</title>
<link rel="stylesheet" type="text/css" href="css/MemberScore.css">
</head>
<body>

<div>
	<h1>안내</h1>
	<hr />
</div>

<div>
	<p>성적 적보가 등록되어있으므로 해당 회원 삭제가 불가능합니다.</p>
	<br />
	<a href="MemberSelect.jsp" style="text-decoration:none;">돌아가기</a>
</div>


</body>
</html>