본문 바로가기
JSP

230127_DB

by 경 훈 2023. 1. 27.

 

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" a href="css/sample.css">
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<header>
	<a href = "index.jsp"><h1>[2023년 성적 공고]</h1></a>
</header>
<nav>
	<a href = "index.jsp">홈으로</a> &emsp;&emsp;
	<a href = "input.jsp">성적입력</a> &emsp;&emsp;
	<a href = "out.jsp">성적출력</a> &emsp;&emsp;
	<a href = "totout.jsp">반별총점현황</a>
</nav>
</body>
</html>

 

<%@ 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>
<footer>
	<a href = "index.jsp">OO시 OO구 OO동 OOOO아카데미 전화번호 OO-OOOO-OOOO</a>
</footer>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<img src="image/school.jpg" width="1200px" height="800px">
</section>
<jsp:include page="footer.jsp"/>
</html>

 

@charset "UTF-8";
a {
	text-decoration: none;
	color:#ffffff;
	font-weight: bold;
}
th,td {
	text-align: center;
	background: #ffffff;
}
header {
	text-align: center;
	background-color: #333333;
	height: 90px;
	line-height: 90px;
	color : #ffffff;
}
nav {
	text-align: center;
	background-color: #444444;
	height: 50px;
	line-height: 50px;
	color : #ffffff;
}
section {
	text-align: center;
	background-color: #aaaaaa;
	color : #000000;
}
footer {
	text-align: center;
	background-color: #333333;
	height: 40px;
	line-height: 40px;
	color : #ffffff;
}

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<form action="save.jsp">
		<table border="1" align="center">
			<tr>
				<th>반</th>
				<td><input type="text" name="ban"></td>
			</tr>
			<tr>
				<th>번호</th>
				<td><input type="text" name="num"></td>
			</tr>
			<tr>
				<th>이름</th>
				<td><input type="text" name="name"></td>
			</tr>		
			<tr>
				<th>국어</th>
				<td><input type="text" name="kor"></td>
			</tr>		
			<tr>
				<th>영어</th>
				<td><input type="text" name="eng"></td>
			</tr>		
			<tr>
				<th>수학</th>
				<td><input type="text" name="mat"></td>
			</tr>
			<tr>
				<td colspan="2" align="center">
				<input type="submit" value="전송">
				<input type="reset" value="리셋"></td>
			</tr>
		</table>
	</form>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="day10_ban.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="dto" class="day10_ban.DTO"/>
<jsp:setProperty property="*" name="dto"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	DAO dao = new DAO();
	dao.insert(dto);
	response.sendRedirect("index.jsp");
	%>
</body>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day10_ban.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs = dao.out();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>반</th><th>번호</th><th>이름</th><th>국어</th><th>영어</th>
			<th>수학</th><th>총점</th><th>평균</th><th>학점</th>
		</tr>
	<%
	while(rs.next()) {
		int ban = rs.getInt(1);
		int num = rs.getInt(2);
		String name = rs.getString(3);
		int kor = rs.getInt(4);
		int eng = rs.getInt(5);
		int mat = rs.getInt(6);
		int tot = rs.getInt(7);
		double avg = rs.getDouble(8);
		String hak = rs.getString(9);
	%>
		<tr>
			<td><%=ban%></td><td><%=num%></td><td><%=name%></td><td><%=kor%></td>
			<td><%=eng%></td><td><%=mat%></td><td><%=tot%></td><td><%=avg%></td>
			<td><%=hak%></td>
		</tr>
	<%	
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day10_ban.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<section>
	<%
	DAO dao = new DAO();
	ResultSet rs=dao.totout();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>반</th><th>반별인원</th>
			<th>국어합계</th><th>영어합계</th><th>수학합계</th>
			<th>국어평균</th><th>영어평균</th><th>수학평균</th>
		</tr>
	<%
	while(rs.next()) {
		int ban = rs.getInt(1);
		int bancount = rs.getInt(2);
		int kortot = rs.getInt(3);
		int engtot = rs.getInt(4);
		int mattot = rs.getInt(5);
		double koravg = rs.getDouble(6);
		double engavg = rs.getDouble(7);
		double matavg = rs.getDouble(8);
	%>
		<tr>
			<td><%=ban%></td><td><%=bancount%></td>
			<td><%=kortot%></td><td><%=engtot%></td><td><%=mattot%></td>
			<td><%=koravg%></td><td><%=engavg%></td><td><%=matavg%></td>
		</tr>
	<%
	}
	%>	
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

package day10_ban;

public class DTO {
	int ban,num;
	String name;
	int kor,eng,mat,tot;
	double avg;
	String hak;
	public DTO() {}
	public DTO(int ban, int num, String name, int kor, int eng, int mat, int tot, double avg, String hak) {
		super();
		this.ban = ban;
		this.num = num;
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
		this.tot = tot;
		this.avg = avg;
		this.hak = hak;
	}
	public int getBan() {
		return ban;
	}
	public void setBan(int ban) {
		this.ban = ban;
	}
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	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 double getAvg() {
		return avg;
	}
	public void setAvg(double avg) {
		this.avg = avg;
	}
	public String getHak() {
		return hak;
	}
	public void setHak(String hak) {
		this.hak = hak;
	}
}

 

package day10_ban;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ChoiceFormat;

public class DAO {
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	
	public DAO() throws ClassNotFoundException {
		Class.forName(driver);
		try {
			con = DriverManager.getConnection(url, "ezen", "12345");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insert(DTO dto) {
		double [] a = {0,60,70,80,90};
		String [] b = {"F","D","C","B","A"};
		ChoiceFormat cf = new ChoiceFormat(a, b);
		int ban = dto.getBan();
		int num = dto.getNum();
		String name = dto.getName();
		int kor = dto.getKor();
		int eng = dto.getEng();
		int mat = dto.getMat();
		int tot = kor+eng+mat;
		double avg = (double)tot/3;
		String hak = cf.format(avg);
		String sql = "insert into db01271 values (?,?,?,?,?,?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, ban);
			psmt.setInt(2, num);
			psmt.setString(3, name);
			psmt.setInt(4, kor);
			psmt.setInt(5, eng);
			psmt.setInt(6, mat);
			psmt.setInt(7, tot);
			psmt.setDouble(8, avg);
			psmt.setString(9, hak);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet out() {
		String sql = "select ban,num,name,kor,eng,mat,tot,avg,hak "
				+ "from db01271 "
				+ "order by ban asc, num asc";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet totout() {
		String sql = "select ban,count(ban),sum(kor),sum(eng),sum(mat), "
				+ "sum(kor)/count(ban),sum(eng)/count(ban),sum(mat)/count(ban)"
				+ "from db01271 "
				+ "group by ban ";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
}

 

'JSP' 카테고리의 다른 글

230131_DB  (0) 2023.01.31
230130_DB  (0) 2023.01.30
230126_DB  (0) 2023.01.26
230125_DB  (0) 2023.01.25
230120_DB  (0) 2023.01.20

관련글

댓글

티스토리툴바