(105)[Spring]16일차: 🧨🎉✨F_MybatisWork03: 학생, 성적 관리✨🎉🧨

1. 2024.1.25(목)

F_MybatisWork03

ㄴ/MybatisWork03/src/com/test/mybatis

ㄴㄴGradeController.java

/* ==================================================
    #09 GradeController.java
    - Controller
=====================================================*/
package com.test.mybatis;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class GradeController
{
    //주요 속성 구성
    //mybatis 객체 의존성(자동) 주입
    @Autowired
    private SqlSession sqlSession;

    // 리스트 조회
    @RequestMapping(value="/gradelist.action", method = RequestMethod.GET)
    public String gradeList(ModelMap model)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        model.addAttribute("count",dao.count());
        model.addAttribute("list",dao.list());

        return "/WEB-INF/view/GradeList.jsp";
    }

    // 입력 폼
    @RequestMapping(value="/gradeinsertform.action")
    public String studentInsertForm(String sid, ModelMap model)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        GradeDTO grade = dao.search(Integer.parseInt(sid));
        model.addAttribute("grade", grade);

        return "/WEB-INF/view/GradeInsertForm.jsp";
    }

    // 입력
    @RequestMapping(value="/gradeinsert.action", method = RequestMethod.POST)
    public String studentInsert(GradeDTO grade)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        dao.add(grade);

        return "redirect:gradelist.action";
    }

    // 삭제
    @RequestMapping(value="/gradedelete.action", method = RequestMethod.GET)
    public String studentDelete(String sid)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        dao.remove(Integer.parseInt(sid));

        return "redirect:gradelist.action";
    }

    // 업데이트 폼
    @RequestMapping(value= "/gradeupdateform.action", method = RequestMethod.GET)
    public String studentUpdateForm(String sid, ModelMap model)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        GradeDTO grade = dao.search(Integer.parseInt(sid));
        model.addAttribute("grade", grade);

        return "/WEB-INF/view/GradeUpdateForm.jsp";
    }

    // 업데이트
    @RequestMapping(value= "/gradeupdate.action", method = RequestMethod.POST)
    public String studentUpdate(GradeDTO grade)
    {
        IGradeDAO dao = sqlSession.getMapper(IGradeDAO.class);

        dao.update(grade);

        return "redirect:gradelist.action";
    }

}

ㄴㄴGradeDTO.java

/* ==================================================
    #03 GradeDTO.java
    - 데이터베이스 전송 객체 자료형 클래스(DTO)
      성적 데이터 저장 및 전송
=====================================================*/
package com.test.mybatis;

/*
CREATE TABLE TBL_GRADE
( SID   NUMBER
, SUB1  NUMBER(3)
, SUB2  NUMBER(3)
, SUB3  NUMBER(3)
, CONSTRAINT GRADE_SID_PK PRIMARY KEY(SID)
, CONSTRAINT GRADE_SID_FK FOREIGN KEY(SID)
             REFERENCES TBL_STUDENT(SID)
, CONSTRAINT GRADE_SUB1_CK CHECK(SUB1 BETWEEN 0 AND 100)
, CONSTRAINT GRADE_SUB2_CK CHECK(SUB2 BETWEEN 0 AND 100)
, CONSTRAINT GRADE_SUB3_CK CHECK(SUB3 BETWEEN 0 AND 100)
);

SELECT SID, NAME, SUB1, SUB2, SUB3, TOT, AVG, CH
FROM GRADEVIEW
ORDER BY SID;
*/
public class GradeDTO
{
    // 주요 속성 구성
    private String sid, name, ch;        //-- 번호, 이름, 합격/불합격
    private int sub1, sub2, sub3, tot;    //-- 성적1, 성적2, 성적3, 합계
    private double avg;                    //-- 평균

    // 사용자 정의 생성자 정의 안함
    // → default 생성자 자동 삽입

    // getter / setter 구성
    public String getSid()
    {
        return sid;
    }
    public void setSid(String sid)
    {
        this.sid = sid;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public String getCh()
    {
        return ch;
    }
    public void setCh(String ch)
    {
        this.ch = ch;
    }
    public int getSub1()
    {
        return sub1;
    }
    public void setSub1(int sub1)
    {
        this.sub1 = sub1;
    }
    public int getSub2()
    {
        return sub2;
    }
    public void setSub2(int sub2)
    {
        this.sub2 = sub2;
    }
    public int getSub3()
    {
        return sub3;
    }
    public void setSub3(int sub3)
    {
        this.sub3 = sub3;
    }
    public int getTot()
    {
        return tot;
    }
    public void setTot(int tot)
    {
        this.tot = tot;
    }
    public double getAvg()
    {
        return avg;
    }
    public void setAvg(double avg)
    {
        this.avg = avg;
    }    

}

ㄴㄴIGradeDAO.java

/* ==================================================
    #05 IGradeDAO.java
    - 인터페이스
      학생 데이터 액션 처리 메소드 선언
=====================================================*/
package com.test.mybatis;

import java.util.ArrayList;

public interface IGradeDAO
{
    public ArrayList<StudentDTO> list();    //-- 리스트조회
    public int count();                        //-- 총 개수
    public int add(GradeDTO grade);        //-- 성적 데이터 추가
    public int remove(int sid);                //-- 성적 데이터 삭제
    public GradeDTO search(int sid);        //-- 성적 데이터 검색
    public int update(GradeDTO grade);//-- 성적 데이터 업데이트
}

ㄴㄴIStudentDAO.java

/* ==================================================
    #04 IStudentDAO.java 
    - 인터페이스
      학생 데이터 액션 처리 메소드 선언
=====================================================*/

package com.test.mybatis;

import java.util.ArrayList;

public interface IStudentDAO
{
    public ArrayList<StudentDTO> list();    //-- 리스트조회
    public int count();                        //-- 총 개수
    public int add(StudentDTO student);        //-- 학생 데이터 추가
    public int remove(int sid);                //-- 학생 데이터 삭제
    public StudentDTO search(int sid);        //-- 학생 데이터 검색
    public int update(StudentDTO student);//-- 학생 데이터 업데이트
    public int maxId();                        //-- 학생 번호 최대값
}

ㄴㄴStudentController.java

/* ==================================================
    #08 StudentController.java
    - Controller
=====================================================*/
package com.test.mybatis;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class StudentController
{
    //주요 속성 구성
    //mybatis 객체 의존성(자동) 주입
    @Autowired
    private SqlSession sqlSession;

    // 리스트 조회
    @RequestMapping(value="/studentlist.action", method = RequestMethod.GET)
    public String studentList(ModelMap model)
    {
        IStudentDAO dao = sqlSession.getMapper(IStudentDAO.class);

        model.addAttribute("count",dao.count());
        model.addAttribute("list",dao.list());

        return "/WEB-INF/view/StudentList.jsp";
    }

    // 입력 폼
    @RequestMapping(value="/studentinsertform.action")
    public String studentInsertForm()
    {
        return "/WEB-INF/view/StudentInsertForm.jsp";
    }

    // 입력
    @RequestMapping(value="/studentinsert.action", method = RequestMethod.POST)
    public String studentInsert(StudentDTO student)
    {
        IStudentDAO dao = sqlSession.getMapper(IStudentDAO.class);
        int sid = dao.maxId();
        student.setSid(String.format("%s", sid));

        dao.add(student);

        return "redirect:studentlist.action";
    }

    // 삭제
    @RequestMapping(value="/studentdelete.action", method = RequestMethod.GET)
    public String studentDelete(String sid)
    {
        IStudentDAO dao = sqlSession.getMapper(IStudentDAO.class);

        dao.remove(Integer.parseInt(sid));

        return "redirect:studentlist.action";
    }

    // 업데이트 폼
    @RequestMapping(value= "/studentupdateform.action", method = RequestMethod.GET)
    public String studentUpdateForm(String sid, ModelMap model)
    {
        IStudentDAO dao = sqlSession.getMapper(IStudentDAO.class);

        StudentDTO student = dao.search(Integer.parseInt(sid));
        model.addAttribute("student", student);

        return "/WEB-INF/view/StudentUpdateForm.jsp";
    }

    // 업데이트
    @RequestMapping(value= "/studentupdate.action", method = RequestMethod.GET)
    public String studentUpdate(StudentDTO student)
    {
        IStudentDAO dao = sqlSession.getMapper(IStudentDAO.class);

        dao.update(student);

        return "redirect:studentlist.action";
    }
}

ㄴㄴStudentDTO.java

/* ==================================================
    #02 StudentDTO.java
    - 데이터베이스 전송 객체 자료형 클래스(DTO)
      학생 데이터 저장 및 전송
=====================================================*/
package com.test.mybatis;

/*
CREATE TABLE TBL_STUDENT
( SID   NUMBER
, NAME  VARCHAR2(30)
, TEL   VARCHAR2(40)
, CONSTRAINT STUDENT_SID_PK PRIMARY KEY(SID)
);

SELECT SID, NAME, TEL, SUB
FROM STUDENTVIEW
ORDER BY SID;
*/
public class StudentDTO
{
    // 주요 속성 구성
    private String sid, name, tel;    //-- 번호, 이름, 전화번호
    private int sub;                //-- 삭제 가능 여부

    // 사용자 정의 생성자 정의 안함
    // → default 생성자 자동 삽입

    // getter / setter
    public String getSid()
    {
        return sid;
    }
    public void setSid(String sid)
    {
        this.sid = sid;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public String getTel()
    {
        return tel;
    }
    public void setTel(String tel)
    {
        this.tel = tel;
    }
    public int getSub()
    {
        return sub;
    }
    public void setSub(int sub)
    {
        this.sub = sub;
    }


}

ㄴ/MybatisWork03/src/com/test/mybatis/mapper

ㄴㄴGradeDAO.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.mybatis.IGradeDAO">
<!-- ============================================
    #07 GradeDAO.xml
        - mybatis 환경 설정 파일 활용
================================================= -->
    <select id="list" resultType="com.test.mybatis.GradeDTO">
        SELECT SID, NAME, SUB1, SUB2, SUB3, TOT, AVG, CH
        FROM GRADEVIEW
        ORDER BY SID
    </select>

    <select id="search" resultType="com.test.mybatis.GradeDTO">
        SELECT SID, NAME, SUB1, SUB2, SUB3, TOT, AVG, CH
        FROM GRADEVIEW
        WHERE SID = #{SID}
    </select>

    <select id="count" resultType="java.lang.Integer">
        SELECT COUNT(*) AS COUNT
        FROM TBL_GRADE
    </select>

    <insert id="add">
        INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
        VALUES(#{sid},#{sub1},#{sub2},#{sub3})
    </insert>

    <delete id="remove">
        DELETE
        FROM TBL_GRADE
        WHERE SID=#{sid}
    </delete>

    <update id="update">
        UPDATE TBL_GRADE
        SET SUB1=#{sub1}, SUB2=#{sub2}, SUB3=#{sub3}
        WHERE SID = #{sid}
    </update>
</mapper>

ㄴㄴStudentDAO.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.mybatis.IStudentDAO">
<!-- ============================================
    #06 StudentDAO.xml
        - mybatis 환경 설정 파일 활용
================================================= -->
    <select id="list" resultType="com.test.mybatis.StudentDTO">
        SELECT SID, NAME, TEL, SUB
        FROM STUDENTVIEW
        ORDER BY SID
    </select>

    <select id="search" resultType="com.test.mybatis.StudentDTO">
        SELECT SID, NAME, TEL, SUB
        FROM STUDENTVIEW
        WHERE SID = #{sid}
    </select>

    <select id="count" resultType="java.lang.Integer">
        SELECT COUNT(*) AS COUNT
        FROM TBL_STUDENT
    </select>

    <select id="maxId" resultType="java.lang.Integer">
        SELECT MAX(SID)+1
        FROM TBL_STUDENT
    </select>

    <insert id="add">
        INSERT INTO TBL_STUDENT(SID, NAME, TEL)
        VALUES(#{sid}, #{name}, #{tel})
    </insert>

    <delete id="remove">
        DELETE
        FROM TBL_STUDENT
        WHERE SID = #{sid}
    </delete>

    <update id="update">
        UPDATE TBL_STUDENT
        SET NAME=#{name}, TEL=#{tel}
        WHERE SID = #{sid}
    </update>
</mapper>

ㄴ/MybatisWork03/WebContent/css

ㄴㄴmain.css

... 기존 파일과 거의 동일 ...

ㄴㄴstyle.css

@charset "UTF-8";


/* 웹폰트-Pretendard ------------------------------------------------------ */
@import url("https://cdn.jsdelivr.net/gh/orioncactus/pretendard@v1.3.9/dist/web/static/pretendard.min.css");

/* 초기셋팅  ------------------------------------------------------ */
*{font-family: Pretendard;box-sizing: border-box;}
body{margin: 0;}
dl, ul, ol, menu, li{list-style: none;}
ul{padding: 0;margin:0;}
p {margin:0;padding:0;}
a {text-decoration: none;}
input, select, textarea {margin:0; padding:0; box-sizing:border-box; -webkit-box-sizing:border-box; -moz-box-sizing: border-box;}
:root {
    --border: #333;
    --btn-border: #ddd;
    --text: #707070;
    --text-bold: #111;
    --bg-gray: #eef3ff;
    --main: #21409a;
    --light1: #f6f3c3;
    --light2: #dec3f6;
    --white: #ffffffc2;
    --disabled: #fafafa;
    --student: #0177a9;
    --grade: #FF9800;
    --insert: #FF8F8F;
    --update: #62c8eb;
  }

/* section  ------------------------------------------------------ */
.section{max-width: 1200px;width:100%;margin:0 auto;padding-left: 20px;padding-right: 20px;}
.section h1{font-size: 28px;}
.section input + input{margin-left: 4px;}
.section input[readonly]{background: var(--btn-border) !important;}
.section input[type="text"],
.section input[type="password"],
.section input[type="tel"]{padding: 4px;border-radius: 5px;background: var(--white);border: 1px solid var(--btn-border);}
.section input[type="text"]:disabled{background: var(--disabled);}
.section textarea{padding: 4px;border: 1px solid var(--btn-border);background: #fff;border-radius: 5px;transition: 0.6s;}
.section table{border-spacing:0;border-collapse:collapse;}
.tbl_ty01{}
.section table{border-top: 1px solid var(--text);}
.section table th{padding: 4px;background: #F2F2F2;text-align:center;}
.section table td{padding: 4px;border-bottom: 1px solid var(--btn-border);text-align:center;}
.section table tr{}


.section .layout{}
.section .layout [class*="_box"]{padding-top: 14px;padding-bottom:14px;}
.section .layout form > div{padding-top: 14px; padding-bottom: 14px;}
.section .layout .tit{display:inline-flex; align-items:center;}
.section .layout .tit + input{margin-left: 4px;}

/* 정보영역 */
.info_box{background: var(--light1);padding-left: 20px;padding-right: 20px;border-radius: 10px;}

/* 입력영역 */
.input_box{background: var(--light2);padding-left: 20px;padding-right: 20px;border-radius: 10px;}

/* 버튼영역 */
.btn_box{display: flex;}
.btn_box.center{justify-content: center;}
.btn_box [type="button"],
.btn_box [type="reset"],
.btn_box [type="submit"],
.btn{min-width: 100px;height: 30px;padding: 4px;font-weight: bold;border: 1px solid var(--btn-border);background: #fff;border-radius: 5px;transition: 0.6s;}
.btn + .btn{margin-left: 8px;}
.btn_box [type="button"].w_auto,
.btn_box [type="reset"].w_auto,
.btn.w_auto{min-width:50px;width:auto;}
.btn_box [type="submit"]:hover,
.btn_box [type="submit"]:active,
.btn_box [type="button"]:hover,
.btn_box [type="button"]:active,
.btn_box [type="reset"]:hover,
.btn_box [type="reset"]:active,
.btn:hover,.btn:active{background: var(--bg-gray);border-color: var(--border);}

/* 결과 영역 */
.section .layout .result_box{}
.section .layout ul>li + li{margin-top: 6px;}
.section .layout ul>li .tit{display: inline-block;width: 100px;}

.section .layout >div>ul>li .ip_box{}


/*   ------------------------------------------------------ */
.section .color_list{}
.section .color_insert{color: #FF8F8F;}
.section .color_update{color: #508D69;}
.section.grade .color_title{color: var(--grade);}
.section.student .color_title{color: var(--student);}

#menu{margin: 20px 0 40px;}
#menu > ul{display: flex;}
#menu > ul > li{flex: 1 1 auto;}
#menu > ul > li >a{display: block;width: 100%;text-align: center;padding: 20px;font-size: 20px;font-weight: 700;background: var(--student);}
#menu > ul > li >a:hover{opacity: 0.5;}
#menu > ul > li:nth-child(2n) >a{background: var(--grade);}
#menu > ul > li >a{color: #fff;}
.info_desc{text-align: right;margin-bottom: 4px;}

.c_red{color: var(--grade);}

ㄴ/MybatisWork03/WebContent/WEB-INF/view

ㄴㄴGradeInsertForm.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
</head>
<body>
<body class="section grade">

<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">성적</span> 데이터 <span class="color_insert">입력</span> 페이지</h1>
</div>

<div class="layout">

        <!-- 성적 데이터 입력 폼 구성 -->
        <form action="gradeinsert.action" method="post" id="gradeScoreForm">
            <table class="table">
                <tr>
                    <th>번호</th>
                    <td style="text-align:left;">${grade.sid }</td>
                    <input type="hidden" id="sid" name="sid" value="${grade.sid }">
                </tr>
                <tr>
                    <th style="width: 150px;">이름</th>
                    <td style="text-align:left;">${grade.name }</td>
                    <input type="hidden" id="name" name="name" value="${grade.name }">
                </tr>
                <tr>
                    <th>국어점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub1" name="sub1" value="">
                    </td>
                </tr>
                <tr>
                    <th>영어점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub2" name="sub2" value="">
                    </td>
                </tr>
                <tr>
                    <th>수학점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub3" name="sub3" value="">
                    </td>
                </tr>
            </table>

            <div class="btn_box">
                <button type="submit">입력하기</button>
                <input type="reset" value="취소하기" />
                <a href="gradelist.action"><button type="button">목록으로</button></a>
            </div>
        </form>
</div>

</body>
</body>
</html>

ㄴㄴGradeList.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>StudentList.jsp</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
<script type="text/javascript" src="http://code.jquery.com/jquery.min.js"></script>

<script type="text/javascript">
$(function(){

    $(".btnCancel").click(function ()
    {
        if(confirm("데이터를 정말 삭제하시겠습니까?"))
        {
            $(location).attr("href","gradedelete.action?sid="+$(this).val());
        }
    })
})
</script>
</head>
<body class="section grade">

<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">성적</span> 관리 및 <span class="color_list">출력</span> 페이지</h1>
</div>

<div class="info_desc">성적처리 인원수: ${count }</div>
<!-- 번호 이름 국어점수 영어점수 수학점수 총점 평균        성적처리(입력 수정 삭제) -->
<table>
    <tr>
        <th style="width: 10%">번호</th>
        <th style="width: 10%">이름</th>
        <th>국어점수</th><th>영어점수</th><th>수학점수</th>
        <th>총점</th><th>평균</th><th>합격여부</th>
        <th>성적처리</th>
    </tr>
    <c:forEach var="grade" items="${list}">
        <tr>
            <td>${grade.sid}</td>
            <td>${grade.name}</td>
            <td>${grade.sub1 >-1 ? grade.sub1 : '<span class="c_red">미등록</span>'}</td>
            <td>${grade.sub2 >-1 ? grade.sub2 : '<span class="c_red">미등록</span>'}</td>
            <td>${grade.sub3 >-1 ? grade.sub3 : '<span class="c_red">미등록</span>'}</td>
            <td>${grade.tot >-1 ? grade.tot : '<span class="c_red">미등록</span>'}</td>
            <td>${grade.avg >-1 ? grade.avg : '<span class="c_red">미등록</span>'}</td>
            <td>${grade.ch}</td>
            <td>
                <button type="button" class="btn01" ${grade.sub1 >-1 && grade.sub2 >-1 && grade.sub3 >-1 ? "disabled='disabled'" : "" }
                onclick="location.href='gradeinsertform.action?sid=${grade.sid}'"
                >입력</button>
                <button type="button" class="btn01" ${grade.sub1 >-1 && grade.sub2 >-1 && grade.sub3 >-1 ? "" : "disabled='disabled'" }
                onclick="location.href='gradeupdateform.action?sid=${grade.sid}'"
                >수정</button>
                <button type="button" class="btn01 btnCancel" ${grade.sub1 >-1 && grade.sub2 >-1 && grade.sub3 >-1 ? "" : "disabled='disabled'" }
                value="${grade.sid}">삭제</button>
            </td>
        </tr>
    </c:forEach>
</table>

</body>
</html>

ㄴㄴGradeUpdateForm.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>GradeUpdateForm.jsp</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
</head>
<body class="section grade">
<!-- ==================================================
    #15 GradeUpdateForm.jsp 
    - view 페이지
      (성적 명단 수정 폼 구성)
===================================================== -->
<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">성적</span> 데이터 <span class="color_update">수정</span> 페이지</h1>
</div>

<div class="layout">

    <div>
        <!-- 성적 데이터 수정 폼 구성 -->
        <form action="gradeupdate.action" method="post" id="gradeScoreForm">
            <table class="table">
                <tr>
                    <th>번호</th>
                    <td style="text-align:left;">${grade.sid }</td>
                    <input type="hidden" id="sid" name="sid" value="${grade.sid }">
                </tr>
                <tr>
                    <th style="width: 150px;">이름</th>
                    <td style="text-align:left;">${grade.name }</td>
                    <input type="hidden" id="name" name="name" value="${grade.name }">
                </tr>
                <tr>
                    <th>국어점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub1" name="sub1" value="${grade.sub1 }">
                    </td>
                </tr>
                <tr>
                    <th>영어점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub2" name="sub2" value="${grade.sub2 }"">
                    </td>
                </tr>
                <tr>
                    <th>수학점수</th>
                    <td style="text-align:left;">
                        <input type="text" id="sub3" name="sub3" value="${grade.sub3 }"">
                    </td>
                </tr>
            </table>

            <div class="btn_box">
                <button type="submit">수정하기</button>
                <input type="reset" value="취소하기" />
                <a href="gradelist.action"><button type="button">목록으로</button></a>
            </div>
        </form>
    </div>
</div>

</body>
</html>​

ㄴㄴListMenu.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="css/main.css">
</head>
<body>
<!-- -------------------------------------
    #18 listMenu.jsp
        - 메인 메뉴 페이지 구성
------------------------------------------ -->

<div id="menu">
    <ul>
        <li><a href="studentlist.action" class="menu">학생 정보</a></li>
        <li><a href="gradelist.action" class="menu">성적 정보</a></li>
    </ul>
</div>

</body>
</html>​

ㄴㄴStudentInsertForm.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>StudentList.jsp</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
</head>
<body class="section student">
<!-- ==================================================
    #11 StudentInsertForm.jsp 
    - view 페이지
      (학생 명단 입력 폼 구성)
===================================================== -->
<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">학생</span> 데이터 <span class="color_insert">입력</span> 페이지</h1>
</div>

<div class="layout">

    <div>
        <!-- 회원 데이터 입력 폼 구성 -->
        <form action="studentinsert.action" method="post" id="StudentForm">
            <table class="table" style="width: 600px">
                <tr>
                    <th style="width: 150px;">이름</th>
                    <td>
                        <input type="text" id="name" name="name">
                    </td>
                </tr>
                <tr>
                    <th>전화번호</th>
                    <td>
                        <input type="text" id="tel" name="tel">
                    </td>
                </tr>
            </table>

            <div class="btn_box">
                <button type="submit">입력하기</button>
                <input type="reset" value="취소하기" />
                <a href="studentlist.action"><button type="button">목록으로</button></a>
            </div>
        </form>
    </div>
</div>

</body>
</html>​

ㄴㄴStudentList.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>StudentList.jsp</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
<script type="text/javascript" src="http://code.jquery.com/jquery.min.js"></script>

<script type="text/javascript">
$(function(){

    $(".btnCancel").click(function ()
    {

        if(confirm("데이터를 정말 삭제하시겠습니까?"))
        {
            $(location).attr("href","studentdelete.action?sid="+$(this).val());
        }
    })
})
</script>
</head>
<body class="section student">
<!-- ==================================================
    #10 StudentList.jsp 
    - view 페이지
      (학생 명단 출력 레이아웃 구성)
===================================================== -->
<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">학생</span> 관리 및 <span class="color_list">출력</span> 페이지</h1>
</div>

<div class="layout">
    <div class="btn_box">
        <a href="studentinsertform.action"><button type="button">신규 회원 등록</button></a>
    </div>

    <div class="info_desc">총 인원수: ${count }</div>
    <!-- 리스트 출력 -->
    <table class="table">
        <tr>
            <th style="width: 25%">번호</th>
            <th style="width: 25%">이름</th>
            <th style="width: 25%">전화번호</th>
            <th style="width: 25%">관리</th>
        </tr>
        <!-- 
        <tr>
            <td>1</td>
            <td>회원1</td>
            <td>010-1111-1111</td>
            <td>
                <a><button type="button" class="btnUpdate">수정</button></a>
                <a><button type="button" class="btnCancel">삭제</button></a>
            </td>
        </tr>
         -->
        <c:forEach var="student" items="${list}">
        <tr>
            <td>${student.sid}</td>
            <td>${student.name}</td>
            <td>${student.tel}</td>
            <td>
                <a href="studentupdateform.action?sid=${student.sid}"><button type="button" class="btnUpdate" value="${student.sid }">수정</button></a>
                 <%-- <button type="button" class="btn btn-default btn-xs btnInsert"
                     onclick="location.href='gradeinsertform.action?sid=${student.sid}'"
                     ${student.sub == 0 ? "style=\"display:inline;\"" : "style=\"display:none;\"" }>성적 입력</button> --%>
                <button type="button" class="btnCancel"
                ${student.sub >0 ? "disabled='disabled'": ""} value="${student.sid }">삭제</button>
            </td>
        </tr>
        </c:forEach>
    </table>
</div>
</body>
</html>​

ㄴㄴStudentUpdateForm.jsp

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
    request.setCharacterEncoding("UTF-8");
    String cp = request.getContextPath();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>StudentList.jsp</title>
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/main.css">
<link rel="stylesheet" type="text/css" href="<%=cp %>/css/style.css">
</head>
<body class="section student">
<!-- ==================================================
    #14 StudentUpdateForm.jsp 
    - view 페이지
      (학생 명단 수정 폼 구성)
===================================================== -->
<!-- 공통 메뉴 -->
<div>
    <c:import url="ListMenu.jsp"></c:import>
</div>

<div>
    <h1><span class="color_title">학생</span> 데이터 <span class="color_update">수정</span> 페이지</h1>
</div>

<div class="layout">

    <div>
        <!-- 회원 데이터 수정 폼 구성 -->
        <form action="studentupdate.action" method="get" id="StudentForm">
            <table class="table" style="width: 600px">
                <tr>
                    <th style="width: 150px;">번호</th>
                    <td>
                        <input type="text" id="sid" name="sid" value="${student.sid}" readonly="readonly">
                    </td>
                </tr>
                <tr>
                    <th style="width: 150px;">이름(*)</th>
                    <td>
                        <input type="text" id="name" name="name" value="${student.name}">
                    </td>
                </tr>
                <tr>
                    <th>전화번호</th>
                    <td>
                        <input type="text" id="tel" name="tel" value="${student.tel}">
                    </td>
                </tr>
            </table>

            <div class="btn_box">
                <button type="submit">수정하기</button>
                <input type="reset" value="취소하기" />
                <a href="studentlist.action"><button type="button">목록으로</button></a>
            </div>
        </form>
    </div>
</div>

</body>
</html>​

ㄴ /MybatisWork03/WebContent/WEB-INF

ㄴㄴ dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
                    http://www.springframework.org/schema/context
                    http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <!-- ※ 사용자 정의 Controller 객체 등록 및 URL 매핑 주소 등록 -->
    <!-- - 『name=""』속성에 매핑 주소를 등록한다. -->
    <!--     이 과정에서 Front Controller 가 갖고있는 확장자의 형태로 구성한다. -->
    <!-- - 『class=""』속성 -->
    <!--     은 Controller 객체의 패키지 경로가 포함된 클래스 이름을 등록한다. -->



    <!-- ① 기존 코드 -->
    <!-- ※ Annotation 표기법으로 Controller 객체를 등록할 수 있도록 한다. -->
    <!-- <context:component-scan    base-package="org.springframework.samples.petclinic.web" /> -->
    <context:component-scan    base-package="com.test.mybatis" />



    <!-- ③ mybatis 등록 과정에서 생겨나는 추가 코드 『2』 -->
    <!-- ※ mybatis 를 사용하기 위한 환경 설정을 추가하는 과정에서 -->
    <!--     필요한 datatSource 를 사용하기 위해 환경 설정 추가 -->
    <bean id="localDataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@211.238.142.167:1521:xe"></property>
        <property name="username" value="scott"></property>
        <property name="password" value="tiger"></property>
    </bean>


    <!-- ② mybatis 등록 과정에서 생겨나는 추가 코드 『1』 -->
    <!-- ※ mybatis 를 사용하기 위한 환경 설정 추가 -->
    <!--    → SqlSessionFactoryBean 등록 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

        <!-- ④ dataSource 를 등록하고 와서 다시 추가 -->
        <property name="dataSource" ref="localDataSource"></property>

        <!-- ⑤ 매퍼 파일을 작성하고 와서 다시 추가 -->
        <!-- value 구성 과정 check~!!! -->
        <property name="mapperLocations" value="classpath:com/test/mybatis/mapper/*.xml"></property>
    </bean>
    <!-- ** 인터페이스 안에 메소드가 들어갈 것
    인터페이스를 객체화 시켜서 돌아가게 하는 것
     ** -->

    <!-- ⑥ mybatis 등록 과정에서 생겨나는 추가 코드 『3』 -->
    <!-- ※ SqlSession 을 사용하기 위한 환경 설정 추가 -->
    <!--    → SqlSessionTemplate 등록 -->
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory"></constructor-arg>
        <!-- 【<constructor-arg>】: 생성자의 인자값을 넘겨 주겠다./ 【index="0"】: 0번째 매개변수 전달 -->
    </bean>

</beans>

ㄴㄴ web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://xmlns.jcp.org/xml/ns/javaee"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
    id="WebApp_ID" version="3.1">
    <display-name>Mvc00</display-name>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.htm</welcome-file>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>default.html</welcome-file>
        <welcome-file>default.htm</welcome-file>
        <welcome-file>default.jsp</welcome-file>
    </welcome-file-list>

    <!-- #18. web.xml -->

    <!-- ※ Spring MVC Framework 등록 -->
    <!--     → Front Controller 등록 -->
    <!--     → DispatcherServlet 등록 -->
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.action</url-pattern>
    </servlet-mapping>

    <!-- check~!!! -->
    <!-- 필터 등록 → 인코딩 필터 등록 → CharacterEncodingFilter -->
    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <!-- **java 파일에서 위치 찾아오기 -->

        <!-- 필터 설정 → 필터 객체에 파라미터 초기값 설정 -->
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
    </filter>

    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <!-- <url-pattern>*.action</url-pattern> --> <!-- **『*.action』: action일 경우 동작 -->
        <url-pattern>/*</url-pattern> <!-- **『/*』 : 어떤 액션이든 모두 동작 -->
    </filter-mapping>

</web-app>

ㄴ /MybatisWork03/WebContent

ㄴㄴ studentlist.action

... 내용 없음 ...

ㄴMybatisWork03_scott.sql

SELECT USER
FROM DUAL;
--==>> SCOTT


--○ 실습 테이블 생성
CREATE TABLE TBL_STUDENT
( SID   NUMBER
, NAME  VARCHAR2(30)
, TEL   VARCHAR2(40)
, CONSTRAINT STUDENT_SID_PK PRIMARY KEY(SID)
);
--==>> Table TBL_STUDENT이(가) 생성되었습니다.


--○ 데이터 입력
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(1, '정현욱', '010-1111-1111');
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(2, '이윤수', '010-2222-2222');
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(3, '강혜성', '010-3333-3333');
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(4, '박범구', '010-4444-4444');
--==>> 1 행 이(가) 삽입되었습니다. * 4


--○ 확인
SELECT SID, NAME, TEL
FROM TBL_STUDENT;
--==>> 
/*
1	정현욱	010-1111-1111
2	이윤수	010-2222-2222
3	강혜성	010-3333-3333
4	박범구	010-4444-4444
*/


--○ 커밋
COMMIT;
--==>> 커밋 완료.


--○ 실습테이블 생성
CREATE TABLE TBL_GRADE
( SID   NUMBER
, SUB1  NUMBER(3)
, SUB2  NUMBER(3)
, SUB3  NUMBER(3)
, CONSTRAINT GRADE_SID_PK PRIMARY KEY(SID)
, CONSTRAINT GRADE_SID_FK FOREIGN KEY(SID)
             REFERENCES TBL_STUDENT(SID)
, CONSTRAINT GRADE_SUB1_CK CHECK(SUB1 BETWEEN 0 AND 100)
, CONSTRAINT GRADE_SUB2_CK CHECK(SUB2 BETWEEN 0 AND 100)
, CONSTRAINT GRADE_SUB3_CK CHECK(SUB3 BETWEEN 0 AND 100)
);
--==>> Table TBL_GRADE이(가) 생성되었습니다.


--○ 데이터 입력
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(1,90,90,80);
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(2,92,92,92);
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(3,70,60,50);
--==>> 1 행 이(가) 삽입되었습니다. * 3


--○ 확인
SELECT *
FROM TBL_GRADE;
--==>>
/*
1	90	90	80
2	92	92	92
3	70	60	50
*/


--○ 커밋
COMMIT;
--==>> 커밋 완료.

--○ 뷰 생성(STUDENTVIEW)
CREATE OR REPLACE VIEW STUDENTVIEW
AS
SELECT S.SID AS SID
     , S.NAME AS NAME
     , S.TEL AS TEL
     , (SELECT COUNT(*)
        FROM TBL_GRADE
        WHERE SID = S.SID) AS SUB
FROM TBL_STUDENT S;
--==>> View STUDENTVIEW이(가) 생성되었습니다.


--○ 뷰 생성(GRADEVIEW)
CREATE OR REPLACE VIEW GRADEVIEW
AS
SELECT S.SID AS SID
     , S.NAME AS NAME
     , NVL(G.SUB1, -1) AS SUB1
     , NVL(G.SUB2, -1) AS SUB2
     , NVL(G.SUB3, -1) AS SUB3
     , NVL((G.SUB1 + G.SUB2 + G.SUB3), -1) AS TOT
     , NVL(ROUND((G.SUB1 + G.SUB2 + G.SUB3)/3,1),-1) AS AVG
     , (CASE WHEN NVL((G.SUB1 + G.SUB2 + G.SUB3)/3, -1) <= 60 THEN '불합격'
             WHEN (G.SUB1<=40) OR (G.SUB2<=40) OR (G.SUB3<=40) THEN '과락'
             ELSE '합격'
        END) AS CH
--    ,RANK() OVER(ORDER BY (SUB1+SUB2+SUB3) DESC) AS RANK
FROM TBL_STUDENT S LEFT JOIN TBL_GRADE G
ON S.SID = G.SID;
--==>> View GRADEVIEW이(가) 생성되었습니다.


--○ 뷰(STUDENTVIEW) 조회
SELECT SID, NAME, TEL, SUB
FROM STUDENTVIEW
ORDER BY SID;
--==>>
/*
1	정현욱	010-1111-1111	1
2	이윤수	010-2222-2222	1
3	강혜성	010-3333-3333	1
4	박범구	010-4444-4444	0
*/

--○
SELECT SID, NAME, TEL, SUB
FROM STUDENTVIEW
WHERE SID = 1;

--○
UPDATE TBL_STUDENT
SET NAME='수정', TEL='010-7777-7777'
WHERE SID = 4;

ROLLBACK;


--○ 뷰(GRADEVIEW) 조회
SELECT SID, NAME, SUB1, SUB2, SUB3, TOT, AVG, CH
FROM GRADEVIEW
ORDER BY SID;
--==>>
/*
1	정현욱	90	90	80	260	86.7	합격
2	이윤수	92	92	92	276	92	합격
3	강혜성	70	60	50	180	60	불합격
4	박범구	-1	-1	-1	-1	-1	불합격
*/

--○ 인원 수 확인
SELECT COUNT(*) AS COUNT
FROM TBL_STUDENT;
--==>> 4


--○ 성적 처리 수 확인
SELECT COUNT(*) AS COUNT
FROM TBL_GRADE;


--○ 학생 데이터 삭제
DELETE
FROM TBL_STUDENT
WHERE SID = 4;

--○ 성적 데이터 삭제
DELETE
FROM TBL_GRADE
WHERE SID=3;

--○
ROLLBACK;

COMMIT;