[JSP] Score 구현

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;
	}
}

ScoreDTO.java  ▼▼▼

package com.test;

public class ScoreDTO
{
	private String sid, name;
	int kor, eng, mat, tot;
	double avg;
	
	public int getTot()
	{
		return tot;
	}
	public void setTot(int tot)
	{
		this.tot = tot;
	}
	public double getAvg()
	{
		return avg;
	}
	public void setAvg(double avg)
	{
		this.avg = avg;
	}
	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;
	}
	
}

ScoreDAO.java  ▼▼▼

package com.test;

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

import com.util.DBConn;

public class ScoreDAO
{
	private Connection conn;
	
	public ScoreDAO()
	{
		conn = DBConn.getConnection();
	}
	
	public int add(ScoreDTO dto) throws SQLException
	{
		int result = 0;
		String sql = String.format("INSERT INTO TBL_SCORE(SID, NAME, KOR, ENG, MAT)"
				                 + " VALUES(SCORESEQ.NEXTVAL, '%s', %d, %d, %d)", dto.getName(), dto.getKor(), dto.getEng(), dto.getMat());
		Statement stmt = conn.createStatement();
		result = stmt.executeUpdate(sql);
		stmt.close();
		return result;
	}
	
	public ArrayList<ScoreDTO> lists() throws SQLException
	{
		ArrayList<ScoreDTO> result = new ArrayList<ScoreDTO>();
		String sql = "SELECT SID, NAME, KOR, ENG, MAT"
				   + ", (NVL(KOR, 0)+NVL(ENG, 0)+NVL(MAT, 0)) AS TOT"
				   + ", ROUND((NVL(KOR, 0)+NVL(ENG, 0)+NVL(MAT, 0))/3, 1) AS AVG"
				   + " FROM TBL_SCORE ORDER BY SID";
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		
		while (rs.next())
		{
			ScoreDTO dto = new ScoreDTO();
			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"));
			
			result.add(dto);
		}
		rs.close();
		stmt.close();
		
		return result;
	}
	
	public int count() throws SQLException
	{
		int result = 0;
		
		String sql = "SELECT COUNT(*) AS COUNT FROM TBL_SCORE";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next())
		{
			result = rs.getInt("COUNT");
		}
		rs.close();
		stmt.close();
		return result;
		
	}
	
	public void close()
	{
		DBConn.close();
	}
}

ScoreList.jsp  ▼▼▼

<%@page import="com.test.ScoreDTO"%>
<%@page import="com.test.ScoreDAO"%>
<%@page import="com.util.DBConn"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	// 스크립 릿
	Connection conn =  DBConn.getConnection();
	ScoreDAO dao = null;
	StringBuffer str = new StringBuffer();
	try
	{
		dao = new ScoreDAO();
		
		for(ScoreDTO dto : dao.lists())
		{
			str.append("<tr>");
			str.append("<td class='list'>" + dto.getSid() + "</td>");
			str.append("<td class='list'>" + dto.getName() + "</td>");
			str.append("<td class='list'>" + dto.getKor() + "</td>");
			str.append("<td class='list'>" + dto.getEng() + "</td>");
			str.append("<td class='list'>" + dto.getMat() + "</td>");
			str.append("<td class='list'>" + dto.getTot() + "</td>");
			str.append("<td class='list'>" + dto.getAvg() + "</td>");
			str.append("</tr>");
		}
		 
	}
	catch(Exception e)
	{
		System.out.println(e.toString());
	}

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>ScoreList.jsp</title>
<link rel="stylesheet" type="text/css" href="css/main.css">
<script type="text/javascript">
	function formCheck()
	{
		var name = document.getElementById("uName");
		var kor = document.getElementById("kor");
		var eng = document.getElementById("eng");
		var mat = document.getElementById("mat");
		
		var nameMsg = document.getElementById("nameMsg");
		var korMsg = document.getElementById("korMsg");
		var engMsg = document.getElementById("engMsg");
		var matMsg = document.getElementById("matMsg");
		
		 
		nameMsg.style.display = "none";
		korMsg.style.display = "none";
		engMsg.style.display = "none";
		matMsg.style.display = "none";
		
		if (name.value == "" || name.value.indexOf(" ") != -1)
		{
			nameMsg.style.display = "inline";
			name.focus();
			return false;	
		}
		if (kor.value == "" || kor.value.indexOf(" ") != -1 || parseInt(kor.value) >100 || parseInt(kor.value) < 0 || isNaN(kor.value) )
		{
			korMsg.style.display = "inline";
			kor.focus();
			return false;	
		}
		if (eng.value == "" || eng.value.indexOf(" ") != -1 || parseInt(eng.value) >100 || parseInt(eng.value) < 0 || isNaN(eng.value) )
		{
			engMsg.style.display = "inline";
			eng.focus();
			return false;	
		}
		if (mat.value == "" || mat.value.indexOf(" ") != -1 || parseInt(mat.value) >100 || parseInt(mat.value) < 0 || isNaN(mat.value) )
		{
			matMsg.style.display = "inline";
			mat.focus();
			return false;	
		}
		
		return true;
		
	}
</script>
<style type="text/css">

	.list
	{
		text-align: center;
	}
	.errMsg
	{
		color:	red;
		display: none;
		font-size: small;
	}
	input
	{
		width: 140px;
	}
	.title
	{
		width: 100px;
	}
	.btn
	{
		width:	252px;
		height: 50px;
	}
</style>
</head>
<body>

<div>
	<h1>데이터베이스 연동 및 DAO DTO 를 활용한 성적처리 구현</h1>
	<hr />
</div>

<div>
	<form action="ScoreInsert.jsp" method="post" onsubmit="return formCheck()">
		<table border="1" class="table">
			<tr>
				<td class="title">이름(*)
				</td>
				<td>
				<input type="text" id="uName" name="uName"/>
				<span class="errMsg" id="nameMsg">이름을 입력하세요</span>
				</td>
			</tr>
			<tr>	
				<td class="title">	
					국어점수
				</td>
				<td>
					<input type="text" id="kor" name="kor" />
					<span class="errMsg" id="korMsg">0~100사이의 숫자만 입력해주세요</span>
				</td>
			</tr>
			<tr>	
				<td class="title">	
					영어점수
				</td>
				<td>	
					<input type="text" id="eng" name="eng"/>
					<span class="errMsg" id="engMsg">0~100사이의 숫자만 입력해주세요</span>
				</td>
			</tr>
			<tr>	
				<td class="title">	
					수학점수
				</td>
				<td>	
					<input type="text" id="mat" name="mat"/>
					<span class="errMsg" id="matMsg">0~100사이의 숫자만 입력해주세요</span>
				</td>
			</tr>
			<tr>
				<td colspan="2"> 
				<button type="submit" class="btn">성적 입력</button>
				</td>
			</tr>
		</table>
	</form>
</div>


<div>
	<form action="">
		<table border="1" class="table">
			<tr>
				<th>번호</th>
				<th>이름</th>
				<th>국어점수</th>
				<th>영어점수</th>
				<th>수학점수</th>
				<th>총점</th>
				<th>평균</th>
			</tr>
			<%=str %>
		</table>
	</form>
</div>



</body>
</html>

ScoreInsert.jsp  ▼▼▼

<%@page import="com.test.ScoreDTO"%>
<%@page import="com.test.ScoreDAO"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<%
	// 스크립 릿
	request.setCharacterEncoding("UTF-8");

	String name = "";
	int kor = 0;
	int eng = 0;
	int mat = 0;
	int result = 0;
	ScoreDAO dao = null;
	try
	{
		dao = new ScoreDAO();
		
		name = request.getParameter("uName");
		kor = Integer.parseInt(request.getParameter("kor"));
		eng = Integer.parseInt(request.getParameter("eng"));
		mat = Integer.parseInt(request.getParameter("mat"));
		
		ScoreDTO dto = new ScoreDTO();
		dto.setName(name);
		dto.setKor(kor);
		dto.setEng(eng);
		dto.setMat(mat);
		
		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());
		}
	}
	
	response.sendRedirect("ScoreList.jsp");
	
%>