본문 바로가기
Backend/JAVA_WEB

05. JSP, DB연동해서 성적 테이블 CRUD 게시판 만들기 ① (성적 입력, 리스트, 상세보기)

by 개발개발빈이 2022. 7. 3.

○ 성적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>
                &nbsp;&nbsp;
                <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

관련글

댓글

티스토리툴바