○ 성적JSP페이지
- 참고 : 오라클 DB, 성적 테이블 SQL문
create table sungjuk(
uname varchar(20)
,kor int
,eng int
,mat int
,aver int
,addr varchar(20)
,wdate date
);
○ 성적 입력
- 성적 입력 폼 : sungjukForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukForm.jsp</title>
<style>
h3 {
font: 40px Montserrat, sans-serif;
font-weight: 500;
}
a {
font: 20px Montserrat, sans-serif;
font-weight: 400;
}
</style>
</head>
<body>
<div class="container-fluid text-center">
<h3>★ 성적 입력 폼 ★</h3>
<p><a href="sungjukList.jsp">[성적목록]</a></p>
<form method="post" action="sungjukIns.jsp">
<table class="table">
<tr>
<th class="active">이름</th>
<td>
<input type="text" name="uname" class="form-control" maxlength="20" required autofocus>
</td>
</tr>
<tr>
<th class="active">국어</th>
<td>
<input type="number" name="kor" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력">
</td>
</tr>
<tr>
<th class="active">영어</th>
<td>
<input type="number" name="eng" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력">
</td>
</tr>
<tr>
<th class="active">수학</th>
<td>
<input type="number" name="mat" class="form-control" size="5" min="0" max="100"
placeholder="숫자입력">
</td>
</tr>
<tr>
<th class="active">주소</th>
<td>
<select name="addr" class="form-control">
<option value="Seoul">서울</option>
<option value="Jeju">제주</option>
<option value="Suwon">수원</option>
<option value="Busan">부산</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="전송" class="btn btn-default btn-lg">
<input type="reset" value="취소" class="btn btn-default btn-lg">
</td>
</tr>
</table>
</form>
</div>
</body>
</html>
- 성적 입력 결과 : sungjukIns.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukIns.jsp</title>
</head>
<body>
<h3>☆ 성적 결과 페이지 ☆</h3>
<%
//한글 인코딩
request.setCharacterEncoding("UTF-8");
//사용자가 입력한 정보를 가져와서 변수에 담기
String uname = request.getParameter("uname").trim();
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;
//out.println("요청IP: " + request.getRemoteAddr());
//Oracle DB연결 및 행추가
Connection con=null;
PreparedStatement pstmt=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);
out.println("오라클 DB 서버 연결 성공");
StringBuilder 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);
int cnt=pstmt.executeUpdate(); //추가된 행의 개수가 나옴(0이면 추가가 안된것)
if(cnt==0){
out.println("<p>성적 입력이 실패했습니다</p>");
out.println("<p><a href='javascript:history.back()'>[다시시도]</a></p>");
}else{
out.println("<script>"); //script영역을 이렇게 선언하고 사용하는 것도 가능
out.println(" alert('성적이 입력되었습니다');");
out.println(" location.href='sungjukList.jsp';");//목록페이지 이동
out.println("</script>");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
try {
if(pstmt!=null) {pstmt.close();}
} catch (Exception e) { }
try {
if(con!=null) {con.close();}
} catch (Exception e) { }
}//catch end
%>
</body>
</html>
○ 성적 리스트
- 성적 목록 : sungjukList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukList.jsp</title>
<style>
h3 {
font: 40px Montserrat, sans-serif;
font-weight: 500;
}
tbody td {
text-align: left;
}
</style>
</head>
<body>
<div class="container-fluid text-center">
<h3>★ 성적 목록 ★</h3>
<p><a href="sungjukForm.jsp">[성적쓰기]</a></p>
<table class="table table-hover">
<thead>
<tr class="active">
<th>이름</th>
<th>국어</th>
<th>영어</th>
<th>수학</th>
<th>등록일</th>
</tr>
</thead>
<tbody>
<%
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=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);
StringBuilder sql=new StringBuilder();
sql.append(" SELECT sno, uname, kor, eng, mat, wdate ");
sql.append(" FROM sungjuk ");
sql.append(" ORDER BY sno DESC ");
pstmt = con.prepareStatement(sql.toString());
rs = pstmt.executeQuery();
if(rs.next()) {//cursor가 있는지?
System.out.println("자료있음");
do {
out.print("<tr>");
out.print("<td><a href='sungjukRead.jsp?sno=" + rs.getInt("sno") + "'>" + rs.getString("uname") + "</a></td>");
out.print("<td>"+rs.getInt("kor")+"</td>");
out.print("<td>"+rs.getInt("eng")+"</td>");
out.print("<td>"+rs.getInt("mat")+"</td>");
out.print("<td>"+rs.getString("wdate").substring(0,10)+"</td>");
out.print("</tr>");
}while(rs.next()); //다음 cursor가 있는지
}else {
System.out.println("자료없음");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
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) { }
}//catch end
%>
</tbody>
</table>
</div>
</body>
</html>
○ 성적 상세보기
- 성적 상세보기 : sungjukRead.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukList.jsp</title>
<style>
h3 {
font: 40px Montserrat, sans-serif;
font-weight: 500;
}
tbody td {
text-align: left;
}
</style>
</head>
<body>
<div class="container-fluid text-center">
<!--
부모 페이지에서 primary key(sno)를 가져와서
연결받는 Read페이지를 다시 만들어 준다 -->
<h3>★ 성적 상세보기 ★</h3>
<p>
<a href="sungjukForm.jsp">[성적쓰기]</a>
<a href="sungjukList.jsp">[성적목록]</a>
</p>
<%
//예)sungjukRead.jsp?sno=21
//out.print(request.getParameter("sno"));
int sno=Integer.parseInt(request.getParameter("sno"));
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=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);
StringBuilder 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()){
%>
<table class="table">
<tr>
<th class="warning">이름</th>
<td><%=rs.getString("uname")%></td>
</tr>
<tr>
<th class="warning">국어</th>
<td><%=rs.getInt("kor")%></td>
</tr>
<tr>
<th class="warning">영어</th>
<td><%=rs.getInt("eng")%></td>
</tr>
<tr>
<th class="warning">수학</th>
<td><%=rs.getInt("mat")%></td>
</tr>
<tr>
<th class="warning">평균</th>
<td><%=rs.getInt("aver")%></td>
</tr>
<tr>
<th class="warning">주소</th>
<td><%=rs.getString("addr")%></td>
</tr>
<tr>
<th class="warning">등록일</th>
<td><%=rs.getString("wdate").substring(0,10)%></td>
</tr>
</table>
<br>
<a href="sungjukUpdate.jsp">[수정]</a>
<a href="sungjukDel.jsp?sno=<%=sno%>">[삭제]</a>
<%
}else {
System.out.println("해당 글 없음");
}//if end
} catch (Exception e) {
out.println("오라클 DB 연결 실패 : " + e);
} finally {//자원반납(순서주의)
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) { }
}//catch end
%>
</div>
</body>
</html>
'Backend > JAVA_WEB' 카테고리의 다른 글
07. JSP, 자바빈즈 (JavaBeans, 액션태그) (0) | 2022.07.05 |
---|---|
06. JSP, DB연동해서 성적 테이블 CRUD 게시판 만들기 ② (성적 삭제, 수정) (0) | 2022.07.04 |
04. JSP, form (form 관련 속성, 전송방식, 컨트롤 요소) (0) | 2022.06.29 |
03. JSP 내부객체 (out, request , response, session, application, pageContext, 페이지 실행과정) (0) | 2022.06.29 |
02. JSP (배열, 사용자 정의 함수) (0) | 2022.06.28 |
댓글