기록해! 정리해!

학생 성적 목록 보기 본문

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");
    

 %>

 

--검색창 만들기

 

저작자표시 비영리 변경금지

'JAVA' 카테고리의 다른 글

자바 - 확인문제 7장 상속  (0) 2022.07.21
자바 - 강제타입변환, 추상클래스  (0) 2022.07.19
자바 - 상속,메소드재정의,다형성  (0) 2022.07.15
자바 -패키지, 접근제한자, 상속  (0) 2022.07.14
자바 - 정적필드, 문제 , 싱글톤, final  (0) 2022.07.13
'JAVA' Related Articles more
Comments