본문 바로가기
JSP

230126_DB

by 경 훈 2023. 1. 26.

쿼리문

정렬

order by 컬럼명 asc(오름차순) , desc(내림차순)

 

package com.ezen;

public class ReportDTO {
	String name;
	int kor,eng,mat;
	public ReportDTO() {}
	public ReportDTO(String name, int kor, int eng, int mat) {
		super();
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
	}
	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;
	}
}

 

<%@ 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>
	<br><br><br>
	<div align="center">
		<a href = "input.jsp"><button>입력</button></a>
		<a href = "out.jsp"><button>출력</button></a>
	</div>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<script type="text/javascript">
function check() {
	var f = document.form;
	//입력된 이름을 검증
	var vname = /^[가-힣]{2,4}$/;
	var cname = f.name.value;
	if(cname=="") {
		alert("이름이 공백이면 안됩니다.");
		f.name.select();
		return false;
	}
	if(!vname.test(cname)) {
		alert("이름은 한글로만 입력하세요.");
		f.name.select();
		return false;
	}
	//국어점수를 검증
	var vkor = /^[0-9]{1,3}$/;
	var ckor = f.kor.value;
	if(ckor=="") {
		alert("국어점수는 반드시 입력");
		f.kor.select();
		return false;
	}
	if(!vkor.test(ckor)) {
		alert("국어점수는 숫자만 가능");
		f.kor.select();
		return false;
	}
	if(ckor>100||cor<0) {
		alert("국어점수는 0~100점만 가능");
		f.kor.select();
		return false;
	}
////
f.submit();
}
</script>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="save.jsp" method="post" name="form">
		<table border="1" align="center">
			<caption>회원성적입력</caption>
			<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="3" align="center">
					<input type="button" value="전송" onclick="check()">
					<input type="reset" value="초기화">
					<a href="menu.jsp"><input type="button" value="메뉴"></a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@page import="com.ezen.ReportDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="dto" class="com.ezen.ReportDTO"/>
<jsp:setProperty property="*" name="dto"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	ReportDAO dao = new ReportDAO();
	dao.insert(dto);
	response.sendRedirect("menu.jsp");
	%>
</body>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="com.ezen.ReportDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	ReportDAO dao = new ReportDAO();
	ResultSet rs = dao.out();
	%>
	<table border="1" width="500px" align="center">
		<tr>
			<th>이름</th><th>국어</th><th>영어</th>
			<th>수학</th><th>총점</th><th>평균</th>
			<th>순위</th>
		</tr>
	<%
	while(rs.next()) {
		String name = rs.getString(1);
		int kor = rs.getInt(2);
		int eng = rs.getInt(3);
		int mat = rs.getInt(4);
		int tot = rs.getInt(5);
		double avg = rs.getDouble(6);
		int rank = rs.getInt(7);
	%>
		<tr>
			<td><%=name%></td><td><%=kor%></td><td><%=eng%></td>
			<td><%=mat%></td><td><%=tot%></td><td><%=avg%></td>
			<td><%=rank%></td>
		</tr>
	<%
	}
	%>
	</table>
	<div align="center">
		<a href="menu.jsp"><input type="button" value="메뉴"></a>
	</div>
</body>
</html>

 

package com.ezen;

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

public class ReportDAO {
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	public ReportDAO() throws ClassNotFoundException {
		Class.forName(driver);
		try {
			con=DriverManager.getConnection(url, "ezen", "12345");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	// 자료를 넘겨받아 디비에 저장하는 기능
	public void insert(ReportDTO dto) {
		String name = dto.getName();
		int kor = dto.getKor();
		int eng = dto.getEng();
		int mat = dto.getMat();
		String sql = "insert into score126 values(?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1, name);
			psmt.setInt(2, kor);
			psmt.setInt(3, eng);
			psmt.setInt(4, mat);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	// 디비에 저장되어 있는 자료를 화면에 보이는 기능
	public ResultSet out() {
		String sql = "select name,kor,eng,mat,(kor+eng+mat)tot,"
				+ "round((kor+eng+mat)/3,1)avg, "
				+ "rank() over(order by (kor+eng+mat) desc) rank "
				+ "from score126";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
}
<%@ 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년 1분기 성적공고</h1></a>
</header>
<nav>
	<a href="input.jsp"><button>성적입력</button></a>
	<a href="out.jsp"><button>성적출력</button></a>
	<a href="index.jsp"><button>홈으로</button></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구 OOO로 1234번지 3층 아카데미 소프트 저작권보호</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="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="day9_score.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="dto" class="day9_score.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="day9_score.DAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	button {
	border: none;
	background-color: #ffffff;
	cursor: pointer;
	}
</style>
<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()) {
		String name = rs.getString(1);
		int kor = rs.getInt(2);
		int eng = rs.getInt(3);
		int mat = rs.getInt(4);
		int tot = rs.getInt(5);
		double avg = rs.getDouble(6);
		String hak = rs.getString(7);
		int rank = rs.getInt(8);
	%>
		<tr>
			<td><%=name%></td><td><%=kor%></td><td><%=eng%></td><td><%=mat%></td>
			<td><%=tot%></td><td><%=avg%></td><td><%=hak%></td><td><%=rank%></td>
			<td>
				<a href="delete.jsp?name=<%=name%>"><button>&#10060;</button></a>
			</td>
		</tr>
	<%		
	}
	%>
	</table>
</section>
<jsp:include page="footer.jsp"/>
</html>

 

<%@page import="day9_score.DAO"%>
<%@ 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>
	<%!
	String dname;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	dname=request.getParameter("name");
	DAO dao = new DAO();
	dao.delete(dname);
	response.sendRedirect("index.jsp");
	%>
</body>
</html>

 

package day9_score;

public class DTO {
	String name;
	int kor,eng,mat,tot;
	double avg;
	String hak;
	public DTO() {}
	public DTO(String name, int kor, int eng, int mat, int tot, double avg, String hak) {
		super();
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
		this.tot = tot;
		this.avg = avg;
		this.hak = hak;
	}
	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 day9_score;

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);
		String name = dto.getName();
		int kor = dto.getKor();
		int eng = dto.getEng();
		int mat = dto.getMat();
		int tot = dto.getKor()+dto.getEng()+dto.getMat();
		double avg = (double)tot/3;
		String hak = cf.format(avg);
		String sql = "insert into db01262 values(?,?,?,?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1, name);
			psmt.setInt(2, kor);
			psmt.setInt(3, eng);
			psmt.setInt(4, mat);
			psmt.setInt(5, tot);
			psmt.setDouble(6, avg);
			psmt.setString(7, hak);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet out() {
		String sql = "select name,kor,eng,mat,tot,avg,hak,"
				+ "rank() over(order by avg desc) rank "
				+ "from db01262";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	public void delete(String dname) {
		String sql = "delete from db01262 where name=?";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1, dname);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

 

'JSP' 카테고리의 다른 글

230130_DB  (0) 2023.01.30
230127_DB  (0) 2023.01.27
230125_DB  (0) 2023.01.25
230120_DB  (0) 2023.01.20
230119_DB  (0) 2023.01.19

관련글

댓글

티스토리툴바