[정보처리산업기사] 연습문제 – 수강신청도우미

정보처리산업기사\

이번에는 간단한 수강신청도우미 ( 과목 / 강사 ) 프로그램을 만들어보도록 하겠습니다.

연습문제 다운로드 : 정보처리 산업기사 연습문제 3(수정)

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

  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
  5. [oracle][jsp] 정보처리산업기사 과정형평가 예상문제 연습
  6. [정보처리산업기사] 연습문제 - 게시판 및 댓글

전체 솔루션 다운로드 : Course0808.zip

 

문제분석

  1. 교과목 테이블 작성
  2. 강사 테이블 작성
  3. 강사 테이블에 대한 시퀀스 추가
  4. 교과목 샘플 데이터 추가
  5. 강사 샘플 데이터 추가
  6. 교과목 목록, 수정, 삭제 구현
  7. 교과목 목록에서 교과목 테이블과 강사 테이블을 join하여 강사의 이름 출력

 

테이블 명세서

과목 테이블 명세서 ( course_tbl )

과목 테이블 명세서

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

-- 과목 정보 테이블 생성
create table course_tbl (
    id varchar2(5) primary key,
    name varchar2(40),
    credit number(6),
    lecturer varchar2(10),
    week number(2),
    start_hour number(4),
    end_end number(4)
);

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

commit;

 

 

강사 테이블 명세서 ( lecturer_tbl )

-- 강사 정보 테이블 생성
create table lecturer_tbl (
    idx number(6) primary key,
    name varchar2(10),
    major varchar2(40),
    field varchar2(40)
);

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

-- 강사 정보 대한 시퀀스 추가
create sequence lecturer_seq
start with 1
increment by 1
minvalue 1
cache 10;

commit;

 

과목 정보 샘플 데이터

과목 데이터

-- 교과목 정보 추가
insert into sys.course_tbl values('10001', '프로그래밍', '2', '1', '1', '0900', '1100');
insert into sys.course_tbl values('10002', '객체지향 프로그래밍', '2', '2', '2', '0900', '1200');
insert into sys.course_tbl values('10003', '자료구조', '3', '4', '3', '0900', '1200');
insert into sys.course_tbl values('10004', '알고리즘', '3', '4', '4', '0900', '1200');
insert into sys.course_tbl values('20001', '시스템 프로그래밍', '2', '5', '1', '1300', '1600');
insert into sys.course_tbl values('20002', '운영체제', '3', '5', '2', '1500', '1800');
insert into sys.course_tbl values('20003', '오토마타와 컴파일러', '3', '5', '3', '1330', '1630');
insert into sys.course_tbl values('30001', '소프트웨어 공학', '2', '3', '4', '1330', '1530');
insert into sys.course_tbl values('30002', '시스템 분석 및 설계', '3', '3', '5', '0900', '1200');
insert into sys.course_tbl values('40001', '데이터베이스', '3', '2', '5', '1300', '1600');

commit;

 

강사 정보 샘플 데이터

강사 데이터

-- 강사 정보 추가
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'김교수','소프트웨어공학','알고리즘');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'이교수','소프트웨어공학','인공지능');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'박교수','소프트웨어공학','소프트웨어공학');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'우교수','소프트웨어공학','알고리즘');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'최교수','응용컴퓨터공학','임베디드 시스템');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'강교수','응용컴퓨터공학','멀티미디어');
insert into sys.lecturer_tbl values(lecturer_seq.nextval,'황교수','모바일시스템공학','네트워크');

commit;

 

 

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 = "";
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, user, pw);
%>

dbConnection.jsp를 모든 페이지 상단에서 include 합니다. 더 정확히 표현하자면, header.jsp에서 dbConnection.jsp를 include하고, header를 모든 페이지에서 include 함으로써 중복 작성을 최대한 줄일 수 있습니다.

 

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">
<title>수강신청 도우미 사이트</title>
</head>
<body>
    <div class="header">
        <h3 class="logo">수강신청 도우미 사이트</h3>
    </div>
    <div class="container">
        <div class="content">

header에서 사이트 상단에 공통적으로 보여질 내용을 작성합니다. 사실 현재 과제에선 딱히 필요 없으나, 형식적으로 작성하였습니다.

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>

footer에는 copyright를 명시합니다.

 

4. 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 count(*) as cnt from sys.course_tbl";
    ResultSet res = conn.prepareStatement(sql).executeQuery();
    int cnt = 0;
    if (res.next()) cnt = res.getInt(1);
%>
<h2>교과목 목록</h2>
<p>총 <%= cnt %>개의 교과목이 있습니다.</p>
<table>
    <colgroup>
        <col width="10%">
        <col width="30%">
        <col width="10%">
        <col width="10%">
        <col width="10%">
        <col width="10%">
        <col width="10%">
        <col width="10%">
    </colgroup>
    <thead>
        <tr>
            <th>과목코드</th>
            <th>과목명</th>
            <th>학점</th>
            <th>담당강사</th>
            <th>요일</th>
            <th>시작시간</th>
            <th>종료시간</th>
            <th>관리</th>
        </tr>
    </thead>
    <tbody>
        <%
            /* 회원정보 list 가져오기 */
            sql  = "SELECT c.*, l.name as lecturer_name "
                 + "FROM sys.course_tbl c "
                 + "JOIN sys.lecturer_tbl l on c.lecturer = l.idx "
                 + "order by c.id asc";
            res = conn.prepareStatement(sql).executeQuery();
            String id, name, credit, lecturer_name, week, start_hour, end_end;
            while (res.next()) {
                id            = res.getString("id");
                name          = res.getString("name");
                credit        = res.getString("credit");
                lecturer_name = res.getString("lecturer_name");
                week          = res.getString("week");
                start_hour    = res.getString("start_hour");
                end_end       = res.getString("end_end");
        %>
        <tr style="text-align:center;">
            <td><%= id %></td>
            <td><%= name %></td>
            <td><%= credit %></td>
            <td><%= lecturer_name %></td>
            <td><%= week %></td>
            <td><%= start_hour %></td>
            <td><%= end_end %></td>
            <td>
                <a href="./update.jsp?id=<%= id %>">수정</a> /
                <a href="./delete.jsp?id=<%= id %>" onclick="if(!confirm('정말로 삭제하시겠습니까?')) return false;">삭제</a>
            </td>
        </tr>
        <%
            }
        %>
    </tbody>
</table>
<div class="btn_group right">
    <button type="button" onclick="location.href = './write.jsp'">작성</button>
</div>
<%@ include file="footer.jsp" %>

index.jsp는 과목 목록을 조회하는 페이지입니다. 그리고 하단에는 작성 페이지로 이동할 수 있는 버튼을 만들어서 다른 페이지와 상호작용을 해야 합니다. 자세한 내용은 주석을 확인해주세요.

여기서 핵심은 교과목 정보와 강사 정보를 joni 하는 구문입니다. 다음과 같은 과정으로 이해하면 편합니다.

  1. 교과목 정보 가져오기

    SELECT sys.course_tbl.* FROM sys.course_tbl;

  2. 교과목 정보의 강사 idx에 대하여 JOIN

    SELECT sys.course_tbl.*, sys.lecturer_tbl.name
    FROM sys.course_tbl
    JOIN sys.lecturer_tbl on sys.course_tbl.lecturer = sys.lecturer_tbl.idx

  3. 테이블 명을 치환

    SELECT c.*, l.name
    FROM sys.course_tbl c
    JOIN sys.lecturer_tbl l on c.lecturer = l.idx

  4. 컬럼명 치환

    SELECT c.*, l.name as lecturerName
    FROM sys.course_tbl c
    JOIN sys.lecturer_tbl l on c.lecturer = l.idx

  5. 오름차순 정렬

    SELECT c.*, l.name as lecturerName
    FROM sys.course_tbl c
    JOIN sys.lecturer_tbl l on c.lecturer = l.idx
    order by c.id asc

그리고 따로 상세정보를 조회하는 페이지가 없기 때문에 list에서 update와 delete를 처리할 수 있도록 링크를 만들어줘야 합니다.

 

5. write.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 * FROM sys.lecturer_tbl order by idx asc";
    ResultSet res = conn.prepareStatement(sql).executeQuery();
    String idx, name;
%>
<div>
    <form action="./action.jsp" method="post">
        <fieldset><legend>교과목 추가</legend>
            <input type="hidden" name="action" value="insert">
            <ul>
                <li>
                    <label>
                        <span>교과목 코드 :</span>
                        <input type="text" name="id" size="20" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>과목명 :</span>
                        <input type="text" name="name" size="20" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>학점 :</span>
                        <input type="text" name="credit" size="20" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>담당강사 :</span>
                        <select name="lecturer" required>
                            <option value="">담당강사 선택</option>
                            <%
                                while (res.next()) {
                                    idx = res.getString("idx");
                                    name = res.getString("name");
                            %>
                            <option value="<%= idx %>"><%= name %></option>
                            <% }  %>
                        </select>
                    </label>
                </li>
                <li>
                    <span>요일 :</span>
                    <label><input type="radio" name="week" value="1" checked> 월</label>
                    <label><input type="radio" name="week" value="2"> 화</label>
                    <label><input type="radio" name="week" value="3"> 수</label>
                    <label><input type="radio" name="week" value="4"> 목</label>
                    <label><input type="radio" name="week" value="5"> 금</label>
                    <label><input type="radio" name="week" value="6"> 토</label>
                </li>
                <li>
                    <label>
                        <span>시작 :</span>
                        <input type="text" name="start_hour" size="20" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>종료 :</span>
                        <input type="text" name="end_end" size="20" required>
                    </label>
                </li>
            </ul>
            <div class="btn_group">
                <button type="button" onclick="history.back(); return false;">목록</button>
                <button type="submit">완료</button>
            </div>
        </fieldset>
    </form>
</div>
<%@ include file="footer.jsp" %>

write.jsp에서 핵심은 select 태그로 강사 목록을 선택하는 부분입니다.

따라서 강사 목록에 대한 쿼리문을 작성해야 하며, select의 option 부분에서 while로 반복하여 강사 정보를 출력합니다. value값은 강사 데이터의 idx 값이 들어가며, 실제로 보여지는 것은 강사 데이터의 name이 보여집니다.

form을 작성 후 전송하면 action.jsp에서 일괄적으로 처리합니다. 이 때 name="action"에서 지정한 값을 통하여 driven 할 수 있도록 작성할 것입니다.

 

6. update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="header.jsp" %>
<%
    /* next value 가져오기 */
    String id, name, credit, lecturer, week, start_hour, end_end, lecturer_idx, lecturer_name, sel;
    id  = request.getParameter("id");
    sql = "SELECT * FROM sys.course_tbl where id = "+id;
    ResultSet res;
    res = conn.prepareStatement(sql).executeQuery();
    res.next();
    name       = res.getString("name");
    credit     = res.getString("credit");
    lecturer   = res.getString("lecturer");
    week       = res.getString("week");
    start_hour = res.getString("start_hour");
    end_end    = res.getString("end_end");

    sql = "SELECT * FROM sys.lecturer_tbl order by idx asc";
    res = conn.prepareStatement(sql).executeQuery();
%>
<div>
    <form action="./action.jsp" method="post">
        <fieldset><legend>교과목 수정</legend>
            <input type="hidden" name="action" value="update">
            <ul>
                <li>
                    <label>
                        <span>교과목 코드 :</span>
                        <input type="text" name="id" size="20" value="<%= id %>" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>과목명 :</span>
                        <input type="text" name="name" size="20" value="<%= name %>" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>학점 :</span>
                        <input type="text" name="credit" size="20" value="<%= credit %>" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>담당강사 :</span>
                        <select name="lecturer" required>
                            <option value="">담당강사 선택</option>
                            <%
                                while (res.next()) {
                                    lecturer_idx = res.getString("idx");
                                    lecturer_name = res.getString("name");
                                    sel = "";
                                    if (lecturer_idx.equals(lecturer)) sel = " selected";
                            %>
                            <option value="<%= lecturer_idx %>"<%=sel %>><%= lecturer_name %></option>
                            <% }  %>
                        </select>
                    </label>
                </li>
                <li>
                    <span>요일 :</span>
                    <label><input type="radio" name="week" value="1"<%= week.equals("1") ? " checked" : "" %>> 월</label>
                    <label><input type="radio" name="week" value="2"<%= week.equals("2") ? " checked" : "" %>> 화</label>
                    <label><input type="radio" name="week" value="3"<%= week.equals("3") ? " checked" : "" %>> 수</label>
                    <label><input type="radio" name="week" value="4"<%= week.equals("4") ? " checked" : "" %>> 목</label>
                    <label><input type="radio" name="week" value="5"<%= week.equals("5") ? " checked" : "" %>> 금</label>
                    <label><input type="radio" name="week" value="6"<%= week.equals("6") ? " checked" : "" %>> 토</label>
                </li>
                <li>
                    <label>
                        <span>시작 :</span>
                        <input type="text" name="start_hour" value="<%= start_hour %>" size="20" required>
                    </label>
                </li>
                <li>
                    <label>
                        <span>종료 :</span>
                        <input type="text" name="end_end" value="<%= end_end %>" size="20" required>
                    </label>
                </li>
            </ul>
            <div class="btn_group">
                <button type="button" onclick="history.back(); return false;">목록</button>
                <button type="submit">완료</button>
            </div>
        </fieldset>
    </form>
</div>
<%@ include file="footer.jsp" %>

update.jsp에서 기존 과목 정보를 불러온 후 input에 출력해줘야 합니다.

이 때, select와 radio로 저장해놓은 정보는 value에 넣어봤자 매칭 되지 않기 때문에, checked와 selected를 통하여 매핑을 해야 합니다.

 

7. 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");
    PreparedStatement pstmt = null;
    
    /**
     * write.jsp, view.jsp, update.jsp에서 form을 전송할 때
     * action을 hidden값으로 전송한다. 해당 값에 맞는 명령을 수행하도록  작성 
     */
    switch (action) {
    case "insert" :
        sql   = "INSERT INTO sys.course_tbl values(?,?,?,?,?,?,?)";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, request.getParameter("id"));
        pstmt.setString(2, request.getParameter("name"));
        pstmt.setString(3, request.getParameter("credit"));
        pstmt.setString(4, request.getParameter("lecturer"));
        pstmt.setString(5, request.getParameter("week"));
        pstmt.setString(6, request.getParameter("start_hour"));
        pstmt.setString(7, request.getParameter("end_end"));
        break;
    case "update" :
        sql   = "update sys.course_tbl SET name = ?, credit = ?, lecturer = ?, week = ?, start_hour = ?, end_end = ? where id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, request.getParameter("name"));
        pstmt.setString(2, request.getParameter("credit"));
        pstmt.setString(3, request.getParameter("lecturer"));
        pstmt.setString(4, request.getParameter("week"));
        pstmt.setString(5, request.getParameter("start_hour"));
        pstmt.setString(6, request.getParameter("end_end"));
        pstmt.setString(7, request.getParameter("id"));
        break;
    }
    pstmt.executeQuery();
%>
<script>
    alert('완료되었습니다.');
    location.replace('./');
</script>

action.jsp에서 모든 form 전송을 처리합니다.

처리 완료 후 script 부분에서 페이지 이동을 담당합니다.

 

8. delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ include file="dbConnection.jsp" %>
<%
    String id = request.getParameter("id");
    sql = "DELETE FROM sys.course_tbl where id = "+id;
    conn.prepareStatement(sql).executeUpdate();
%>
<script>
    alert('완료되었습니다.');
    location.replace('./');
</script>

delete는 따로 form을 전송할 필요 없이 바로 실행해주면 됩니다. 여기에서 과목 데이터의 삭제를 담당합니다.