JSP-JAVA Bean 활용(성적 테이블)

자주 사용하는 메소드를 Bean으로 작성하고, sungjuk table에서 동작하는지 확인한다.

DB연결- DBOPEN.JAVA

package net.utility;

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

public class DBOpen {
	//오라클 데이터베이스 연결 메소드
	
	public Connection getConnection() {
		Connection con=null;
		try {
			String url="jdbc:oracle:thin:@localhost:1521:xe";
			String user="system";
			String password="1234";
			String driver ="oracle.jdbc.driver.OracleDriver"; 
			Class.forName(driver);
			con= DriverManager.getConnection(url, user, password);
			
			
		}catch (Exception e) {
			System.out.println("오라클 연결 실패"+e);
		}
		return con;
	}
	
}

DB연결 종료- DBCLOSE.JAVA

package net.utility;

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

public class DBClose {
//데이터 베이스 연결 자원 반납
	public static void close(Connection con) {
		try{
			if(con!=null){
				con.close();}
		}catch(Exception e){}	
	}
	
	public static void close(Connection con, PreparedStatement pstmt) {
		try{
			if(con!=null){
				con.close();}
		}catch(Exception e){}
		try{
			if(pstmt!=null){
				pstmt.close();
			}
		}catch(Exception e){}
	}
	
	public static void close(Connection con, PreparedStatement pstmt,ResultSet rs) {
		try{
			if(rs!=null){
				rs.close();
			}
		}catch(Exception e){}
		try{
			if(pstmt!=null){
				pstmt.close();
			}
		}catch(Exception e){}

		try{
			if(con!=null){
				con.close();}
		}catch(Exception e){}
}
	
}

DB관련 비즈니스 로직을 구현한다
JSP파일마다 사용하던 SQL문을 DAO파일에 전부 작성하도록 한다.
SUNGJUKDAO.JAVA

package net.sungjuk;

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

import org.eclipse.jdt.internal.compiler.flow.FinallyFlowContext;

import net.utility.DBClose;
import net.utility.DBOpen;

public class SungjukDAO {
//DATA ACCESS OBJECT
//DB관련 비즈니스 로직 구현	
	
	private DBOpen dbopen=null;
	private Connection con=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	private StringBuilder sql=null;
	
	public SungjukDAO() {
		dbopen= new DBOpen();
	}
	
	public int insert(String uname,int kor, int eng, int mat, int aver, String addr) {
		int cnt=0;
		try {
			con=dbopen.getConnection();//db연결 
			
			sql=new StringBuilder();
			sql.append("insert into sungjuk(sno,uname,kor,eng,mat,aver,addr,wdate)");
			sql.append("values(sungjuk_seq.nextval,?,?,?,?,?,?,sysdate)");
			
			pstmt=con.prepareStatement(sql.toString());
			pstmt.setString(1,uname);
			pstmt.setInt(2, kor);
			pstmt.setInt(3, eng);
			pstmt.setInt(4, mat);
			pstmt.setInt(5, aver);
			pstmt.setString(6, addr);
			
			cnt=pstmt.executeUpdate();
			
		}catch (Exception e) {
		System.out.println("행추가 실패"+e);
		}finally {
			DBClose.close(con,pstmt);
		}
		return cnt;
	}
	
	public int create(SungjukDTO dto) {
		int cnt=0;
		try {
			con=dbopen.getConnection();
			
			sql=new StringBuilder();
			sql.append("insert into sungjuk(sno,uname,kor,eng,mat,aver,addr,wdate)");
			sql.append("values(sungjuk_seq.nextval,?,?,?,?,?,?,sysdate)");
			
			pstmt=con.prepareStatement(sql.toString());
			pstmt.setString(1,dto.getUname());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMat());
			pstmt.setInt(5, dto.getAver());
			pstmt.setString(6, dto.getAddr());
			
			cnt=pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("행추가 실패"+e);
		}finally {
			DBClose.close(con,pstmt);
		}
		return cnt;
	}
	
	public ArrayList<SungjukDTO> list() {
	//데이터 베이스에서 가져온 데이터를 한꺼번에 모아서(arraylist)
	//SUNGJUKLIST.JSP에 전달
		ArrayList<SungjukDTO> list =null;
		
	try {
		con=dbopen.getConnection();
		
		sql=new StringBuilder();
		sql.append(" select sno, uname, kor, eng, mat, wdate ");
		sql.append(" from sungjuk ");
		sql.append(" order by wdate desc ");
		
		pstmt=con.prepareStatement(sql.toString());
		rs=pstmt.executeQuery();
		//dto에 모은 행을 list에 모아서 출력
		
		if(rs.next()){
			list=new ArrayList<>();
			do {	//커서가 가리키는 한줄씩 sungjukDto에 저장
				SungjukDTO dto=new SungjukDTO();
				dto.setSno(rs.getInt("sno"));
				dto.setUname(rs.getString("uname"));
				dto.setKor(rs.getInt("kor"));
				dto.setEng(rs.getInt("eng"));
				dto.setMat(rs.getInt("mat"));
				dto.setWdate(rs.getString("wdate"));
				
				list.add(dto);//list에 저장
			}while(rs.next());
		}else{
			list=null;
		}
		
	}catch(Exception e) {
		System.out.println("목록실패"+e);
	}finally {
		DBClose.close(con, pstmt, rs);
	}
	return list;
	}
	
	
	public SungjukDTO read(int sno) {
		SungjukDTO dto =null;
		
		try {
			con=dbopen.getConnection();
			
			sql=new StringBuilder();
			sql.append(" select sno,uname,kor,eng,mat,aver,addr,wdate ");
			sql.append(" from sungjuk ");
			sql.append(" where sno=? ");
			
			pstmt= con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				dto=new SungjukDTO();
				dto.setSno(rs.getInt("sno"));
				dto.setUname(rs.getString("uname"));
				dto.setKor(rs.getInt("kor"));
				dto.setEng(rs.getInt("eng"));
				dto.setAver(rs.getInt("aver"));
				dto.setMat(rs.getInt("mat"));
				dto.setAddr(rs.getString("addr"));
				dto.setWdate(rs.getString("wdate"));
			}
			
		} catch (Exception e) {
			System.out.println("상세보기 실패"+e);
		}finally {
			DBClose.close(con, pstmt, rs);
		}
	return dto;
	}
	
	public int delete(int sno)  {
		int cnt=0;		
		try {
			con= dbopen.getConnection();
			sql=new StringBuilder();
			sql.append(" Delete from sungjuk ");
			sql.append(" where sno=? ");
			
			pstmt= con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			
			cnt=pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("삭제 실패"+e);
		}
		
		return cnt;
	}
	
	
	public int update(SungjukDTO dto) {
		int cnt=0;
		try {
			
			con=dbopen.getConnection();
			
			sql=new StringBuilder();
			sql.append(" update sungjuk ");
			sql.append(" set uname=?,kor=?,eng=?,mat=?,aver=?,addr=?,wdate=sysdate ");
			sql.append(" where sno=? ");
			
			pstmt= con.prepareStatement(sql.toString());
			pstmt.setString(1,dto.getUname());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMat());
			pstmt.setInt(5, dto.getAver());
			pstmt.setString(6, dto.getAddr());
			pstmt.setInt(7, dto.getSno());
			
			cnt=pstmt.executeUpdate();
			
		} catch (Exception e) {
			
			System.out.println("업데이트 실패"+e);
			
		}finally {
			DBClose.close(con,pstmt);
		}
		return cnt;
	}
	}

sungjukDTO.JAVA

package net.sungjuk;


public class SungjukDTO {	//DATA TRANSFER OBJECT 전송객체
							//VALUE OBJECT

	//성적 클래스를 기준으로 한다
	//성적 테이블 칼럼명을 기준으로 멤버 변수 선언
	private int sno;
	private String uname; 
	private int	kor; 
	private int eng;
	private int mat;
	private int aver;
	private String addr;
	private String wdate;
	
	//기본 생성자 함수
public SungjukDTO( ) {
	
}

//private  멤버 변수에 각각 getter와 setter 함수 작성
public int getSno() {
	return sno;
}

public void setSno(int sno) {
	this.sno = sno;
}

public String getUname() {
	return uname;
}

public void setUname(String uname) {
	this.uname = uname;
}

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 getAver() {
	return aver;
}

public void setAver(int aver) {
	this.aver = aver;
}

public String getAddr() {
	return addr;
}

public void setAddr(String addr) {
	this.addr = addr;
}

public String getWdate() {
	return wdate;
}

public void setWdate(String wdate) {
	this.wdate = wdate;
}

@Override
public String toString() {
	return "SungjukDTO [sno=" + sno + ", uname=" + uname + ", kor=" + kor + ", eng=" + eng + ", mat=" + mat + ", aver="
			+ aver + ", addr=" + addr + ", wdate=" + wdate + "]";
}	
}

공통으로 작성하는 코드는 ssi.jsp파일에 작성해 include 한다
ssi.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!-- ssi.jsp 공통 코드를 포함하는 페이지
     -->
 <%@ page import="java.sql.*" %>
 <%@ page import="java.io.*" %>
 <%@ page import="java.util.*" %>
 <%@ page import="net.sungjuk.*" %>
<jsp:useBean id="dao" class="net.sungjuk.SungjukDAO" scope="page"></jsp:useBean>
<jsp:useBean id="dto" class="net.sungjuk.SungjukDTO" scope="page"></jsp:useBean>

<%request.setCharacterEncoding("UTF-8");%>

sungjukForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukForm</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<h3>성적 입력 폼</h3>
<p>
<a href="sungjukList.jsp">[성적목록]</a></p>
<form action="sungjukIns.jsp" name="sungjukfrm" id="sungjukfrm" method="post">
<table class="table table-hover">
<tr>
<th class="success">이름</th>
<td> <input type="text" name="uname" id="uname" maxlength="50" required autofocus></td>
</tr>

<tr>
<th class="success">국어</th>
<td> <input type="number" name="kor" id="kor" size=5 min="0" max="100" placeholder="국어점수"></td>
</tr>
<tr>
<th class="success">영어</th>
<td> <input type="number" name="eng" id="eng" size=5 min="0" max="100" placeholder="영어점수"></td>
</tr>

<tr>
<th class="success">수학</th>
<td> <input type="number" name="mat" id="mat" size=5 min="0" max="100" placeholder="수학점수"></td>
</tr>

<tr>
<th class="success">주소</th>
<td>
<select name="addr" id="addr">
<option value="Seoul">서울</option>
<option value="Suwon">수원</option>
<option value="Busan">부산</option>
<option value="Jeju">제주</option>
</select>
</td>
</tr>

<tr>
<td colspan="2" align="center">
<input class="btn btn-primary" type="submit" value="전송">
<input class="btn btn-secondary" type="reset" value="취소">
 </td>
</tr>
</table>
</form>
</body>
</html>

sungjukIns.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="net.sungjuk.*" %>
<jsp:useBean id="dao" class="net.sungjuk.SungjukDAO" scope="page"></jsp:useBean>
<jsp:useBean id="dto" class="net.sungjuk.SungjukDTO" scope="page"></jsp:useBean>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukIns</title>
</head>
<body>
<h3>성적 결과 페이지</h3>

<%
request.setCharacterEncoding("UTF-8");

String uname=request.getParameter("uname");
int kor=Integer.parseInt(request.getParameter("kor").trim());
int eng=Integer.parseInt(request.getParameter("eng").trim());
int mat=Integer.parseInt(request.getParameter("mat").trim());
String addr=request.getParameter("addr");

int aver=(kor+eng+mat)/3;
/* 1.DTO 객체를 사용하기 전  
int cnt = dao.insert(uname, kor, eng, mat, aver, addr);
*/

/* 2.DTO 객체를 사용하는 경우
//전달 값을 모두 DTO 객체에 담음
*/
dto.setUname(uname);
dto.setKor(kor);
dto.setEng(eng);
dto.setMat(mat);
dto.setAver(aver);
dto.setAddr(addr);

int cnt=dao.create(dto);


if(cnt==0){
	out.println("<p>성적입력이 실패</p>");
	out.println("<p><a href='javascript:history.back()'>재시도</a></p>");
}else{
	out.println("<script>");
	out.println("    alert('성적입력 성공');");
	out.println(" location.href='sungjukList.jsp';");	//목록페이지 이동
	out.println("</script>");
}
%>
</body>
</html>

sungjukRead.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="ssi.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukRead</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<h3>성적 상세보기</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>
<%
int sno=Integer.parseInt(request.getParameter("sno"));
 dto=dao.read(sno);
if(dto==null){
	out.println("해당 글 없음");
}else{
%>
	<table class="table">
	<tr>
	<th class="danger">이름</th>
	<td><%=dto.getUname() %></td>
	</tr>
	<tr>
	<th class="danger">국어</th>
	<td><%=dto.getKor() %></td>
	</tr>
	<tr>
	<th class="danger">영어</th>
	<td><%=dto.getEng() %></td>
	</tr>
	<tr>
	<th class="danger">수학</th>
	<td><%=dto.getMat()%></td>
	</tr>
	<tr>
	<th class="danger">평균</th>
	<td><%=dto.getAver() %></td>
	</tr>
	<tr>
	<th class="danger">주소</th>
	<td><%=dto.getAddr()%>
	<% 
	String addr= dto.getAddr();
	
	if(addr.equals("Seoul")){
		out.print("서울");
	}else if(addr.equals("Jeju")){
		out.print("제주");
	}else if(addr.equals("Suwon")){
		out.print("수원");
	}else if(addr.equals("Busan")){
		out.print("부산");
	}
	
	%>
	</td>
	</tr>
	<tr>
	<th class="danger">작성일</th>
	<td><%=dto.getWdate() %></td>
	</tr>
	</table>
	<br><br>
	<a href="sungjukUpdate.jsp?sno=<%=sno%>">수정</a>
	&nbsp; &nbsp;
	<a href="sungjukDel.jsp?sno=<%=sno%>">삭제</a>

<%
}//if end
%>
</body>
</html>

sungjukList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ include file="ssi.jsp" %>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukList</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<h3>성적 목록</h3>
<p><a href="sungjukForm.jsp">성적쓰기</a></p>
<table class="table">
<tr class="info" >
<th>이름</th>
<th>국어</th>
<th>영어</th>
<th>수학</th>
<th>등록일</th>
</tr>
<%
ArrayList<SungjukDTO> list = dao.list();
if(list==null){
	out.println("<tr>");
	out.println("<td colspan='5'>글없음</td>" );
	out.println("<tr>");
}else{
	for(int i=0;i<list.size();i++){
	dto=list.get(i);
	%>
	
	<tr>
		<td><a href="sungjukRead.jsp?sno=<%=dto.getSno()%>"> <%=dto.getUname() %></a></td>
		<td><%=dto.getKor() %></td>
		<td><%=dto.getEng() %></td>
		<td><%=dto.getMat() %></td>
		<td><%=dto.getWdate().substring(0,10) %></td>
	</tr>
<%
}
}
%>
</table>

</body>
</html>

sungjukDel

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ include file="ssi.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukDel</title>
</head>
<body>
<h3>성적삭제</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>

<%
 int sno= Integer.parseInt(request.getParameter("sno"));
 int cnt=dao.delete(sno);
 
 if(cnt==0){
		out.println("<p>성적입력이 실패</p>");
		out.println("<p><a href='javascript:history.back()'>다시시도</a></p>");
	}else{
		out.println("<script>");
		out.println("    alert('성적삭제 성공');");
		out.println(" location.href='sungjukList.jsp';");	//목록페이지 이동
		out.println("</script>");
	}
%>
</body>
</html>

sungjukUpdate

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ include file="ssi.jsp"%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukUpdate</title>
<link rel="stylesheet"
	href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<script
	src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

</head>
<body>

	<h3>성적 상세보기</h3>
	<p>
		<a href="sungjukForm.jsp">성적쓰기</a> <a href="sungjukForm.jsp">성적목록</a>
	</p>

	<%
	int sno = Integer.parseInt(request.getParameter("sno"));
	dto = dao.read(sno);
	if (dto == null) {
		out.println("해당 글 없음");
	} else {
	%>
	<form action="sungjukUpdateProc.jsp" name="sungjukfrm" id="sungjukfrm"
		method="post">
		<!--3.sno값도 form으로 정보를 전송할때 필요하기 때문에 hidden 속성으로 사용자에게는 보이지 않도록 해서
전달시킨다  -->
		<input type="hidden" name="sno" value="<%=sno%>">
		<table class="table table-hover">
			<tr>
				<th class="success">이름</th>
				<td><input type="text" name="uname" id="uname"
					value="<%=dto.getUname()%>" maxlength="50" required autofocus></td>
			</tr>

			<tr>
				<th class="success">국어</th>
				<td><input type="number" name="kor" id="kor"
					value="<%=dto.getKor()%>" size=5 min="0" max="100"
					placeholder="국어점수"></td>
			</tr>
			<tr>
				<th class="success">영어</th>
				<td><input type="number" name="eng" id="eng"
					value="<%=dto.getEng()%>" size=5 min="0" max="100"
					placeholder="영어점수"></td>
			</tr>

			<tr>
				<th class="success">수학</th>
				<td><input type="number" name="mat" id="mat"
					value="<%=dto.getMat()%>" size=5 min="0" max="100"
					placeholder="수학점수"></td>
			</tr>

			<tr>
				<th class="success">주소</th>
				<td>
					<%
					String addr = dto.getAddr();
					%> <select name="addr" id="addr">
						<option value="Seoul"
							<%if (addr.equals("Seoul")) {
	out.print("selected");
}%>>서울</option>
						<option value="Suwon"
							<%if (addr.equals("Suwon")) {
	out.print("selected");
}%>>수원</option>
						<option value="Busan"
							<%if (addr.equals("Busan")) {
	out.print("selected");
}%>>부산</option>
						<option value="Jeju"
							<%if (addr.equals("Jeju")) {
	out.print("selected");
}%>>제주</option>
				</select>
				</td>
			</tr>

			<tr>
				<td colspan="2" align="center"><input class="btn btn-primary"
					type="submit" value="수정"> <input class="btn btn-secondary"
					type="reset" value="취소"></td>
			</tr>
		</table>
	</form>

	<%
}
%>
</body>
</html>

sungjukUpdateProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ include file="ssi.jsp"%>

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

<%
//값을 받아오기
	int sno = Integer.parseInt(request.getParameter("sno"));
	String uname = request.getParameter("uname");
	int kor = Integer.parseInt(request.getParameter("kor").trim());
	int eng = Integer.parseInt(request.getParameter("eng").trim());
	int mat = Integer.parseInt(request.getParameter("mat").trim());
	String addr = request.getParameter("addr");
	int aver = (kor + eng + mat) / 3;

	dto.setUname(uname);
	dto.setKor(kor);
	dto.setEng(eng);
	dto.setMat(mat);
	dto.setAver(aver);
	dto.setAddr(addr);
	dto.setSno(sno);

	int cnt=dao.update(dto);

	if (cnt == 0) {
		out.println("<p>성적수정 실패</p>");
		out.println("<p><a href='javascript:history.back()'>재시도</a></p>");
	} else {
		out.println("<script>");
		out.println("    alert('성적수정 성공');");
		out.println(" location.href='sungjukList.jsp';"); //목록페이지 이동
		out.println("</script>");
	}
	%>
</body>
</html>