JSP-테이블 CRUD

임재헌·2023년 4월 6일

JSP

목록 보기

14/33

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukForm</title>
<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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<h3>성적 입력 폼</h3>
<p>
<a href="sungjukList.jsp">[성적목록]</a></p>
<form action="sungjukIns.jsp" name="sungjukfrm" id="sungjukfrm" method="post">
<table class="table table-hover">
<tr>
<th class="success">이름</th>
<td> <input type="text" name="uname" id="uname" maxlength="50" required autofocus></td>
</tr>

<tr>
<th class="success">국어</th>
<td> <input type="number" name="kor" id="kor" size=5 min="0" max="100" placeholder="국어점수"></td>
</tr>
<tr>
<th class="success">영어</th>
<td> <input type="number" name="eng" id="eng" size=5 min="0" max="100" placeholder="영어점수"></td>
</tr>

<tr>
<th class="success">수학</th>
<td> <input type="number" name="mat" id="mat" size=5 min="0" max="100" placeholder="수학점수"></td>
</tr>

<tr>
<th class="success">주소</th>
<td>
<select name="addr" id="addr">
<option value="Seoul">서울</option>
<option value="Suwon">수원</option>
<option value="Busan">부산</option>
<option value="Jeju">제주</option>
</select>
</td>
</tr>

<tr>
<td colspan="2" align="center">
<input class="btn btn-primary" type="submit" value="전송">
<input class="btn btn-secondary" type="reset" value="취소">
 </td>
</tr>
</table>
</form>
</body>
</html>

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<meta charset="UTF-8">
<title>sungjukIns</title>
</head>
<body>
<h3>성적 결과 페이지</h3>
<%
//한글 인코딩
request.setCharacterEncoding("UTF-8");
//사용자가 입력한 정보를 가져와서 변수에 담기
String name=request.getParameter("uname");
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"; 
	
	//2.드라이버 로딩
	Class.forName(driver);
	
	//3.오라클 db 서버 연결
	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,name);
	pstmt.setInt(2, kor);
	pstmt.setInt(3, eng);
	pstmt.setInt(4, mat);
	pstmt.setInt(5, aver);
	pstmt.setString(6, addr);
	
	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>");
	}
	
}catch(Exception e){
	out.println("오라클 연결 실패"+e);
}finally{
	try{
		if(pstmt!=null){
			pstmt.close();
		}
	}catch(Exception e){}
	
	try{
		if(con!=null){
			con.close();}
	}catch(Exception e){}
		
}
%>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<title>sungjukRead</title>
</head>
<body>
<h3>성적 상세보기</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>
<% 
//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);
	//out.println("오라클 DB연결 성공");
	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="danger">이름</th>
	<td><%=rs.getString("uname") %></td>
	</tr>
	<tr>
	<th class="danger">국어</th>
	<td><%=rs.getInt("kor") %></td>
	</tr>
	<tr>
	<th class="danger">영어</th>
	<td><%=rs.getInt("eng") %></td>
	</tr>
	<tr>
	<th class="danger">수학</th>
	<td><%=rs.getInt("mat") %></td>
	</tr>
	<tr>
	<th class="danger">평균</th>
	<td><%=rs.getInt("aver") %></td>
	</tr>
	<tr>
	<th class="danger">주소</th>
	<td><%=rs.getString("addr")%>
	<% 
	String addr= rs.getString("addr");
	if(addr.equals("Seoul")){
		out.print("서울");
	}else if(addr.equals("Jeju")){
		out.print("제주");
	}else if(addr.equals("Suwon")){
		out.print("수원");
	}else if(addr.equals("Busan")){
		out.print("부산");
	}
	
	%>
	</td>
	</tr>
	<tr>
	<th class="danger">작성일</th>
	<td><%=rs.getString("wdate") %></td>
	</tr>
	</table>
	<br><br>
	<a href="sungjukUpdate.jsp?sno=<%=sno%>">수정</a>
	&nbsp; &nbsp;
	<a href="sungjukDel.jsp?sno=<%=sno%>">삭제</a>
<%		
	}else{
		out.println("해당글 없음");
	}
	
}catch(Exception e){
	out.println("오라클 연결 실패"+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){}
		
}
%>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
      <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukDel</title>
</head>
<body>

<h3>성적 삭제</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>

<%

//sno값을 가져옴
int sno= Integer.parseInt(request.getParameter("sno"));

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

	StringBuilder sql=new StringBuilder();
	sql.append(" Delete from sungjuk ");
	sql.append(" where sno=? ");

	pstmt=con.prepareStatement(sql.toString());
	pstmt.setInt(1, sno);
	
	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>");
	}
	
}catch(Exception e){
	out.println("오라클 연결 실패"+e);
	
}finally{
	
	try{
		if(pstmt!=null){
			pstmt.close();
		}
	}catch(Exception e){}
	
	try{
		if(con!=null){
			con.close();}
	}catch(Exception e){}
		
}
%>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>sungjukList</title>
<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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<h3>성적 목록</h3>
<p><a href="sungjukForm.jsp">[성적쓰기]</a></p>

<table class="table">
<tr class="info" >
<th>이름</th>
<th>국어</th>
<th>영어</th>
<th>수학</th>
<th>등록일</th>
</tr>
<% 
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);
	//out.println("오라클 DB연결 성공");
	
	StringBuilder sql=new StringBuilder();
	sql.append(" select sno, uname, kor, eng, mat, wdate ");
	sql.append(" from sungjuk ");
	sql.append(" order by wdate desc ");
	
	pstmt=con.prepareStatement(sql.toString());
	rs=pstmt.executeQuery();
	if(rs.next()){
		do{
%>
		<tr class="success">
		<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.getString("wdate").substring(0,10) %></td>
		</tr>


<% 			
		}while(rs.next());
	}else{
		out.println("<tr>");
		out.println("<td colspan='5'>글 없음</td>");
		out.println("</tr>");
	}
	
}catch(Exception e){
	out.println("오라클 연결 실패"+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){}
		
}
%>
</table>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
     <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<!DOCTYPE html>
<html>
<head>
<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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<meta charset="UTF-8">
<title>sungjukUpdate</title>
</head>
<body>
<h3>성적 업데이트</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>
<%
//update에서 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();
	
	//1.수정할 데이터를 먼저 가져온다
	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()){
	//2.1의 내용을 폼에 출력 sungjukForm 참조
%>

<form action="sungjukUpdateProc.jsp" name="sungjukfrm" id="sungjukfrm" method="post">
<!--3.sno값도 form으로 정보를 전송할때 필요하기 때문에 hidden 속성으로 사용자에게는 보이지 않도록 해서
전달시킨다  -->
<input type="hidden" name="sno" value="<%=sno%>">
<table class="table table-hover">
<tr>
<th class="success">이름</th>
<td> <input type="text" name="uname" id="uname" value="<%=rs.getString("uname")%>" maxlength="50" required autofocus></td>
</tr>

<tr>
<th class="success">국어</th>
<td> <input type="number" name="kor" id="kor" value="<%=rs.getInt("kor")%>" size=5 min="0" max="100" placeholder="국어점수"></td>
</tr>
<tr>
<th class="success">영어</th>
<td> <input type="number" name="eng" id="eng" value="<%=rs.getInt("eng")%>" size=5 min="0" max="100" placeholder="영어점수"></td>
</tr>

<tr>
<th class="success">수학</th>
<td> <input type="number" name="mat" id="mat" value="<%=rs.getInt("mat")%>" size=5 min="0" max="100" placeholder="수학점수"></td>
</tr>

<tr>
<th class="success">주소</th>
<td>
<% String addr=rs.getString("addr"); %>
<select name="addr" id="addr">
<option value="Seoul" <%if(addr.equals("Seoul")){out.print("selected");} %>>서울</option>
<option value="Suwon" <%if(addr.equals("Suwon")){out.print("selected");} %>>수원</option>
<option value="Busan" <%if(addr.equals("Busan")){out.print("selected");} %>>부산</option>
<option value="Jeju" <%if(addr.equals("Jeju")){out.print("selected");} %>>제주</option>
</select>
</td>
</tr>

<tr>
<td colspan="2" align="center">
<input class="btn btn-primary" type="submit" value="수정">
<input class="btn btn-secondary" type="reset" value="취소">
 </td>
</tr>
</table>
</form>

<%	
	}else{
		out.println("해당 글 없음");
	}
	

}catch(Exception e){
	out.println("오라클 연결 실패"+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){}
		
}
%>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>

<!DOCTYPE html>
<html>
<head>
<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.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<meta charset="UTF-8">
<title>sungjukUpdateProc</title>
</head>
<body>
<h3>성적수정결과</h3>
<p>
<a href="sungjukForm.jsp">성적쓰기</a>
<a href="sungjukList.jsp">성적목록</a>
</p>
<%

int sno= Integer.parseInt(request.getParameter("sno"));

request.setCharacterEncoding("UTF-8");
String uname=request.getParameter("uname");
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;


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

	StringBuilder sql=new StringBuilder();
	
	sql.append(" update sungjuk ");
	sql.append(" set uname=?,kor=?,eng=?,mat=?,aver=?,addr=?,wdate=sysdate ");
	sql.append(" where sno=? ");
	
	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);
	pstmt.setInt(7,sno);
	
	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>");
	}
	
}catch(Exception e){
	out.println("오라클 연결 실패"+e);
	
}finally{
	
	try{
		if(pstmt!=null){
			pstmt.close();
		}
	}catch(Exception e){}
	
	try{
		if(con!=null){
			con.close();}
	}catch(Exception e){}
		
}
%>
</body>
</html>

임재헌

이전 포스트

JSP-내장객체 메소드(request)

다음 포스트

JSP-테이블 CRUD

JSP

JSP-내장객체 메소드(request)

JSP-JAVA Bean

0개의 댓글