[20121227] 7일차 (회원관리+성적처리 프로젝트 (Spring, iBatis 버전))

Java/Spring, iBatis 2012. 12. 28. 15:55

share this post

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

회원관리+성적처리 프로젝트 (Spring, iBatis 버전)

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

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

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

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

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

5. 입력, 출력, 수정, 삭제 기능을 모두 구현할 것.

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

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;

--성적 등록 여부 확인용 쿼리

SELECT mid, name, tel

, (SELECT COUNT(*) FROM score2 WHERE mid=m.mid) AS count

FROM member2 m

ORDER BY mid;

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

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;

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

//MemberDTO.java -> 회원 정보 저장용 클래스

//ScoreDTO.java -> 성적 정보 저장용 클래스

//memberScoreIbatis.xml -> iBatis용 쿼리 객체 저장소

//sqlMapConfig.xml -> 데이터베이스 연결 정보 지정, iBatis XML 파일 등록.

//MemberController.java -> 컨트롤러클래스. 데이터베이스 액세스 전용 클래스

//ScoreController.java -> 컨트롤러클래스. 데이터베이스 액세스 전용 클래스

//BasicStyle.css

//memberList.jsp -> JSTL, EL 표현으로 대체

//memberInsertForm.jsp

//memberUpdateForm.jsp -> JSTL, EL 표현으로 대체

//scoreList.jsp -> JSTL, EL 표현으로 대체

//scoreInsertForm.jsp

//scoreUpdateForm.jsp -> JSTL, EL 표현으로 대체

//요청주소

http://localhost:8090/MemberScore_Spring_20121227/memberList.mvc

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

//MemberDTO.java -> 회원 정보 저장용 클래스

package com.test;

public class MemberDTO {

private String mid;

private String name, tel;

//성적이 등록된 상태인지 확인하기 위한 변수 추가

private int count;

public int getCount() {

return count;

}

public void setCount(int count) {

this.count = count;

}

public String getMid() {

return mid;

}

public void setMid(String 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;

}

//ScoreDTO.java -> 성적 정보 저장용 클래스

package com.test;

public class ScoreDTO {

//null 데이터 처리를 위해서 멤버변수의 자료형을 String으로 처리함.

private String mid, sid; //추가

private String name;

private String kor, eng, mat;

private String tot;

private String ave;

private String grade;

//추가

public String getMid() {

return mid;

}

public void setMid(String mid) {

this.mid = mid;

}

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 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 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;

}

public String getGrade() {

return grade;

}

public void setGrade(String grade) {

this.grade = grade;

}

//memberScoreIbatis.xml -> iBatis용 쿼리 객체 저장소

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap

PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

"http://ibatis.apache.org/dtd/sql-map-2.dtd">

INSERT INTO member2 (mid, name, tel)

VALUES (member2Seq.nextval, #name#, #tel#)

</insert>

SELECT COUNT(*) AS count FROM member2

</select>

SELECT mid, name, tel, (SELECT COUNT(*) FROM score2 WHERE mid=m.mid) AS count FROM member2 m ORDER BY mid

</select>

DELETE member2 WHERE mid=#mid#

</delete>

SELECT mid, name, tel FROM member2 WHERE mid=#mid#

</select>

UPDATE member2 SET name=#name#, tel=#tel# WHERE mid=#mid#

</update>

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

VALUES (score2Seq.nextval, #mid#, #kor#, #eng#, #mat#)

</insert>

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

FROM member2Score2View

</select>

DELETE score2 WHERE mid=#mid#

</delete>

SELECT kor, eng, mat FROM score2 WHERE mid=#mid#

</select>

UPDATE score2 SET kor=#kor#, eng=#eng#, mat=#mat# WHERE mid=#mid#

</update>

</sqlMap>

//sqlMapConfig.xml -> 데이터베이스 연결 정보 지정, iBatis XML 파일 등록.

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig

PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"

"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<settings

cacheModelsEnabled="false"

useStatementNamespaces="true"/>

</sqlMapConfig>

//MemberController.java -> 컨트롤러클래스. 데이터베이스 액세스 전용 클래스

package com.test;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import com.spring.dao.CommonDAO;

@Controller("memberController")

public class MemberController {

//iBatis 연결 객체인 IBatisDaoImpl 클래스를 dao 변수와 연결시킨다.

@Resource(name="dao")

private CommonDAO dao;

@RequestMapping(value="/memberList.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String memberList(MemberDTO dto, HttpServletRequest req) {

try {

List<Object> arrayList = dao.getListData("mySql.listsMember");

req.setAttribute("arrayList", arrayList);

} catch (Exception e) {

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

}

return "memberList.jsp";

}

@RequestMapping(value="/memberInsertForm.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String memberInsertForm() {

//forward() 메소드 역할

return "memberInsertForm.jsp";

}

@RequestMapping(value="/memberInsert.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String memberInsert(MemberDTO dto, HttpServletRequest req) {

try {

dao.insertData("mySql.addMember", dto);

} catch (Exception e) {

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

}

//sendRedirect() 메소드 역할

return "redirect:memberList.mvc";

}

//ScoreController.java -> 컨트롤러클래스. 데이터베이스 액세스 전용 클래스

package com.test;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import com.spring.dao.CommonDAO;

@Controller("scoreController")

public class ScoreController {

//iBatis 연결 객체인 IBatisDaoImpl 클래스를 dao 변수와 연결시킨다.

@Resource(name="dao")

private CommonDAO dao;

@RequestMapping(value="/scoreList.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String scoreList(ScoreDTO dto, HttpServletRequest req) {

try {

List<Object> arrayList = dao.getListData("mySql.listsScore");

req.setAttribute("arrayList", arrayList);

} catch (Exception e) {

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

}

return "scoreList.jsp";

}

@RequestMapping(value="/scoreInsertForm.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String scoreInsertForm(ScoreDTO dto, HttpServletRequest req) {

try {

Object newdto = dao.getReadData("mySql.searchMember", dto.getMid());

req.setAttribute("dto", newdto);

} catch (Exception e) {

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

}

//forward() 메소드 역할

return "scoreInsertForm.jsp";

}

@RequestMapping(value="/scoreInsert.mvc", method={RequestMethod.GET, RequestMethod.POST})

public String scoreInsert(ScoreDTO dto) {

try {

dao.insertData("mySql.addScore", dto);

} catch (Exception e) {

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

}

//sendRedirect() 메소드 역할

return "redirect:scoreList.mvc";

}

//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;

}

#result {

overflow:auto; /* DIV 태그 내부에 스크롤 생성 */

height:100px;

}

//memberList.jsp -> JSTL, EL 표현으로 대체

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

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!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>

function deleteForm(mid) {

if (confirm("선택한 자료를 삭제할까요?")) {

window.location.href="memberDelete.mvc?mid="+mid;

}

</script>

</head>

<div>

[<a href="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

<tbody>

<c:forEach var="dto" items="${arrayList}">

<tr>

<c:choose>

<c:when test="${dto.count > 0}">[삭제]</c:when>

<c:otherwise><a href="javascript:deleteForm(${dto.mid})">[삭제]</a></c:otherwise>

</c:choose>

</td>

</tr>

</c:forEach>

</tbody>

</table>

</div>

</body>

</html>

//memberInsertForm.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="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

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

[<a href="memberList.mvc">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//memberUpdateForm.jsp -> JSTL, EL 표현으로 대체

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

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!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="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

<%-- 회원 수정을 위해서 반드시 전송해야 되는 값 --%>

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

[<a href="javascript:myFunc()">수정</a>]

[<a href="memberList.mvc">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//scoreList.jsp -> JSTL, EL 표현으로 대체

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

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>

<!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>

function deleteForm(mid) {

if (confirm("선택한 자료를 삭제할까요?")) {

window.location.href="scoreDelete.mvc?mid="+mid;

}

</script>

</head>

<body>

<div>

[<a href="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

<tbody>

<c:forEach var="dto" items="${arrayList}">

<tr>

<c:choose>

<c:when test="${empty dto.kor}">X</c:when>

<c:otherwise>${dto.kor}</c:otherwise>

</c:choose>

</td>

<c:choose>

<c:when test="${empty dto.eng}">X</c:when>

<c:otherwise>${dto.eng}</c:otherwise>

</c:choose>

</td>

<c:choose>

<c:when test="${empty dto.mat}">X</c:when>

<c:otherwise>${dto.mat}</c:otherwise>

</c:choose>

</td>

<c:choose>

<c:when test="${empty dto.tot}">X</c:when>

<c:otherwise>${dto.tot}</c:otherwise>

</c:choose>

</td>

<c:choose>

<c:when test="${empty dto.ave}">X</c:when>

<c:otherwise><fmt:formatNumber value="${dto.ave}" pattern="0.0"></fmt:formatNumber></c:otherwise>

</c:choose>

</td>

<td class="style1">${dto.grade}</td>

<c:choose>

<c:when test="${empty dto.kor}">

<a href="scoreInsertForm.mvc?mid=${dto.mid}">[입력]</a> [수정] [삭제]

</c:when>

<c:otherwise>

[입력] <a href="scoreUpdateForm.mvc?mid=${dto.mid}">[수정]</a> <a href="javascript:deleteForm(${dto.mid})">[삭제]</a>

</c:otherwise>

</c:choose>

</td>

</tr>

</c:forEach>

</tbody>

</table>

</div>

</body>

</html>

//scoreInsertForm.jsp

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

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!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="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

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

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

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

[<a href="scoreList.mvc">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//scoreUpdateForm.jsp -> JSTL, EL 표현으로 대체

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

pageEncoding="EUC-KR"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!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="memberList.mvc">회원관리</a>]

[<a href="scoreList.mvc">성적관리</a>]

<hr>

</div>

<div>

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

<table>

<tbody>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

[<a href="javascript:myFunc()">수정</a>]

[<a href="scoreList.mvc">취소</a>]<br>

</td>

</tr>

</tbody>

</table>

</form>

</div>

</body>

</html>

//요청주소

http://localhost:8090/MemberScore_Spring_20121227/memberList.mvc

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

문제) 성적 수정, 삭제와 회원 수정, 삭제 과정은 각자 작성할 것.

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

문제) 직원관리 Spring, iBatis 버전 작성. 팀별 작업.

-> 새 프로젝트 생성(EmployeeSpring_20121227)

-> 메뉴 구성 추가

-> emp.sql 쿼리를 먼저 실행해서 환경 구성할 것.

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

--지역 검색 쿼리

SELECT city_id, city FROM city ORDER BY city_id

--부서 검색 쿼리

SELECT buseo_id, buseo FROM buseo ORDER BY buseo_id

--직위 검색 쿼리

SELECT jikwi_id, jikwi FROM jikwi ORDER BY jikwi_id

--직위별 최소 기본급 검색 쿼리

SELECT min_basicpay FROM jikwi WHERE jikwi_id=9

--입력 쿼리

INSERT INTO emp (emp_id, name, ssn, ibsadate, city_id, tel, buseo_id, jikwi_id, basicpay, sudang)

VALUES (empSeq.nextval

, '홍길동'

, '771212-1022432'

, '1998-10-11'

, 6

, '011-2356-4528'

, 7

, 9

, 2610000

, 200000)

--직원 명단 출력 쿼리(emp 테이블이 아니라 empView 이용)

SELECT emp_id, name, ssn, TO_CHAR(ibsadate, 'YYYY-MM-DD') AS ibsadate

, city_id, city, tel, buseo_id, buseo, jikwi_id, jikwi, basicpay

, sudang, pay

FROM empView

ORDER BY emp_id ASC

--전체 직원수 출력 쿼리 (emp 테이블이나 empView나 결과 동일함)

SELECT COUNT(*) AS count

FROM emp

--검색 쿼리(emp 테이블이 아니라 empView 이용)

SELECT emp_id, name, ssn, TO_CHAR(ibsadate, 'YYYY-MM-DD') AS ibsadate

, city_id, city, tel, buseo_id, buseo, jikwi_id, jikwi, basicpay

, sudang, pay

FROM empView

WHERE emp_id = 1001

ORDER BY emp_id ASC

--수정 쿼리

UPDATE emp

SET name='새로운 이름'

, ssn='새로운 주민번호'

, ibsadate='새로운 입사일'

, city_id=1

, tel='새로운 전화번호'

, buseo_id=1

, jikwi_id=1

, basicpay=1

, sudang=1

WHERE emp_id=1001

--삭제 쿼리

DELETE emp

WHERE emp_id=1001

--직원용 ID(이름), PW(주민번호 뒷자리) 검사 쿼리

SELECT COUNT(*) AS count

FROM emp

WHERE name='이순신' AND SUBSTR(ssn, 8, 7)='1544236';

--직원 전용 employeeView

CREATE OR REPLACE VIEW employeeView

SELECT emp_id, name, ibsadate, c.city_id AS city_id, city, tel, b.buseo_id AS buseo_id, buseo, j.jikwi_id AS jikwi_id, jikwi

FROM emp e, city c, buseo b, jikwi j

WHERE e.city_id=c.city_id AND e.buseo_id=b.buseo_id AND e.jikwi_id=j.jikwi_id

ORDER BY emp_id ASC;

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

--관리자 전용 테이블

CREATE TABLE empAdmin (

id VARCHAR2(20) --PK

,pw VARCHAR2(20) --암호화. encrypt() 사용자 정의 함수 이용.

);

ALTER TABLE empAdmin

ADD CONSTRAINT empAdmin_id_pk PRIMARY KEY(id);

INSERT INTO empAdmin (id, pw) VALUES ('admin', encrypt('1234', 'admin'));

INSERT INTO empAdmin (id, pw) VALUES ('hong', encrypt('1234', 'hong'));

COMMIT;

SELECT id, pw FROM empAdmin;

--관리자용 ID, PW 검사 쿼리

SELECT COUNT(*) AS count FROM empAdmin

WHERE id='admin' AND pw=encrypt('1234', 'admin');

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

//EmployeeDTO.java

//CityDTO.java

//BuseoDTO.java

//JikwiDTO.java

//EmployeeIbatis.xml

//CityIbatis.xml

//BuseoIbatis.xml

//JikwiIbatis.xml

//sqlMapConfig.xml

//CityController.java -> 지역관리용 컨트롤러 클래스.

//City.jsp

//BuseoController.java -> 부서관리용 컨트롤러 클래스

//Buseo.jsp

//JikwiController.java -> 직위관리용 컨트롤러 클래스

//Jikwi.jsp

//BasicStyle.css

//EmployeeController.java -> 직원관리용 컨트롤러 클래스

//Employee_Select.jsp -> JSTL, EL 표기 사용

//Employee_Search.jsp

//Employee_InsertForm.jsp

//Employee_UpdateForm.jsp

//Employee_DeleteForm.jsp

//LoginForm.jsp

//MainMenu.jsp -> 메인 메뉴 전용.

//요청주소

http://localhost:8090/EmployeeSpring_20121227/loginForm.mvc

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

'Java > Spring, iBatis' 카테고리의 다른 글

[20121228] 8일차 (SpringMVC Annotation, iBatis) (0)	2012.12.28
[20121226] 6일차(Spring, iBatis) (0)	2012.12.28

WRITTEN BY

: 빨강꼬마

[20121227] 7일차 (회원관리+성적처리 프로젝트 (Spring, iBatis 버전))

'Java > Spring, iBatis' 카테고리의 다른 글

티스토리툴바