JAVA
학생 성적 목록 보기
zsuling
2022. 7. 15. 17:59
07/15
-form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="top.jsp"%>
<%@ page import = "java.sql.*" %>
<%
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String id = "system";
String pwd = "1234";
// 1) 데이터베이스 드라이버 로딩
Class.forName("oracle.jdbc.OracleDriver");
// 2)데이터베이스 컨넥션
Connection conn = DriverManager.getConnection(url, id, pwd);
// 3) SQL 쿼리문
String sql = "select max(sno)+1 as max_sno from examtbl ";
// 4) PreparedStatement 객체 생성
PreparedStatement pstmt = conn.prepareStatement(sql);
// 5) 실행
ResultSet rs = pstmt.executeQuery(); // executeUpdate() or executeQuery()
rs.next();
int max_sno = rs.getInt("max_sno");
%>
<section>
<br>
<div align="center">
<font size=5><b>학생 성적 등록</b></font>
</div>
<div align="center">
<form name="f1" action=form_ok.jsp >
<table width=600 border=1 height=400>
<tr><td width=200 align="center">학번</td>
<td width=400><input type=text name=sno value="<%=max_sno%>"> </td></tr>
<tr><td align="center">학생이름</td>
<td ><input type=text name=sname> </td></tr>
<tr><td align="center">국어</td>
<td><input type=text name=kor size=10> </td></tr>
<tr><td align="center">영어</td>
<td><input type=text name=eng size=10> </td></tr>
<tr><td align="center">수학</td>
<td><input type=text name=math size=10> </td></tr>
<tr><td align="center">역사</td>
<td><input type=text name=hist size=10> </td></tr>
<tr>
<td colspan=2 align="center">
<input type=submit value="저장하기">
<input type=reset value="다시작성">
</td>
</tr>
</table>
</form>
</div>
<br>
<%
rs.close();
pstmt.close();
conn.close();
%>
</section>
<footer>
HRDKOREA Copyright 2018 ALL Rights resources Development Service of Korea
</footer>
</div>
</body>
</html>
-form_ok.jsp
1. 데이터베이스 드라이버 로딩
2. 데이터베이스 커넥션
3. sql쿼리문 작성
4. PreparedStatement 객체생성
5. 실행 pstmt.executeUpdate();
( 6. SQL에서 select 는 return을 받아야함 )
pstmt.executeUpdate();
pstmt.executeQuery(); --select일때만 사용
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
String kor = request.getParameter("kor");
String eng = request.getParameter("eng");
String math = request.getParameter("math");
String hist = request.getParameter("hist");
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
String sql= "insert into examtbl(sno, sname, kor, eng, math, hist) values (?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sno);
pstmt.setString(2, sname);
pstmt.setString(3, kor);
pstmt.setString(4, eng);
pstmt.setString(5, math);
pstmt.setString(6, hist);
pstmt.executeUpdate();
pstmt.close();
conn.close();
response.sendRedirect("index.jsp");
%>
<div align="center">
학번 :<%= sno%> <br>
이름 :<%= sname%> <br>
국어 :<%= kor%> <br>
영어 :<%= eng%> <br>
수학 :<%= math%> <br>
역사 :<%= hist%> <br>
</div>
-list.jsp
-- 학생 수 찍어보기
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
String sql= "select sno, sname, kor, eng, math, hist from examtbl ";
String sql_tc= "select count(*) as tc from examtbl ";
PreparedStatement pstmt = conn.prepareStatement(sql);
PreparedStatement pstmt_tc = conn.prepareStatement(sql_tc);
ResultSet rs = pstmt.executeQuery();
ResultSet rs_tc = pstmt_tc.executeQuery();
rs_tc.next();
int tc = rs_tc.getInt("tc");
%>
<%@ include file="top.jsp"%>
<section>
<br>
<div align="center">
<h2><b>학생 성적 목록보기 (<%=tc%>명)</b></h2>
<%
%>
</div>
<div align="center">
<table border=1 whidth=600>
<tr>
<th>학번</th><th>이름</th><th>국어</th>
<th>영어</th><th>수학</th><th>역사</th>
<th>합계</th><th>평균</th>
</tr>
<% while(rs.next()) {
int sum = rs.getInt("kor")+rs.getInt("eng")+rs.getInt("math")+rs.getInt("hist");
double avg = (double)sum/4;
%>
<tr>
<td><%=rs.getString("sno")%></td><td><%=rs.getString("sname")%></td><td><%=rs.getInt("kor")%></td>
<td><%=rs.getInt("eng")%></td><td><%=rs.getInt("math")%></td><td><%=rs.getInt("hist")%></td>
<td><%=sum%></td><td><%=avg%></td>
</tr>
<% } %>
</table>
</div>
<br>
<%
rs.close();
pstmt.close();
conn.close();
%>
</section>
<footer>
HRDKOREA Copyright 2018 ALL Rights resources Development Service of Korea
</footer>
</div>
</body>
</html>
-- 이름에 링크걸고 누르면 상세보기 할 수 있게하기
1. list.jsp에 링크걸기
<td><a href=edit.jsp?sno=<%=rs.getString("sno") %>>
<%=rs.getString("sname") %></a></td>
2.edit.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="top.jsp"%>
<%@ page import = "java.sql.*" %>
<%
String sno = request.getParameter("sno");
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String id = "system";
String pwd = "1234";
// 1) 데이터베이스 드라이버 로딩
Class.forName("oracle.jdbc.OracleDriver");
// 2)데이터베이스 컨넥션
Connection conn = DriverManager.getConnection(url, id, pwd);
// 3) SQL 쿼리문
String sql = "select * from examtbl where sno=? ";
// 4) PreparedStatement 객체 생성
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sno);
// 5) 실행
ResultSet rs = pstmt.executeQuery(); // executeUpdate() or executeQuery()
rs.next();
sno = rs.getString("sno");
String sname = rs.getString("sname");
int kor = rs.getInt("kor");
int eng = rs.getInt("eng");
int math = rs.getInt("math");
int hist = rs.getInt("hist");
%>
<section>
<br>
<div align="center">
<font size=5><b>학생 성적 등록</b></font>
</div>
<div align="center">
<form name="f1" action=update_ok.jsp >
<table width=600 border=1 height=400>
<tr><td width=200 align="center">학번</td>
<td width=400><input type=text name=sno value="<%=sno%>"> </td></tr>
<tr><td align="center">학생이름</td>
<td ><input type=text name=sname value="<%=sname%>"> </td></tr>
<tr><td align="center">국어</td>
<td><input type=text name=kor size=10 value="<%=kor%>"> </td></tr>
<tr><td align="center">영어</td>
<td><input type=text name=eng size=10 value="<%=eng%>"> </td></tr>
<tr><td align="center">수학</td>
<td><input type=text name=math size=10 value="<%=math%>"> </td></tr>
<tr><td align="center">역사</td>
<td><input type=text name=hist size=10 value="<%=hist%>"> </td></tr>
<tr>
<td colspan=2 align="center">
<input type=submit value="수정하기">
<input type=reset value="다시작성">
</td>
</tr>
</table>
</form>
</div>
<br>
<%
rs.close();
pstmt.close();
conn.close();
%>
</section>
<footer>
HRDKOREA Copyright 2018 ALL Rights resources Development Service of Korea
</footer>
</div>
</body>
</html>-- 링크눌러서 값 수정하게 하기
-update_ok.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
String kor = request.getParameter("kor");
String eng = request.getParameter("eng");
String math = request.getParameter("math");
String hist = request.getParameter("hist");
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
String sql= "update examtbl set sname=?, kor=?, eng=?, math=?, hist=? where sno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sname);
pstmt.setString(2, kor);
pstmt.setString(3, eng);
pstmt.setString(4, math);
pstmt.setString(5, hist);
pstmt.setString(6, sno);
pstmt.executeUpdate();
pstmt.close();
conn.close();
response.sendRedirect("list.jsp");
%>
<div align="center">
학번 :<%= sno%> <br>
이름 :<%= sname%> <br>
국어 :<%= kor%> <br>
영어 :<%= eng%> <br>
수학 :<%= math%> <br>
역사 :<%= hist%> <br>
</div>
-- 수정하기 -> 삭제하기
<input type=button onClick="ck(<%=sno%>)" value="삭제하기">
<script>
function ck(k){
//alert(k + "확인");
location.href="delete.jsp?sno="+<%=sno%>
//return false;
}
</script>
-delete.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%
String sno = request.getParameter("sno");
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
String sql= "delete from examtbl where sno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sno);
pstmt.executeUpdate();
pstmt.close();
conn.close();
response.sendRedirect("list.jsp");
%>
--검색창 만들기