성적테이블 CRUD
Create - 행추가 insert
Read - 조회 select
Update - 수정 update
Delete - 삭제 delete
[성적테이블 CRUD]
sungjukForm.jsp 성적쓰기 -> sungjukIns.jsp
sno sno
sungjukList.jsp 성적목록 --------> sungjukRead.jsp 상세보기 (수정, 삭제할 수 있는 부모 jsp) -------> 삭제
sno
-------> 수정
목록과 상세보기는 연결되어 있는 페이지처럼 보이는 것이지만, 실제로는 다 각자 존재하는 페이지다.
[페이지이동]
<a href=""></a>
location.href=""
<form action="">
<jsp:forward page=""></jsp:forward>
1. 성적 폼 생성
<%@ page contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukForm.jsp</title>
<style>
/* .line{border:3px solid ; } */
</style>
</head>
<body>
<h1>*성적 입력 폼*</h1>
<p><a href="sungjukList.jsp">[성적목록]</a></p>
<form method="post" action="sungjukIns.jsp">
<table border='1'>
<tr>
<th>이름</th>
<td>
<input type="text" name="uname" maxlength="20" required autofocus> <!-- autofocus는 폼당 하나만 가능 -->
</td>
</tr>
<tr>
<th>국어</th>
<td>
<input type="number" name="kor" size="5" min="0" max="100" placeholder="숫자입력">
</td>
</tr>
<tr>
<th>영어</th>
<td>
<input type="number" name="eng" size="5" min="0" max="100" placeholder="숫자입력">
</td>
</tr>
<tr>
<th>수학</th>
<td>
<input type="number" name="mat" size="5" min="0" max="100" placeholder="숫자입력">
</td>
</tr>
<tr>
<th>주소</th>
<td>
<select name="addr">
<option value="Seoul">서울</option>
<option value="Jeju">제주</option>
<option value="Busan">부산</option>
<option value="Suwon">수원</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="전송">
<input type="reset" value="취소">
</td>
</tr>
</table>
</form>
</body>
</html>
2. 성적 INSERT 생성
<%@ page contentType="text/html; charset=UTF-8"%>
<%@page import="java.sql.PreparedStatement" %>
<%@page import="java.sql.DriverManager" %>
<%@page import="java.sql.Connection" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukIns.jsp</title>
</head>
<body>
<h1>*성적 결과*</h1>
<p><a href="sungjukForm.jsp">[성적쓰기]</a></p>
<%
//한글 인코딩
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").trim();
//평균구하기
int aver=(kor+eng+mat)/3;
//출력
out.println("이름 : "+uname+"<hr>");
out.println("국어 : "+kor+"<hr>");
out.println("영어 : "+eng+"<hr>");
out.println("수학 : "+mat+"<hr>");
out.println("평균 : "+aver+"<hr>");
out.println("주소 : "+addr+"<hr>");
out.println("요청IP : "+request.getRemoteAddr()+"<hr>");
//Oracle DB 저장----------------------------------------------------------
try{
//1)Oracle 드라이버 (ojdbc.jar)
Class.forName("oracle.jdbc.OracleDriver");
//2)Oracle DB 연결
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe","java0514","1234");
PreparedStatement pstmt=null;
//3)SQL문 작성
StringBuilder sql=new StringBuilder();
sql.append(" INSERT INTO sungjuk(sno,uname,kor,eng,mat,aver,addr,wdate) ");
sql.append(" VALUES((SELECT NVL(MAX(sno),0)+1 FROM sungjuk) ");
sql.append(" ,?,?,?,?,?,?,sysdate) ");
//4)SQL문 변환
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);
//5)SQL문 실행
int cnt=pstmt.executeUpdate();
if(cnt==0){
out.println("<p>성적 입력에 실패했습니다</p>");
out.println("<p><a href='javascript:history.back()'>[다시시도]</a></p>");
}else{
out.println("<script>");
out.println(" alert('성적이 입력되었습니다.');");
out.println(" location.href='sungjukList.jsp';"); //목록페이지 이동
out.println("</script>");
}//if end
}catch(Exception e){
out.println("실패!"+e);
}//try end
%>
</body>
</html>
3. 성적 LIST 생성
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukList.jsp</title>
</head>
<body>
<h1>*성적 목록*</h1>
<p><a href="sungjukForm.jsp">[성적쓰기]</a></p>
<table border='1'>
<tr>
<th>이름</th>
<th>국어</th>
<th>영어</th>
<th>수학</th>
<th>평균</th>
<th>등록일</th>
</tr>
<%
try{
Class.forName("oracle.jdbc.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe","java0514","1234");
StringBuilder sql=new StringBuilder();
sql.append(" SELECT sno,uname,kor,eng,mat,aver,wdate ");
sql.append(" FROM sungjuk ");
sql.append(" ORDER BY wdate DESC ");
PreparedStatement pstmt=con.prepareStatement(sql.toString());
ResultSet rs=pstmt.executeQuery();
if(rs.next()){ //cursor가 있나?
do{
%>
<tr>
<td><a href='sungjukRead.jsp?sno=<%=rs.getInt("sno")%>'><%=rs.getString("uname")%></a></td>
<td><%=rs.getInt("kor")%></td>
<td><%=rs.getInt("eng")%></td>
<td><%=rs.getInt("mat")%></td>
<td><%=rs.getInt("aver")%></td>
<td><%=rs.getString("wdate")%></td>
</tr>
<%
}while(rs.next());
}else{
out.println("<tr>");
out.println(" <td colspan='7'>글없음!</td>");
out.println("</tr>");
}//if end
}catch(Exception e){
out.println("실패 : "+e);
}//try end
%>
</table>
</body>
</html>
4. 성적 READ 생성
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukRead.jsp</title>
</head>
<body>
<h1>*성적 상세보기*</h1>
<p>
<a href="sungjukForm.jsp">[성적쓰기]</a>
<a href="sungjukList.jsp">[성적목록]</a>
</p>
<%
int sno=Integer.parseInt(request.getParameter("sno"));
out.print(sno);
%>
</body>
</html>
LIST 페이지에서 sno를
DBOpen 이라는 클라스를 만들어서 굳이 매번 db를 연결해주지않고 class로 만들어서 넣어준다.
[성적 수정 sungjukUpdate.jsp]
수정하고자 하는 행을 가져와서 -> select문
-> sungjukRead.jsp
폼에 출력하고-> sungjukForm.jsp
해당 데이터를 수정한다. -> sungjukUpdateProc.jsp
-> update문
'공부 > JSP' 카테고리의 다른 글
| 8월23일 - JSP 3 : 기본문법 (0) | 2019.08.23 |
|---|---|
| 8월22일 - JSP 2 : 기본문법 (배열, 메소드) (0) | 2019.08.22 |
| 8월21일 - JSP 1 (0) | 2019.08.21 |
