[oracle][jsp] 정보처리산업기사 과정형평가 예상문제 연습

정보처리산업기사

이번에는 정보처리산업기사 과정형평가 연습문제를 풀이해보도록 하겠습니다.

다음 내용을 선행학습 해주세요

  1. Oracle Database 11g Express Edition 설치
  2. [Oracle] table create, insert, select, update, delete ( CRUD )
  3. [Oracle] [Java] oracle과 java 연동
  4. [Oracle] [JSP] Oracle과 JSP 연동 및 CRUD

 

예상문제

전체 다운로드 - 정보처리산업기사예상문제

해당 예상문제를 기준으로 프로그램을 작성해보도록 하겠습니다.

 

문제 분석
정처기연습문제 1-1

여기서 업무요건을 살펴보면 약 5가지 정도의 수행사항을 유추할 수 있습니다.

  1. 2개의 테이블(회원정보, 매출정보)을 생성
  2. 회원정보 추가/수정/조회 기능
  3. 회원정보 sequence 기능
  4. 매출정보 조회 기능
  5. 회원정보 매출정보 JOIN 기능

위의 내용을 토대로 과제를 수행하여야 합니다.

 

회원정보 SQL 작성

정처기연습문제 1-2

회원정보 명세서를 기준으로 테이블을 생성해보도록 하겠습니다.

-- DB 연결 및 table 삭제
conn system/oracle as sysdba;
drop table member_tbl_02;
drop table money_tbl_02;
commit;

-- 테이블 생성 전 권한부여
grant create table, create sequence to system;

-- 회원정보 테이블 생성
create table member_tbl_02 (
  custno number(6),     -- 회원 번호
  custname varchar2(20),    -- 이름
  phone varchar2(13),       -- 연락처
  address varchar2(60),     -- 주소
  joindate date,        -- 등록일
  grade char(1), city char(2),  -- 등급, 거주지
  primary key(custno)       -- 기본키
);

-- 생성 후 CRUD 권한부여
grant insert,update,delete,select on member_tbl_02 to system;

-- 회원정보 테이블에 대한 시퀀스 추가
create sequence member_seq
start with 10001
increment by 1
minvalue 10001
cache 10;

-- 시퀀스 권한 추가
grant alter, select on member_seq to system;

-- 커밋
commit;

 

매출정보 SQL 작성

정처기연습문제 1-3

-- 회원 매출 정보 명세서
create table money_tbl_02 (
  custno number(6),    -- 고객번호
  salenol number(8),   -- 판매번호
  pcost number(8),     -- 단가
  amount number(4),    -- 수량
  price number(8),     -- 가격
  pcode varchar2(4),   -- 상품코드
  sdate date,          -- 판매일자
  primary key(custno, salenol)  --기본키
);

-- 권한 부여
grant insert, update, delete, select on money_tbl_02 to system;

commit;

 

샘플데이터는 나중에 프로그램에서 회원정보 추가가 완료 된 후 작성해보도록 하겠습니다.

 

JSP 작성

프로젝트 생성 방법 - 이전 포스트를 참고해주세요 (바로가기)
톰캣 다운로드 : apache-tomcat-8.0.53-windows-x64

프로젝트 작성에 필요한 폴더 및 파일 구조

폴더 : 굵은표시
파일 : 기울임

  • src : 기본생성
  • WebContent : 기본생성
    • css
      • common.css
    • META-INF : 기본생성
    • WEB-INF : 기본생성
      • lib : 기본생성
    • action.jsp  : insert, update 등의 쿼리문 처리
    • dbConnection.jsp : 데이터베이스 연결 담당
    • footer.jsp : 사이트 하단
    • header.jsp : 사이트 상단
    • index.jsp : 회원 추가
    • list.jsp : 회원 목록
    • money.jsp : 매출 정보
    • update.jsp : 회원 정보 수정

 

1. dbConnection.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
    /* 한글 깨짐 방지 */
    request.setCharacterEncoding("UTF-8");

    /* DB 접속 정보 */
    String user = "system"; // oracle 계정
    String pw = "oracle";   // oracle 비밀번호
    String url = "jdbc:oracle:thin:@localhost:1521:xe"; // 그대로 사용
    String sql = "";

    /* DB 접속 */
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, user, pw);
%>

 

 

2. header.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ include file="dbConnection.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="./css/common.css" />
<title>쇼핑몰 회원 관리 Ver 1.0</title>
</head>
<body>
    <div class="header">
        <h3 class="logo">쇼핑몰 회원 관리 Ver 1.0</h3>
        <div class="gnb">
            <ul>
                <li><a href="./index.jsp">회원등록</a></li>
                <li><a href="./list.jsp">회원목록 조회/수정</a></li>
                <li><a href="./money.jsp">회원매출조회</a></li>
                <li><a href="./index.jsp">홈으로.</a></li>
            </ul>
        </div>
    </div>
    <div class="container">
        <div class="content">

 

 

3. footer.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
        </div>
    </div>
    <footer class="footer">
        copyright &copy; 2018 junil.hwang all right reserved.
    </footer>
</body>
</html>

 

 

4. list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="header.jsp" %>
<h2>홈쇼핑 회원 등록</h2>
<table>
    <colgroup>
        <col width="10%">
        <col width="10%">
        <col width="20%">
        <col width="20%">
        <col width="20%">
        <col width="10%">
        <col width="10%">
    </colgroup>
    <thead>
        <tr>
            <th>회원번호</th>
            <th>회원성명</th>
            <th>전화번호</th>
            <th>주소</th>
            <th>가입일자</th>
            <th>고객등급</th>
            <th>거주지역</th>
        </tr>
    </thead>
    <tbody>
        <%
            /** 테스트를 위해 샘플 데이터 추가하기
             *  insert into sys.member_tbl_02 values(10001, '김행복', '010-1111-2222', '서울 동대문구 휘경1동', '20151202', 'A', '01')
             *  insert into sys.member_tbl_02 values(10002, '이축복', '010-1111-3333', '서울 동대문구 휘경2동', '20151206', 'B', '01')
             *  insert into sys.member_tbl_02 values(10003, '장믿음', '010-1111-4444', '울릉군 울릉읍 독도1리', '20151001', 'B', '30')
             *  insert into sys.member_tbl_02 values(10004, '최사랑', '010-1111-5555', '울릉군 울릉읍 독도2리', '20151113', 'A', '30')
             *  insert into sys.member_tbl_02 values(10005, '진평화', '010-1111-6666', '제주도 제주시 외나무골', '20151225', 'B', '60')
             *  insert into sys.member_tbl_02 values(10006, '차공단', '010-1111-7777', '제주도 제주시 감나무골', '20151211', 'C', '60') 
             */
             
            /* 회원정보 list 가져오기 */
            sql = "SELECT * from sys.member_tbl_02 order by custno asc";
            ResultSet res = conn.prepareStatement(sql).executeQuery();
            String custno, custname, phone, address, joindate, grade, city;
            while (res.next()) {
                custno = res.getString("custno");
                custname = res.getString("custname");
                phone = res.getString("phone");
                address = res.getString("address");
                grade = res.getString("grade");
                city = res.getString("city");
                
                /** 1) joindate에서 time 값을 제외하고 가져온다.
                 *  input  : 2018-07-29 00:00:00
                 *  output : 2018-07-29
                 */
                joindate = res.getString("joindate");
                if(joindate.length() > 10) joindate = joindate.substring(0,10);
                
                /** 2) grade의 A, B, C에 대한 실제 권한을 매핑 */ 
                switch (grade) {
                    case "A" : grade = "VIP"; break;
                    case "B" : grade = "일반"; break;
                    case "C" : grade = "7직원"; break;
                }
        %>
        <tr style="text-align:center;">
            <td><a href="./update.jsp?custno=<%=custno%>"><%= custno %></a></td>
            <td><%= custname %></td>
            <td><%= phone %></td>
            <td><%= address %></td>
            <td><%= joindate %></td>
            <td><%= grade %></td>
            <td><%= city %></td>
        </tr>
        <%
            }
        %>
    </tbody>
</table>
<%@ include file="footer.jsp" %>

 

 

5. money.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="header.jsp" %>
<h2>회원 매출 조회</h2>
<table>
    <colgroup>
        <col width="25%">
        <col width="25%">
        <col width="25%">
        <col width="25%">
    </colgroup>
    <thead>
        <tr>
            <th>회원번호</th>
            <th>회원성명</th>
            <th>고객등급</th>
            <th>매출</th>
        </tr>
    </thead>
    <tbody>
        <%
            /** 테스트를 위해 샘플 데이터 추가하기
             *  insert into sys.money_tbl_02 values(10001, 20160001, 500, 5, 2500, 'A001', '20160101')
             *  insert into sys.money_tbl_02 values(10001, 20160002, 1000, 4, 4000, 'A002', '20160101')
             *  insert into sys.money_tbl_02 values(10001, 20160003, 500, 3, 1500, 'A008', '20160101')
             *  insert into sys.money_tbl_02 values(10002, 20160004, 2000, 1, 2000, 'A004', '20160102')
             *  insert into sys.money_tbl_02 values(10002, 20160005, 500, 1, 500, 'A001', '20160103')
             *  insert into sys.money_tbl_02 values(10003, 20160006, 1500, 2, 3000, 'A003', '20160103')
             *  insert into sys.money_tbl_02 values(10004, 20160007, 500, 2, 1000, 'A001', '20160104')
             *  insert into sys.money_tbl_02 values(10004, 20160008, 300, 1, 300, 'A005', '20160104')
             *  insert into sys.money_tbl_02 values(10004, 20160009, 600, 1, 600, 'A006', '20160104')
             *  insert into sys.money_tbl_02 values(10004, 20160010, 3000, 1, 3000, 'A007', '20160106')
             */
             
            /** 매출정보 list 가져오기
             *  매출정보와 회원정보를 JOIN 및 집계하여 가져와야 한다.
             */
            sql  = "SELECT mb.custno, mb.custname, mb.grade, sum(mn.price) as total ";
            sql += "FROM sys.member_tbl_02 mb ";
            sql += "JOIN sys.money_tbl_02 mn on mb.custno = mn.custno ";
            sql += "group by (mb.custno, mb.custname, mb.grade) ";
            sql += "order by total desc";
            ResultSet res = conn.prepareStatement(sql).executeQuery();
            String custno, custname, grade, total;
            while (res.next()) {
                custno = res.getString("custno");
                custname = res.getString("custname");
                grade = res.getString("grade");
                total = res.getString("total");
                
                /* grade의 A, B, C에 대한 실제 권한을 매핑 */ 
                switch (grade) {
                    case "A" : grade = "VIP"; break;
                    case "B" : grade = "일반"; break;
                    case "C" : grade = "7직원"; break;
                }
        %>
        <tr style="text-align:center;">
            <td><%= custno %></td>
            <td><%= custname %></td>
            <td><%= grade %></td>
            <td><%= total %></td>
        </tr>
        <%
            }
        %>
    </tbody>
</table>
<%@ include file="footer.jsp" %>

 

 

6. index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="header.jsp" %>
<%
    /* next value 가져오기 */
    sql = "SELECT custno from sys.member_tbl_02 where rownum <= 1 order by custno desc";
    ResultSet res = conn.prepareStatement(sql).executeQuery();
    int custno = 10001;
    if (res.next()) custno = res.getInt(1)+1;
%>
<h2>홈쇼핑 회원 등록</h2>
<form action="./action.jsp" method="post">
    <!-- action.jsp에서 actino 값을 통하여 sql을 handling 한다. -->
    <input type="hidden" name="action" value="insert">
    <table>
        <colgroup>
            <col width="20%">
            <col width="80%">
        </colgroup>
        <tr>
            <th>회원번호(자동발생)</th>
            <td><input type="text" size="20" value="<%= custno %>"></td>
        </tr>
        <tr>
            <th>회원성명</th>
            <td><input type="text" name="custname" size="20"></td>
        </tr>
        <tr>
            <th>회원전화</th>
            <td><input type="text" name="phone" size="30"></td>
        </tr>
        <tr>
            <th>회원주소</th>
            <td><input type="text" name="address" size="40"></td>
        </tr>
        <tr>
            <th>가입일자</th>
            <td><input type="text" name="joindate" size="20"></td>
        </tr>
        <tr>
            <th>
                고객등급
                <br>(A:VIP, B:일반, C:직원)
            </th>
            <td><input type="text" name="grade" size="20"></td>
        </tr>
        <tr>
            <th>도시코드</th>
            <td><input type="text" name="city" size="20"></td>
        </tr>
    </table>
    <div class="btn_group">
        <button type="submit">등록</button>
        <button type="button">조회</button>
    </div>
</form>
<%@ include file="footer.jsp" %>

 

 

7. update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="header.jsp" %>
<%
    /* data 가져오기 */
    String custno = request.getParameter("custno");
    String custname, phone, address, joindate, grade, city;
    
    sql = "SELECT * from sys.member_tbl_02 where custno = "+custno;
    ResultSet res = conn.prepareStatement(sql).executeQuery();
    res.next();
    
    custname = res.getString("custname");
    phone = res.getString("phone");
    address = res.getString("address");
    joindate = res.getString("joindate");
    if(joindate.length() > 10) joindate = joindate.substring(0, 10);
    grade = res.getString("grade");
    city = res.getString("city");
%>
<h2>홈쇼핑 회원 등록</h2>
<form action="./action.jsp" method="post">
    <input type="hidden" name="action" value="update">
    <table>
        <colgroup>
            <col width="20%">
            <col width="80%">
        </colgroup>
        <tr>
            <th>회원번호(자동발생)</th>
            <td><input type="text" name="custno" size="20" value="<%= custno %>" readonly></td>
        </tr>
        <tr>
            <th>회원성명</th>
            <td><input type="text" name="custname" size="20" value="<%= custname %>"></td>
        </tr>
        <tr>
            <th>회원전화</th>
            <td><input type="text" name="phone" size="30" value="<%= phone %>"></td>
        </tr>
        <tr>
            <th>회원주소</th>
            <td><input type="text" name="address" size="40" value="<%= address %>"></td>
        </tr>
        <tr>
            <th>가입일자</th>
            <td><input type="text" name="joindate" size="20" value="<%= joindate %>"></td>
        </tr>
        <tr>
            <th>
                고객등급
                <br>(A:VIP, B:일반, C:직원)
            </th>
            <td><input type="text" name="grade" size="20" value="<%= grade %>"></td>
        </tr>
        <tr>
            <th>도시코드</th>
            <td><input type="text" name="city" size="20" value="<%= city %>"></td>
        </tr>
    </table>
    <div class="btn_group">
        <button type="submit">수정</button>
        <button type="button" onclick="history.back();">목록</button>
    </div>
</form>
<%@ include file="footer.jsp" %>

 

 

8. action.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="dbConnection.jsp" %>
<%
    String action = request.getParameter("action");
    String move_url = "./";
    PreparedStatement pstmt = null;
    
    /**
     * index.jsp와 update.jsp에서 form을 전송할 때
     * action을 hidden값으로 전송한다. 해당 값에 맞는 명령을 수행하도록  작성 
     */
    switch (action) {
    case "insert" :
        sql = "INSERT INTO sys.member_tbl_02 values(sys.member_seq.nextval, ?, ?, ?, ?, ?, ?)";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, request.getParameter("custname"));
        pstmt.setString(2, request.getParameter("phone"));
        pstmt.setString(3, request.getParameter("address"));
        pstmt.setString(4, request.getParameter("joindate"));
        pstmt.setString(5, request.getParameter("grade"));
        pstmt.setString(6, request.getParameter("city"));
        break;
    case "update" :
        sql  = "update sys.member_tbl_02 SET custname = ?, phone = ?, address = ?, joindate = ?, grade = ?, city = ? where custno = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, request.getParameter("custname"));
        pstmt.setString(2, request.getParameter("phone"));
        pstmt.setString(3, request.getParameter("address"));
        pstmt.setString(4, request.getParameter("joindate"));
        pstmt.setString(5, request.getParameter("grade"));
        pstmt.setString(6, request.getParameter("city"));
        pstmt.setInt(7, Integer.parseInt(request.getParameter("custno")));
        move_url = "./list.jsp";
        break;
    }
    //out.println(sql.trim());
    //if(true) return;
    pstmt.executeQuery();
%>
<script>
    alert('완료되었습니다.');
    location.replace("<%=move_url%>");
</script>

 

전체 소스코드 다운로드 : JspOracle0729

다른 문제 : 정보처리산업기사과정형평가출제기준