[20121116] 4일차 (회원관리 + 성적처리)

Java/JSP & Servlet 2012. 12. 4. 17:43

share this post

-----------------------------------------

회원관리+성적처리 프로젝트

1. 성적 테이블, 회원 테이블 조인. FK 설정 필요.

2. 회원(회원번호-PK, 이름, 전화번호) -> 이전과 동일

3. 성적 (성적번호-PK, 회원번호-FK, 국어, 영어, 수학)

-> 이름 대신 회원번호-FK 설정

4. 회원에 회원 등록을 먼저하고, 성적에 성적 입력을 나중에 한다.

----------------------------------------

CREATE TABLE member2 (

mid NUMBER --PK

,name VARCHAR2(10)

,tel VARCHAR2(20)

);

ALTER TABLE member2

ADD CONSTRAINT member2_mid_pk PRIMARY KEY(mid);

CREATE SEQUENCE member2Seq;

//회원 테이블에 자료 입력. INSERT 쿼리.

//쿼리문의 끝에 ; 표시하지 말것.

INSERT INTO member2 (mid, name, tel) VALUES (member2Seq.nextval, 'hong', '111-1111');

INSERT INTO member2 (mid, name, tel) VALUES (member2Seq.nextval, 'park', '222-2222');

COMMIT;

----------------------------------------

CREATE TABLE score2 (

sid NUMBER --PK, 자동 번호 부여

,mid NUMBER --FK

,kor NUMBER(3) --CK (0~100)

,eng NUMBER(3) --CK (0~100)

,mat NUMBER(3) --CK (0~100)

);

--제약 조건 추가

ALTER TABLE score2

ADD CONSTRAINT score2_sid_pk PRIMARY KEY (sid);

ALTER TABLE score2

ADD CONSTRAINT score2_kor2_ck CHECK (kor BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_eng_ck CHECK (eng BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_mat_ck CHECK (mat BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_mid_fk FOREIGN KEY (mid)

REFERENCES member2(mid);

--자동 번호 부여 시퀀스 객체 생성

CREATE SEQUENCE score2Seq;

--INSERT 쿼리 샘플 (쿼리 끝 부분에 ; 표시하지 말 것)

INSERT INTO score2 (sid, mid, kor, eng, mat)

VALUES (score2Seq.nextval, 1, 100, 100, 100)

--SELECT 쿼리 샘플 (쿼리 끝 부분에 ; 표시하지 말 것)

SELECT m.mid AS mid, sid, name, kor, eng, mat

, (kor+eng+mat) AS tot

, (kor+eng+mat)/3 AS ave

, CASE

WHEN ((kor+eng+mat)/3 >= 60) AND (kor<40 OR eng<40 OR

mat<40) THEN '과락'

WHEN ((kor+eng+mat)/3 >= 60) THEN '합격'

ELSE '불합격'

END AS grade

FROM member2 m, score2 s

WHERE m.mid = s.mid(+)

--SELECT 쿼리 전용 뷰 생성(scott 계정)

CREATE OR REPLACE VIEW member2Score2View

SELECT m.mid AS mid, sid, name, kor, eng, mat

, (kor+eng+mat) AS tot

, (kor+eng+mat)/3 AS ave

, CASE

WHEN ((kor+eng+mat)/3 >= 60) AND (kor<40 OR eng<40 OR

mat<40) THEN '과락'

WHEN ((kor+eng+mat)/3 >= 60) THEN '합격'

ELSE '불합격'

END AS grade

FROM member2 m, score2 s

WHERE m.mid = s.mid(+);

--뷰를 이용한 SELECT 쿼리 실행

SELECT mid, sid, name, kor, eng, mat, tot, ave, grade

FROM member2Score2View;

----------------------------------------------

회원관리 - 회원 입, 출력 (수정, 삭제)

성적처리 - 성적 입, 출력 (수정, 삭제)

//DBConn.java

package com.test;

import java.sql.*;

public class DBConn {

//Singleton pattern

private static Connection dbConn;

public static Connection getConnection()

throws SQLException, ClassNotFoundException {

if (dbConn == null) {

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String user = "scott";

String pw = "tiger";

Class.forName("oracle.jdbc.driver.OracleDriver");

dbConn = DriverManager.getConnection(url, user, pw);

}

return dbConn;

}

public static void close()

throws SQLException {

if (dbConn != null) {

if (!dbConn.isClosed()) {

dbConn.close();

}

dbConn = null;

}

//Member2DTO.java

package com.test;

public class Member2DTO {

private int mid;

private String name, tel;

public int getMid() {

return mid;

}

public void setMid(int mid) {

this.mid = mid;

}

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;

}

//Score2DTO.java -> mid 컬럼

package com.test;

public class Score2DTO {

private int mid, sid; //추가

private String name;

private String kor, eng, mat; //int->String

private int tot;

private double ave;

private String grade;

//추가

public int getMid() {

return mid;

}

public void setMid(int mid) {

this.mid = mid;

}

public int getSid() {

return sid;

}

public void setSid(int sid) {

this.sid = sid;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getKor() {

return kor;

}

public void setKor(String kor) {

this.kor = kor;

}

public String getEng() {

return eng;

}

public void setEng(String eng) {

this.eng = eng;

}

public String getMat() {

return mat;

}

public void setMat(String mat) {

this.mat = mat;

}

public int getTot() {

return tot;

}

public void setTot(int tot) {

this.tot = tot;

}

public double getAve() {

return ave;

}

public void setAve(double ave) {

this.ave = ave;

}

public String getGrade() {

return grade;

}

public void setGrade(String grade) {

this.grade = grade;

}

//Member2DAO.java

package com.test;

import java.sql.*;

import java.util.*;

public class Member2DAO {

private Connection conn;

public void connect()

throws SQLException, ClassNotFoundException {

//연결 전용 메소드

conn = DBConn.getConnection();

}

public void close()

throws SQLException {

//연결 해제 전용 메소드

DBConn.close();

conn = null;

}

public int add(Member2DTO dto)

throws SQLException {

//INSERT 쿼리 실행

int result = 0;

String sql = String.format("INSERT INTO member2 (mid, name, tel) VALUES (member2Seq.nextval, '%s', '%s')", dto.getName(), dto.getTel());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public int count()

throws SQLException {

//SELECT 쿼리 실행

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM member2");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) { //row 단위 접근

result = rs.getInt("count"); //column 단위 접근

}

rs.close();

return result;

}

public ArrayList<Member2DTO> lists()

throws SQLException {

//SELECT 쿼리 실행

ArrayList<Member2DTO> arrayList

= new ArrayList<Member2DTO>();

String sql = String.format("SELECT mid, name, tel FROM member2 ORDER BY mid");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

//결과집합(ResultSet)을 리턴해야 된다.

//결과집합은 특성상 이 메소드를 벗어날 수 없다.

//결과집합을 컬렉션에 저장

//컬렉션을 리턴.

while(rs.next()) { //row 단위 접근

int mid = rs.getInt("mid"); //column 단위 접근

String name = rs.getString("name");

String tel = rs.getString("tel");

Member2DTO dto = new Member2DTO();

dto.setMid(mid);

dto.setName(name);

dto.setTel(tel);

arrayList.add(dto);

}

rs.close();

return arrayList;

}

public int remove(int mid)

throws SQLException {

//DELETE 쿼리 실행

int result = 0;

String sql = String.format("DELETE member2 WHERE mid=%d", mid);

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public Member2DTO search(int mid)

throws SQLException {

Member2DTO dto = null;

//문제) mid 를 전달받아서 Member2DTO를 리턴하는 프로그램 작성.

//SELECT mid, name, tel FROM member2 WHERE mid=2

String sql = String.format("SELECT mid, name, tel FROM member2 WHERE mid=%d", mid);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) { //row 단위 접근

String name = rs.getString("name");

String tel = rs.getString("tel");

dto = new Member2DTO();

dto.setMid(mid);

dto.setName(name);

dto.setTel(tel);

}

rs.close();

return dto;

}

//Score2DAO.java -> mid 컬럼. null 값 처리.

package com.test;

import java.sql.*;

import java.util.*;

public class Score2DAO {

private Connection conn;

public void connect()

throws SQLException, ClassNotFoundException{

conn = DBConn.getConnection();

}

public void close()

throws SQLException{

DBConn.close();

conn = null;

}

public int add(Score2DTO dto)

throws SQLException{

int rowCount =0;

String sql = String.format("INSERT INTO score2 (sid, mid, kor, eng, mat) VALUES (score2Seq.nextval, %d, %s, %s, %s)", dto.getMid(), dto.getKor(), dto.getEng(), dto.getMat()); //%d->%s

Statement stmt = conn.createStatement();

rowCount = stmt.executeUpdate(sql);

return rowCount;

}

public ArrayList<Score2DTO> lists()

throws SQLException{

ArrayList<Score2DTO> arrayList = new ArrayList<Score2DTO>();

String sql = String.format("SELECT mid, sid, name, kor, eng, mat, tot, ave, grade FROM member2Score2View");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()){

//sid, kor, eng, mat, tot, ave 등은

//null을 받을 수도 있다.

//null인 경우 getInt()는 0을 리턴한다.

int mid = rs.getInt("mid");

int sid = rs.getInt("sid");

String name = rs.getString("name");

String kor = rs.getString("kor"); //int->String

String eng = rs.getString("eng"); //int->String

String mat = rs.getString("mat"); //int->String

int tot = rs.getInt("tot");

double ave = rs.getDouble("ave");

String grade = rs.getString("grade");

Score2DTO dto = new Score2DTO();

dto.setMid(mid);

dto.setSid(sid);

dto.setName(name);

dto.setKor(kor);

dto.setEng(eng);

dto.setMat(mat);

dto.setTot(tot);

dto.setAve(ave);

dto.setGrade(grade);

arrayList.add(dto);

}

rs.close();

return arrayList;

}

//BasicStyle.css

@CHARSET "EUC-KR";

body {

color:#2f3743;

font-size:10pt;

}

a {

text-decoration:none;

color:gray;

}

a:hover {

text-decoration:underline;

color:red;

}

table.style00 td, table.style00 th

{

padding: 3px;

border-right-style: solid;

border-bottom-style: solid;

border-width: 1px;

border-color: #6980b8;

}

table.style00 td:hover

{

background-color:#d2d2d2;

}

//Member2List.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" %>

<%@ page import="com.test.*" %>

StringBuilder str = new StringBuilder();

Member2DAO dao = new Member2DAO();

try {

dao.connect();

for (Member2DTO dto : dao.lists()) {

str.append("<tr>");

str.append(String.format("<td class=\"style1\">%d</td>", dto.getMid()));

str.append(String.format("<td class=\"style1\">%s</td>", dto.getName()));

str.append(String.format("<td class=\"style1\">%s</td>", dto.getTel()));

str.append("</tr>");

}

}catch(Exception e){

}finally{

dao.close();

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

.style1 {

text-align: center;

}

</style>

</head>

<body>

<div>

[<a href="Member2List.jsp">회원관리</a>]

[<a href="Score2List.jsp">성적관리</a>]

<hr>

</div>

<div>

<tbody>

<%=str%>

</tbody>

</table>

</div>

</body>

</html>

//Member2InsertForm.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

function myFunc() {

obj = document.getElementById("myForm");

//데이터 검사

var name = document.getElementById("name");

var tel = document.getElementById("tel");

var msg = document.getElementById("msg");

msg.style.display = "none";

//빈 칸 검사

if (name.value == ""

|| tel.value == "") {

msg.style.display = "inline";

return;

}

//데이터 전송

obj.submit();

}

</script>

</head>

<body>

<div>

[<a href="Member2List.jsp">회원관리</a>]

[<a href="Score2List.jsp">성적관리</a>]

<hr>

</div>

<div>

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

[<a href="javascript:myFunc()">등록</a>]

[<a href="Member2List.jsp">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//Member2Insert.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" %>

<%@ page import="com.test.*" %>

request.setCharacterEncoding("euc-kr");

String name = request.getParameter("name");

String tel = request.getParameter("tel");

Member2DAO dao = new Member2DAO();

try {

dao.connect();

Member2DTO dto = new Member2DTO();

dto.setName(name);

dto.setTel(tel);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

dao.close();

}

String url = String.format("Member2List.jsp");

response.sendRedirect(url);

//Score2List.jsp -> 회원 명단 및 성적 출력 페이지

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" %>

<%@ page import="com.test.*" %>

StringBuilder str = new StringBuilder();

Score2DAO dao = new Score2DAO();

try {

dao.connect();

for (Score2DTO dto : dao.lists()) {

str.append("<tr>");

str.append(String.format("<td class=\"style1\">%d</td>", dto.getMid()));

str.append(String.format("<td class=\"style1\">%s</td>", dto.getName()));

str.append(String.format("<td class=\"style2\">%s</td>", (dto.getKor()==null)?"x":dto.getKor())); //%d->%s

str.append(String.format("<td class=\"style2\">%s</td>", (dto.getEng()==null)?"x":dto.getEng())); //%d->%s

str.append(String.format("<td class=\"style2\">%s</td>", (dto.getMat()==null)?"x":dto.getMat())); //%d->%s

str.append(String.format("<td class=\"style2\">%d</td>", dto.getTot()));

str.append(String.format("<td class=\"style2\">%.1f</td>", dto.getAve()));

str.append(String.format("<td class=\"style1\">%s</td>", dto.getGrade()));

if (dto.getKor() != null) {

str.append(String.format("<td class=\"style1\">[입력]</td>"));

} else {

//입력 폼 화면으로 전환시

//mid(회원의 고유 번호)를 반드시 전달해야 한다.

//성적 입력시 필요한 중요 정보임.

str.append(String.format("<td class=\"style1\">[<a href=\"Score2InsertForm.jsp?mid=%d\">입력</a>]</td>", dto.getMid()));

}

str.append("</tr>");

}

}catch(Exception e){

}finally{

dao.close();

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

.style1 {

text-align: center;

}

.style2 {

text-align: right;

}

</style>

</head>

<body>

<div>

[<a href="Member2List.jsp">회원관리</a>]

[<a href="Score2List.jsp">성적관리</a>]

<hr>

</div>

<div>

<tbody>

<%=str%>

</tbody>

</table>

</div>

</body>

</html>

//Score2InsertForm.jsp -> 회원별 성적 입력 화면 페이지

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" %>

<%@ page import="com.test.*" %>

String mid = request.getParameter("mid");

String name = "";

//문제) mid를 가지고 이름을 검색하는 쿼리 작성

//Member2DAO dao = new Member2DAO();

//Member2DTO dto = dao.search(Integer.parseInt(mid));

Member2DAO dao = new Member2DAO();

try {

dao.connect();

Member2DTO dto = dao.search(Integer.parseInt(mid));

name = dto.getName();

}catch(Exception e){

}finally{

dao.close();

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

function myFunc() {

obj = document.getElementById("myForm");

//문제) 데이터 검사 과정 추가

var kor = document.getElementById('kor');

var eng = document.getElementById("eng");

var mat = document.getElementById("mat");

var msg = document.getElementById("msg");

msg.style.display = "none";

//빈칸 검사

if(kor.value == "" || eng.value ==""|| mat.value==""){

msg.style.display = "inline";

return;

}

//숫자 검사

if (kor.value.match(/[^0-9]/) || eng.value.match(/[^0-9]/)||mat.value.match(/[^0-9]/)){

msg.style.display = "inline";

return;

}

//범위 검사

if(parseInt(kor.value)>100 ||parseInt(eng.value)>100 ||parseInt(mat.value)>100 ){

msg.style.display = "inline";

return;

}

//데이터 전송

obj.submit();

}

</script>

</head>

<body>

<div>

[<a href="Member2List.jsp">회원관리</a>]

[<a href="Score2List.jsp">성적관리</a>]

<hr>

</div>

<div>

<%-- 성적 입력을 위해서 반드시 전송해야 되는 값 --%>

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

[<a href="javascript:myFunc()">등록</a>]

[<a href="Score2List.jsp">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//Score2Insert.jsp -> 회원의 성적 입력 액션 처리

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="java.sql.*" %>

<%@ page import="com.test.*" %>

String mid = request.getParameter("mid");

String kor = request.getParameter("kor");

String eng = request.getParameter("eng");

String mat = request.getParameter("mat");

Score2DAO dao = new Score2DAO();

try {

dao.connect();

Score2DTO dto = new Score2DTO();

dto.setMid(Integer.parseInt(mid));

dto.setKor(kor);

dto.setEng(eng);

dto.setMat(mat);

dao.add(dto);

}catch(Exception e){

System.out.println(e.toString());

}finally{

dao.close();

}

String url = String.format("Score2List.jsp");

response.sendRedirect(url);

------------------------------------

회원관리+성적처리 프로젝트 완성됨.

'Java > JSP & Servlet' 카테고리의 다른 글

[20121120] 6일차 (직원관리) (0)	2012.12.04
[20121119] 5일차 (직원관리) (0)	2012.12.04
[20121115] 3일차 (JSP와 JDBC 연동, DTO클래스와 DAO클래스 연동 코딩) (0)	2012.11.15
[20121114] 2일차 (POST 전송방식, GET 전송방식, forward(), sendRedirect()) (0)	2012.11.15
[20121113] 1일차 (JSP, JSP기본문법, JSP데이터송수신) (0)	2012.11.15

WRITTEN BY

: 빨강꼬마

[20121116] 4일차 (회원관리 + 성적처리)

'Java > JSP & Servlet' 카테고리의 다른 글

티스토리툴바