03일차(2)_모델2방식, 성적관리
----------------------------------------------
성적관리 Servlet 프로그램(3)
-> Model2 방식
-> 자료형 클래스 및 데이터베이스 액션, 서블릿 클래스, 화면구성 페이지 분리
1. 기능 구현
- 학생 테이블에는 학생의 개인 정보(번호, 학생 이름, 전화번호)가 저장된다.
- 성적 테이블에는 학생의 성적 정보(번호, 과목1, 과목2, 과목3)가 저장된다.
- 번호 항목은 학생 테이블의 PK, 성적 테이블의 PK&FK로 지정한다.
- 학생 출력시 번호, 학생 이름, 전화번호, 성적 입력 여부가 출력된다.
- 학생 입력은 학생 이름, 전화번호를 입력한다.
- 성적 출력시 번호, 학생이름, 과목1, 과목2, 과목3, 총점, 평균, 판정 점수가 출력한다.
- 판정 기준은 합격, 과락, 불합격으로 구분한다.
- 성적 입력이 안된 학생인 경우는 번호, 학생 이름만 출력하고 점수는 null 문자 또는 X 문자로 출력한다.
- 성적 입력은 학생 개개인별로 과목1, 과목2, 과목3 점수를 입력한다.
- 학생 삭제 기능, 성적 삭제 기능 추가
2. 화면 구성
---------------------------------------------------
학생명단버튼 성적출력버튼
--- 학생 명단 ---
학생추가버튼
전체 학생 수 : 2명
-----------------------------------------
번호, 학생 이름, 전화번호, 성적 입력 여부, 삭제
-----------------------------------------
1, kim, 010-111-1111, O, 삭제버튼(비활성)
2, park, 010-222-2222, O, 삭제버튼(비활성)
3, choi, 010-456-4567, X, 삭제버튼(활성)
---------------------------------------------------
학생명단버튼 성적출력버튼
--- 학생 추가 ---
이름 [ ]
전화번호 [ ]
학생추가버튼
---------------------------------------------------
학생명단버튼 성적출력버튼
--- 성적 출력 ---
전체 학생 수 : 2명
-----------------------------------------
번호, 학생이름, 과목1, 과목2, 과목3, 총점, 평균, 판정, 삭제
-----------------------------------------
1, kim, 100, 100, 100, 300, 100.0, 합격 성적추가버튼(비활성) 삭제버튼(활성)
2, park, 50, 50, 50, 150, 50.0, 불합격 성적추가버튼(비활성) 삭제버튼(활성)
3, choi, null, null, null, null, 0, 불합격 성적추가버튼(활성) 삭제버튼(비활성)
---------------------------------------------------
학생명단버튼 성적출력버튼
--성적 추가--
번호* [3 ]
학생이름* [choi ]
과목1(0~100) [ ]
과목2(0~100) [ ]
과목3(0~100) [ ]
성적추가버튼
3. 데이터베이스 준비
-- 학생 테이블 생성
CREATE TABLE student (
sid NUMBER --PK
,name NVARCHAR2(30) --한글 저장
,tel VARCHAR2(30)
);
--PK 제약 추가
ALTER TABLE student
ADD CONSTRAINT student_sid_pk PRIMARY KEY(sid);
--일련번호 생성을 위한 시퀀스 객체 생성
CREATE SEQUENCE studentSeq;
--INSERT 쿼리 샘플 (프로그램 사용 예정)
INSERT INTO student (sid, name, tel)
VALUES (studentSeq.nextval, 'kim', '010-111-1111');
INSERT INTO student (sid, name, tel)
VALUES (studentSeq.nextval, 'park', '010-222-2222');
SELECT * FROM student;
COMMIT;
--성적 테이블 생성
CREATE TABLE grade (
sid NUMBER --PK and FK
,sub1 NUMBER(3) --CHECK 제약 추가
,sub2 NUMBER(3) --CHECK 제약 추가
,sub3 NUMBER(3) --CHECK 제약 추가
);
--제약 추가
ALTER TABLE grade
ADD (CONSTRAINT grade_sid_pk PRIMARY KEY(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));
ALTER TABLE grade
ADD CONSTRAINT grade_sid_fk FOREIGN KEY (sid)
REFERENCES student(sid);
SELECT *
FROM constraint_check
WHERE table_name='GRADE';
--INSERT 쿼리 샘플 (프로그램에서 사용 예정)
INSERT INTO grade (sid, sub1, sub2, sub3)
VALUES (1, 100, 100, 100); --O
COMMIT;
--학생 테이블의 자료 2명으로 만들 것.
SELECT * FROM student;
--성적 테이블의 자료 1명으로 만들 것.
SELECT * FROM grade;
--학생수 출력 쿼리
SELECT COUNT(*) AS count FROM student;
--학생 전체 명단 출력 쿼리 (프로그램에서 사용 예정)
--번호, 학생 이름, 전화번호, 성적 입력 여부(0 또는 1)
--학생 테이블 + 성적 테이블 -> JOIN, SUB QUERY
--OUTER JOIN의 경우
SELECT s.sid AS sid, name, tel
, DECODE(sub1, NULL, 0, 1) AS sub --성적 입력 여부 (1 또는 0)
FROM student s, grade g
WHERE s.sid = g.sid(+);
--SUB QUERY의 경우
SELECT sid, name, tel
, DECODE((SELECT sid FROM grade WHERE sid=s.sid)
, null, 0, 1) AS sub
FROM student s;
-->뷰(View)로 등록하는 것을 권장
CREATE OR REPLACE VIEW studentView
AS
SELECT sid, name, tel
, DECODE((SELECT sid FROM grade WHERE sid=s.sid)
, null, 0, 1) AS sub
FROM student s;
-->뷰를 이용한 학생 전체 명단 출력 쿼리
SELECT sid, name, tel, sub
FROM studentView
ORDER BY sid;
--학생 검색 쿼리(성적 입력 전에 학생 정보 검색 과정에서 사용. 프로그램에서 사용 예정)
SELECT sid, name, tel, sub
FROM studentView
WHERE sid=1;
--성적 출력 쿼리 (프로그램에서 사용 예정)
--번호, 학생이름, 과목1, 과목2, 과목3, 총점, 평균, 판정(0 합격, 1 과락, 2 불합격)
--학생 테이블->번호, 학생이름
--성적 테이블->과목1, 과목2, 과목3
--총점, 평균, 판정 -> 계산 결과
--학생 테이블 + 성적 테이블 -> JOIN, SUB QUERY
주의) 오라클에서 NULL 데이터는 자바에서는 String 자료형인 경우에만 처리 가능.
NUMBER -> int -> 숫자 서식 지정 가능
VARCHAR2 -> String -> 문자열 서식 지정 가능
NUMBER + NULL -> String -> NULL이 아닌 경우에만 Integer.parseInt(String) 가능
VARCHAR2 + NULL -> String
--OUTER JOIN의 경우
SELECT sid, name, sub1, sub2, sub3, tot, ave
, ( CASE
WHEN ave>=60 AND sub1>=40 AND sub2>=40 AND sub3>=40 THEN 0 --합격
WHEN ave>=60 THEN 1 --과락
ELSE 2 --불합격
END ) AS ch
FROM (SELECT s.sid AS sid, name, sub1, sub2, sub3
, (sub1 + sub2 + sub3) AS tot
, ( (sub1 + sub2 + sub3) / 3 ) AS ave
FROM student s, grade g
WHERE s.sid = g.sid(+));
--> 뷰(View) 등록
CREATE OR REPLACE VIEW gradeView
AS
SELECT sid, name, sub1, sub2, sub3, tot, ave
, ( CASE
WHEN ave>=60 AND sub1>=40 AND sub2>=40 AND sub3>=40 THEN 0 --합격
WHEN ave>=60 THEN 1 --과락
ELSE 2 --불합격
END ) AS ch
FROM (SELECT s.sid AS sid, name, sub1, sub2, sub3
, (sub1 + sub2 + sub3) AS tot
, ( (sub1 + sub2 + sub3) / 3 ) AS ave
FROM student s, grade g
WHERE s.sid = g.sid(+));
--> 뷰를 이용한 성적 출력
SELECT sid, name, sub1, sub2, sub3, tot, ave, ch
FROM gradeView
ORDER BY sid;
--학생 삭제 쿼리
DELETE FROM student WHERE sid=1;
--성적 삭제 쿼리
DELETE FROM grade WHERE sid=1;
4. 프로그램 구성
//DBConn.java
//Student.java -> 자료형 클래스
//Grade.java -> 자료형 클래스
//StudentDAO.java -> 데이터베이스 액션
//GradeDAO.java -> 데이터베이스 액션
//StudentMain.java -> 서블릿 클래스. 학생 명단 출력 액션 처리. 메인 페이지. forward() 메소드.
//StudentMain.jsp -> 학생 명단 출력 페이지. JSTL&EL 표현. 삭제 버튼 추가.
//StudentInsertForm.java -> 서블릿 클래스. forward() 메소드.
//StudentInsertForm.jsp -> 학생 입력 폼 페이지. action="서블릿주소"
//StudentInsert.java -> 서블릿 클래스. 학생 입력 액션 처리. 액션 전용. sendRedirect() 메소드.
//StudentDelete.java -> 서블릿 클래스. 학생 삭제 액션 처리. 액션 전용. sendRedirect() 메소드.
//GradeMain.java -> 서블릿 클래스. 성적 출력 액션 처리. forward() 메소드
//GradeMain.jsp -> 성적 출력 페이지. JSTL&EL 표현. 삭제 버튼 추가
//GradeInsertForm.java -> 서블릿 클래스. forward() 메소드.
//GradeInsertForm.jsp -> 성적 입력 폼 페이지. JSTL&EL 표현. action="서블릿주소"
//GradeInsert.java -> 서블릿 클래스. 성적 입력 액션 처리. 액션 전용. sendRedirect() 메솓.
//GradeDelete.java -> 서블릿 클래스. 성적 삭제 액션 처리. 액션 전용. sendRedirect() 메솓.
//web.xml -> 서블릿 주소 등록
//요청주소
http://localhost:8090/프로젝트이름/StudentMain
5. 프로그램 소스 코드
//DBConn.java
package com.test;
import java.sql.*;
public class DBConn {
//Connection(데이터베이스 연결) 객체 저장용 변수 선언
private static Connection conn = null;
//1. 데이터베이스 연결 과정
public static Connection getConn()
throws ClassNotFoundException, SQLException {
//Connection 객체가 생성되지 않은 경우에만
//신규 객체를 생성한다. -> SingleTone
if (conn == null) {
//데이터베이스 연결 액션(로그인 과정)
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:아이디/패스워드@211.63.89.XX:1521:xe";
conn = DriverManager.getConnection(url);
}
return conn;
}
//2. 데이터베이스 연결 종료 과정
public static void close() throws SQLException {
if (conn != null) {
conn.close();
}
conn = null; //멤버변수를 NULL 값으로 초기화
}
}
//Student.java -> 자료형 클래스
package com.test;
public class Student {
private int sub;
private String sid, name, tel;
public int getSub() {
return sub;
}
public void setSub(int sub) {
this.sub = sub;
}
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;
}
}
//Grade.java -> 자료형 클래스
package com.test;
public class Grade {
private int ch;
private String sid, name, sub1, sub2, sub3, tot, ave;
public int getCh() {
return ch;
}
public void setCh(int ch) {
this.ch = ch;
}
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 getSub1() {
return sub1;
}
public void setSub1(String sub1) {
this.sub1 = sub1;
}
public String getSub2() {
return sub2;
}
public void setSub2(String sub2) {
this.sub2 = sub2;
}
public String getSub3() {
return sub3;
}
public void setSub3(String sub3) {
this.sub3 = sub3;
}
public String getTot() {
return tot;
}
public void setTot(String tot) {
this.tot = tot;
}
public String getAve() {
return ave;
}
public void setAve(String ave) {
this.ave = ave;
}
}
//StudentDAO.java -> 데이터베이스 액션
package com.test;
import java.util.*;
import java.sql.*;
public class StudentDAO {
//학생 명단 출력 메소드
public ArrayList<Student> list() {
ArrayList<Student> result = new ArrayList<Student>();
//1. 데이터베이스 연결
//2. SELECT 쿼리 준비 및 실행
//3. 결과를 컬렉션에 저장
//4. 데이터베이스 연결 마무리
//5. 컬렉션 반환
Connection conn = null;
Statement stmt = null;
try {
conn = DBConn.getConnection();
stmt = conn.createStatement();
//주의: 쿼리 문자열 끝에 ;(semicolon) 없음.
String sql = String.format("SELECT sid, name, tel, gradeCheck FROM studentView ORDER BY sid");
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
//문제)
//데이터베이스 자료를 순차적으로 읽어내서
//Student 객체에 저장하고
//Student 객체를 컬렉션에 저장하는 액션 추가
String sid = rs.getString("sid");
String name = rs.getString("name");
String tel = rs.getString("tel");
int gradeCheck = rs.getInt("gradeCheck");
Student s = new Student();
s.setSid(sid);
s.setName(name);
s.setTel(tel);
s.setGradeCheck(gradeCheck);
result.add(s);
}
rs.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if (stmt != null) {
stmt.close();
}
}catch(Exception e){
}
DBConn.close();
}
return result;
}
//학생 수 출력 메소드
public int count() {
int result = 0;
//1. 데이터베이스 연결
//2. SELECT 쿼리 준비 및 실행
//->SELECT COUNT(*) AS "count" FROM student
//3. 결과를 변수에 저장
//4. 데이터베이스 연결 마무리
//5. 변수 반환
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConnection();
String sql = String.format("SELECT COUNT(*) AS \"count\" FROM student");
pstmt = conn.prepareStatement(sql);
//바인딩 데이터 연결하는 과정
ResultSet rs = pstmt.executeQuery(); //SELECT 쿼리인 경우
while(rs.next()) {
result = rs.getInt("count");
}
rs.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if (pstmt != null) {
pstmt.close();
}
}catch(Exception e){
}
DBConn.close();
}
return result;
}
//학생 추가(입력) 메소드
public int add(Student student) {
int result = 0;
//문제)
//1. 데이터베이스 연결
//2. INSERT 쿼리 준비 및 실행
//-> INSERT INTO student (sid, name, tel)
// VALUES (studentSeq.nextval, ?, ?)
//3. 결과를 변수에 저장
//4. 데이터베이스 연결 마무리
//5. 변수 반환
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConnection();
String sql = String.format("INSERT INTO student (sid, name, tel) VALUES (studentSeq.nextval, ?, ?)");
pstmt = conn.prepareStatement(sql); //쿼리를 사전 분석한다.
pstmt.setString(1, student.getName()); //'(작은 따옴표) 추가
pstmt.setString(2, student.getTel()); //'(작은 따옴표) 추가
result = pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if (pstmt != null) {
pstmt.close();
}
}catch(Exception e){
}
DBConn.close();
}
return result;
}
//학생 번호 검색 메소드
public Student searchSid(String sid) {
Student result = null;
//문제)
//1. 데이터베이스 연결
//2. SELECT 쿼리 준비 및 실행
//SELECT sid, name, tel, gradeCheck
// FROM studentView
// WHERE sid=?
//3. 결과를 변수에 저장
//4. 데이터베이스 연결 마무리
//5. 변수 반환
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConnection();
//검색 액션은 PK 가 지정된 sid 컬럼을 대상으로
//하기 때문에
//검색 결과는 0 또는 1 이다.
//검색 결과가 있다면 Student 객체에 저장해서 반환.
String sql = String.format("SELECT sid, name, tel, gradeCheck FROM studentView WHERE sid=?");
pstmt = conn.prepareStatement(sql);
//바인딩 데이터 연결하는 과정
pstmt.setInt(1, Integer.parseInt(sid));
ResultSet rs = pstmt.executeQuery(); //SELECT 쿼리인 경우
//SELECT 결과가 존재하는 경우
//rs.next() 메소드 결과는 true가 된다.
//-> 반복문 실행 가능
/*
while(rs.next()) {
result = new Student();
}
*/
while(rs.next()) {
String name = rs.getString("name");
String tel = rs.getString("tel");
int gradeCheck = rs.getInt("gradeCheck");
result = new Student();
result.setSid(sid);
result.setName(name);
result.setTel(tel);
result.setGradeCheck(gradeCheck);
}
rs.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if (pstmt != null) {
pstmt.close();
}
}catch(Exception e){
}
DBConn.close();
}
return result; //null 또는 Student객체
}
//학생 삭제 메소드
public int remove(String sid) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConnection();
//DELETE FROM studentView WHERE sid=?
String sql = String.format("DELETE FROM studentView WHERE sid=?");
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sid);
result = pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if (pstmt != null) {
pstmt.close();
}
}catch(Exception e){
}
DBConn.close();
}
return result;
}
}
//GradeDAO.java -> 데이터베이스 액션
package com.test;
import java.sql.*;
import java.util.*;
public class GradeDAO {
public int count() {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConn();
String sql = "SELECT COUNT(*) AS count FROM studentView";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
result = rs.getInt("count");
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
DBConn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return result;
}
public ArrayList<Grade> list() {
ArrayList<Grade> result = new ArrayList<Grade>();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConn();
String sql = "SELECT sid, name, sub1, sub2, sub3, tot, ave, ch FROM gradeView ORDER BY sid";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Grade grade = new Grade();
grade.setSid(rs.getString("sid"));
grade.setName(rs.getString("name"));
grade.setSub1(rs.getString("sub1"));
grade.setSub2(rs.getString("sub2"));
grade.setSub3(rs.getString("sub3"));
grade.setTot(rs.getString("tot"));
grade.setAve(rs.getString("ave"));
grade.setCh(rs.getInt("ch"));
result.add(grade);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
DBConn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return result;
}
public String searchId(String sid) {
String result = "";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConn();
String sql = "SELECT name FROM studentView WHERE sid =?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(sid));
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
result = rs.getString("name");
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
DBConn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return result;
}
public int add(Grade grade) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBConn.getConn();
String sql = "INSERT INTO grade (sid, sub1, sub2, sub3) VALUES (?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, grade.getSid());
pstmt.setString(2, grade.getSub1());
pstmt.setString(3, grade.getSub2());
pstmt.setString(4, grade.getSub3());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
DBConn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return result;
}
}
//StudentMain.java -> 서블릿 클래스. 학생 명단 출력 액션 처리. 메인 페이지. forward() 메소드.
package com.test;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.*;
import javax.servlet.http.*;
public class StudentMain extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//데이터베이스 액션
StudentDAO dao = new StudentDAO();
int count = dao.count();
ArrayList<Student> list = dao.list();
//결과 전달 및 페이지 전환
request.setAttribute("count", count);
request.setAttribute("list", list);
RequestDispatcher dispatcher
= request.getRequestDispatcher("StudentMain.jsp");
dispatcher.forward(request, response);
}
}
//StudentMain.jsp -> 학생 명단 출력 페이지. JSTL&EL 표현
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>성적관리</title>
<link rel="stylesheet" type="text/css" href="TableStyle.css">
<link rel="stylesheet" type="text/css" href="DivStyle.css">
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$("#btnAdd").click(function() {
/* 요청주소에서 .jsp 사용 금지 */
$(location).attr("href", "StudentInsertForm");
});
$(".btnDelete").click(function() {
var sid = $(this).parent().parent().find("td:first").text();
if (confirm(sid+"번 학생을 삭제할까요?")){
/* 요청주소에서 .jsp 사용 금지 */
$(location).attr("href", "StudentDelete?sid="+sid);
}
});
});
</script>
</head>
<body>
<div>
<%-- 메인 메뉴 영역 --%>
<div class="title">
<h1>성적관리(Servlet버전)</h1>
<p>
<!-- 요청주소에서 .jsp 사용 금지 -->
<a href="StudentMain">[학생관리]</a>
<a href="GradeMain">[성적관리]</a>
</p>
</div>
<%-- 콘텐츠 영역 --%>
<div class="main">
<h2>--학생명단--</h2>
<form>
<input type="button" value="학생추가"
id="btnAdd">
</form>
<%-- <p>전체 학생 수 : 2명</p> --%>
<p>전체 학생수 : ${count}명</p>
<table id="t01" style = "text-align : center;">
<tr>
<th style="text-align: center;">번호</th>
<th>이름</th>
<th>전화번호</th>
<th>성적입력여부</th>
<th>삭제</th>
</tr>
<!-- <tr style="text-align: center;">
<td>1</td>
<td>Kim gildong</td>
<td>010-1234-1234</td>
<td>O</td>
<td>삭제버튼</td>
</tr> -->
<c:forEach var="s" items="${list}">
<tr style="text-align: center;">
<td>${s.sid}</td>
<td>${s.name}</td>
<td>${s.tel}</td>
<td>${s.gradeCheck==0?"X":"O"}</td>
<td>
<!-- 식별자 사용시 id="" 대신 class="" 사용할 것 -->
<input type="button" value="삭제"
class="btnDelete" ${s.gradeCheck==0?"":"disabled=\"disabled\""}>
</td>
</tr>
</c:forEach>
</table>
</div>
</div>
</body>
</html>
//StudentInsertForm.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class StudentInsertForm extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//페이지 전환
RequestDispatcher dispatcher
= request.getRequestDispatcher("StudentInsertForm.jsp");
dispatcher.forward(request, response);
}
}
//StudentInsertForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>성적관리</title>
<link rel="stylesheet" type="text/css" href="TableStyle.css">
<link rel="stylesheet" type="text/css" href="DivStyle.css">
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript">
//문제) jQuery 버전 작성
</script>
</head>
<body>
<div>
<%-- 메인 메뉴 영역 --%>
<div class="title">
<h1>성적관리(JSP버전)</h1>
<p>
<!-- 요청주소에서 .jsp 사용 금지 -->
<a href="StudentMain">[학생관리]</a>
<a href="GradeMain">[성적관리]</a>
</p>
</div>
<%-- 콘텐츠 영역 --%>
<div class="main">
<h2>학생 추가</h2>
<form>
<input type="button" value="학생명단"
id="btnList">
</form>
<!-- 요청주소에서 .jsp 사용 금지 -->
<form action="StudentInsert" method="post" id="myForm">
<table>
<tr>
<th>이름*</th>
<td style="text-align: left;">
<input type="text" name="name" id="name">(30자이내)
<span id="errMsg" style="color: red;"></span>
</td>
</tr>
<tr>
<th>전화번호</th>
<td><input type="text" name="tel" id="tel"></td>
</tr>
<tr>
<th></th>
<td><input type="submit" value="가입"></td>
</tr>
</table>
</form>
</div>
</div>
</body>
</html>
//StudentInsert.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class StudentInsert extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//액션 전용 페이지
//데이터 수신(한글 인코딩 추가)
request.setCharacterEncoding("UTF-8");
String name = request.getParameter("name");
String tel = request.getParameter("tel");
//데이터베이스 액션->add() 메소드 호출
StudentDAO dao = new StudentDAO();
Student s = new Student();
s.setName(name);
s.setTel(tel);
dao.add(s);
//서블릿 주소 지정
response.sendRedirect("StudentMain");
}
}
//StudentDelete.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class StudentDelete extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//액션 전용 페이지
//데이터 수신
String sid = request.getParameter("sid");
//데이터베이스 액션
StudentDAO dao = new StudentDAO();
dao.remove(sid);
//페이지 전환 (서블릿 주소 지정)
response.sendRedirect("StudentMain");
}
}
//web.xml -> 서블릿 주소 등록
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>Temp</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>
<!--
<servlet>
<servlet-name></servlet-name>
<servlet-class></servlet-class>
</servlet>
<servlet-mapping>
<servlet-name></servlet-name>
<url-pattern></url-pattern>
</servlet-mapping>
-->
<servlet>
<servlet-name>studentMain</servlet-name>
<servlet-class>com.test.StudentMain</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>studentMain</servlet-name>
<url-pattern>/StudentMain</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>studentInsertForm</servlet-name>
<servlet-class>com.test.StudentInsertForm</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>studentInsertForm</servlet-name>
<url-pattern>/StudentInsertForm</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>studentInsert</servlet-name>
<servlet-class>com.test.StudentInsert</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>studentInsert</servlet-name>
<url-pattern>/StudentInsert</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>studentDelete</servlet-name>
<servlet-class>com.test.StudentDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>studentDelete</servlet-name>
<url-pattern>/StudentDelete</url-pattern>
</servlet-mapping>
</web-app>
//요청주소
http://localhost:8090/프로젝트이름/StudentMain
----------------------------------------------------
문제) 성적 액션 작성 할 것.
- 성적 출력, 입력, 삭제
- GradeMain.java, GradeMain.jsp
- GradeInsertForm.java, GradeInsertForm.jsp
- GradeInsert.java
- GradeDelete.java
- web.xml
//GradeMain.java
package com.test;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.*;
import javax.servlet.http.*;
public class GradeMain extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
GradeDAO dao = new GradeDAO();
StudentDAO dao2 = new StudentDAO();
int count = dao2.count();
ArrayList<Grade> list = dao.list();
request.setAttribute("count", count);
request.setAttribute("list", list);
RequestDispatcher disPatcher = request.getRequestDispatcher("GradeMain.jsp");
disPatcher.forward(request, response);
}
}
//GradeMain.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "com.test.*" %>
<%@ taglib prefix="c" uri = "http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri = "http://java.sun.com/jsp/jstl/fmt" %>
<%--
StringBuilder sb = new StringBuilder();
int count = 0;
GradeDAO dao = new GradeDAO();
String[] array = {"합격","과락","불합격"};
//인원수 얻는 과정 추가
for(Grade g : dao.list()){
sb.append(String.format("<tr>"));
sb.append(String.format("<td>%s</td>", g.getSid()));
sb.append(String.format("<td>%s</td>", g.getName()));
sb.append(String.format("<td>%s</td>", ((g.getSub1()==null)?"X":g.getSub1())));
sb.append(String.format("<td>%s</td>", (g.getSub2()==null)?"X":g.getSub2()));
sb.append(String.format("<td>%s</td>", (g.getSub3()==null)?"X":g.getSub3()));
sb.append(String.format("<td>%s</td>", (g.getTot()==null)?"X":g.getTot()));
sb.append(String.format("<td>%s</td>", (g.getAve()==null)?"X":String.format("%.1f", Double.parseDouble(g.getAve()))));
sb.append(String.format("<td>%s</td>", (array[g.getCh()])));
sb.append(String.format("<td><input type = \"button\" value =\"성적 추가\" %s onclick = \"location.href = 'GradeInsertForm.jsp?sid=%s'\"></td>"
,(g.getSub1()==null)?"":"disabled=\"disabled\"", g.getSid()));
sb.append(String.format("</tr>"));
}
--%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<link rel = "stylesheet" type = "text/css" href = "TableStyle.css" >
<link rel = "stylesheet" type = "text/css" href = "DivStyle.css">
<script type="text/javascript">
$(document).ready(function(){
$(".btnInsert").click(function(){
var sid = $(this).parent().parent().find("td:first").text();
$(location).attr("href", "GradeInsertForm?sid="+sid);
});
$(".btnDelete").click(function(){
var sid = $(this).parent().parent().find("td:first").text();
if(confirm(sid+"번 성적을 삭제 하시겠습니까?")){
$(location).attr("href", "GradeDelete?sid="+sid);
}
});
});
</script>
</head>
<body>
<form>
<div>
<!-- 타이틀 영역 -->
<div class = "title">
<h1>성적관리 (JSP버전)</h1>
<p>
<a href = "StudentMain">학생 명단</a>
<a href = "GradeMain">성적 출력</a>
</p>
</div>
<!-- 컨텐츠 영역 -->
<div>
<h2>성적 출력</h2>
<p>전체 학생 수 : ${count} 명</p>
<table id = "t01">
<tr>
<th>번호</th><th>이름</th><th>과목1</th><th>과목2</th><th>과목3</th><th>총점</th><th>평균</th><th>판정</th><th>성적 입력</th><th>성적 삭제</th>
</tr>
<c:forEach var = "g" items ="${list}">
<tr>
<td>${g.sid}</td>
<td>${g.name}</td>
<td>${g.sub1==null?"X":g.sub1}</td>
<td>${g.sub2==null?"X":g.sub2}</td>
<td>${g.sub3==null?"X":g.sub3}</td>
<td>${g.tot==null?"X":g.tot}</td>
<%-- <td>${g.ave==null?"X":g.ave}</td> --%>
<td>
<c:choose>
<c:when test="${g.ave==null}"> X </c:when>
<c:when test="${g.ave!=null}"><fmt:formatNumber value="${g.ave}" pattern ="##.#"></fmt:formatNumber> </c:when>
</c:choose>
</td>
<td>
<c:choose>
<c:when test="${g.ch==0}"> 합격</c:when>
<c:when test="${g.ch==1}"> 과락</c:when>
<c:when test="${g.ch==2}"> 불합격</c:when>
</c:choose>
</td>
<td><input type="button" value = "성적입력" class="btnInsert"
${g.sub1==null?"":"disabled=\"disabled\""}
></td>
<td><input type="button" value = "성적삭제" class="btnDelete"
${g.sub1==null?"disabled=\"disabled\"":""}
></td>
</tr>
</c:forEach>
</table>
</div>
</div>
</form>
</body>
</html>
//GradeInsertForm.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class GradeInsertForm extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sid = request.getParameter("sid");
String name = "";
/* select name from studentview where sid = ? */
GradeDAO dao = new GradeDAO();
name = dao.getName(sid);
request.setAttribute("sid", sid);
request.setAttribute("name", name);
RequestDispatcher disPatcher = request.getRequestDispatcher("GradeInsertForm.jsp");
disPatcher.forward(request, response);
}
}
//GradeInsertForm.jsp
<%@page import="com.sun.xml.internal.txw2.Document"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "com.test.*" %>
<%--
String sid = request.getParameter("sid");
String name = "";
/* select name from studentview where sid = ? */
GradeDAO dao = new GradeDAO();
name = dao.getName(sid);
--%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<link rel = "stylesheet" type = "text/css" href = "TableStyle.css" >
<link rel = "stylesheet" type = "text/css" href = "DivStyle.css">
<script type="text/javascript">
$(document).ready(function(){
});
</script>
</head>
<body>
<form>
<div>
<!-- 타이틀 영역 -->
<div class = "title">
<h1>성적관리 (JSP버전)</h1>
<p>
<a href = "StudentMain.jsp">학생 명단</a>
<a href = "GradeMain.jsp">성적 출력</a>
</p>
</div>
</div>
</form>
<!-- 컨텐츠 영역 -->
<div>
<h2>성적 추가</h2>
<form action = "GradeInsert" method = "post" id ="myForm">
<table class = "t01">
<tr>
<th>번호*</th><td><input type = "text" value ="${sid}" readonly="readonly" name = "sid">ReadOnly</td>
</tr>
<tr>
<th>학생이름*</th><td><input type = "text" value ="${name} " readonly="readonly">ReadOnly</td>
</tr>
<tr>
<th>과목1</th><td><input type = "text" name = "sub1" id = "sub1">(0 ~ 100)</td>
</tr>
<tr>
<th>과목2</th><td><input type = "text" name = "sub2" id = "sub2">(0 ~ 100)</td>
</tr>
<tr>
<th>과목3</th><td><input type = "text" name = "sub3" id = "sub3">(0 ~ 100)</td>
</tr>
<tr>
<th></th>
<td style = "text-align: left; padding-left: 4pc;"><input type = "submit" value = "입력완료" ><span id = "errMsg" style = "color: red;"></td>
</tr>
</table>
</form>
</div>
</body>
</html>
//GradeInsert.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class GradeInsert extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String sid = request.getParameter("sid");
String sub1 = request.getParameter("sub1");
String sub2 = request.getParameter("sub2");
String sub3 = request.getParameter("sub3");
GradeDAO dao = new GradeDAO();
Grade g = new Grade();
g.setSid(sid);
g.setSub1(sub1);
g.setSub2(sub2);
g.setSub3(sub3);
dao.add(g);
response.sendRedirect("GradeMain");
/* RequestDispatcher disPatcher = request.getRequestDispatcher("");
disPatcher.forward(request, response);*/
}
}
//GradeDelete.java
package com.test;
import java.io.IOException;
import javax.servlet.*;
import javax.servlet.http.*;
public class GradeDelete extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGetPost(req, resp);
}
private void doGetPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sid = request.getParameter("sid");
GradeDAO dao = new GradeDAO();
dao.remove(sid);
response.sendRedirect("GradeMain");
/* RequestDispatcher disPatcher = request.getRequestDispatcher("");
disPatcher.forward(request, response);*/
}
}
//web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>Servlet_Temp</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>
<servlet>
<servlet-name>StudentMain</servlet-name>
<servlet-class>com.test.StudentMain</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentMain</servlet-name>
<url-pattern>/StudentMain</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>StudentInsert</servlet-name>
<servlet-class>com.test.StudentInsert</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentInsert</servlet-name>
<url-pattern>/StudentInsert</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>StudentDelete</servlet-name>
<servlet-class>com.test.StudentDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentDelete</servlet-name>
<url-pattern>/StudentDelete</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>StudentInsertForm</servlet-name>
<servlet-class>com.test.StudentInsertForm</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentInsertForm</servlet-name>
<url-pattern>/StudentInsertForm</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>GradeInsert</servlet-name>
<servlet-class>com.test.GradeInsert</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GradeInsert</servlet-name>
<url-pattern>/GradeInsert</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>GradeInsertForm</servlet-name>
<servlet-class>com.test.GradeInsertForm</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GradeInsertForm</servlet-name>
<url-pattern>/GradeInsertForm</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>GradeMain</servlet-name>
<servlet-class>com.test.GradeMain</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GradeMain</servlet-name>
<url-pattern>/GradeMain</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>GradeDelete</servlet-name>
<servlet-class>com.test.GradeDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GradeDelete</servlet-name>
<url-pattern>/GradeDelete</url-pattern>
</servlet-mapping>
</web-app>
------------------------------------------