Spring MVC 실습 3-1(DB)

유동현·2022년 11월 16일
dbspring mvc실습
0

스프링MVC

목록 보기
4/13
post-thumbnail

1. 실습 테이블 생성

Region

--○실습 테이블 생성(지역)
CREATE TABLE REGION
( REGIONID  NUMBER      --지역 아이디  -- PK
, REGIONNAME    VARCHAR2(30)  --지역 이름
, CONSTRAINT    REGION_ID_PK PRIMARY KEY(REGIONID)
);

뷰 생성

--○ 지역 정보 조회 쿼리문 구성
--  특정 지역 데이터 삭제 가능 여부 확인

SELECT R.REGIONID AS REGIONID
   , R.REGIONNAME AS REGIONNAME
   , ( SELECT COUNT(*)
   FROM EMPLOYEE
   WHERE REGIONID = R.REGIONID) AS DELCHECK
FROM REGION R;

SELECT R.REGIONID AS REGIONID, R.REGIONNAME AS REGIONNAME, ( SELECT COUNT(*) FROM EMPLOYEE WHERE REGIONID = R.REGIONID) AS DELCHECK FROM REGION R
;

/*
1	서울	1
2	인천	0
3	대구	0
4	광주	0
5	대전	0
*/
-- 서울 의 지역 정보는 삭제가 불가능한 데이터이며
-- 그 외 나머지 지역 정보는 삭제가 가능한 데이터임을 판별할 수 있도록 구성한 쿼리문,

--※ 뷰 생성 (지역 정보 : REGIONVIEW)
CREATE OR REPLACE VIEW REGIONVIEW
AS
SELECT R.REGIONID AS REGIONID
   , R.REGIONNAME AS REGIONNAME
   , ( SELECT COUNT(*)
   FROM EMPLOYEE
   WHERE REGIONID = R.REGIONID) AS DELCHECK
FROM REGION R;
--View REGIONVIEW이(가) 생성되었습니다.



Position

--○ 실습 테이블 생성
CREATE TABLE POSITION
( POSITIONID    NUMBER      -- 직위 아이디   --PK
, POSITIONNAME VARCHAR2(30) -- 직위 이름
, MINBASICPAY   NUMBER      --해당 직위의 최소 기본급
, CONSTRAINT POSITION_ID_PK PRIMARY KEY(POSITIONID)
);

뷰 생성


--※ 뷰 생성 (직위 정보 : POSITIONVIEW)

CREATE OR REPLACE VIEW POSITIONVIEW
AS
SELECT P.POSITIONID AS POSITIONID
        , P.POSITIONNAME AS POSITIONNAME
        , P.MINBASICPAY AS MINBASICPAY
        , (SELECT COUNT(*)
          FROM EMPLOYEE
          WHERE POSITIONID = P.POSITIONID) AS DELCHECK
FROM POSITION P;



Department

CREATE TABLE DEPARTMENT
( DEPARTMENTID  NUMBER      --부서 아이디 --PK
, DEPARTMENTNAME VARCHAR2(30)   --부서 이름
, CONSTRAINT DEPARTMENT_ID_PK PRIMARY KEY(DEPARTMENTID)
);

뷰 생성

--○ 부서 정보 조회 쿼리문 구성
--  특정 부서 데이터 삭제 가능 여부 확인
SELECT D.DEPARTMENTID AS DEPARTMENTID
    , D.DEPARTMENTNAME AS DEPARTMENTNAME
    ,(SELECT COUNT(*)
    FROM EMPLOYEE
    WHERE DEPARTMENTID = D.DEPARTMENTID) AS DELCHECK
FROM DEPARTMENT D;

--※ 뷰 생성 (부서 정보 : DEPARTMENTVIEW)
CREATE OR REPLACE VIEW DEPARTMENTVIEW
AS
SELECT D.DEPARTMENTID AS DEPARTMENTID
    , D.DEPARTMENTNAME AS DEPARTMENTNAME
    ,(SELECT COUNT(*)
    FROM EMPLOYEE
    WHERE DEPARTMENTID = D.DEPARTMENTID) AS DELCHECK
FROM DEPARTMENT D;
--View DEPARTMENTVIEW이(가) 생성되었습니다.

SELECT * 
FROM DEPARTMENTVIEW;
/*
1	개발부	1
2	기획부	0
3	영업부	0
*/



Employee

--○실습 테이블 생성 (EMPLOYEE)
--사원번호, 사원명, 주민번호, 생년월일, 양음력, 전화번호, 부서, 직위, 지역, 기본급, 수당
CREATE TABLE EMPLOYEE
( EMPLOYEEID    NUMBER              --사원번호      -PK
, NAME          VARCHAR2(30)        --사원 이름
, SSN           VARCHAR2(30)        --주민 번호     --암호화 기능 적용(타입 CHECK~!!!)
, BIRTHDAY      DATE                --생년월일
, LUNAR         NUMBER(1) DEFAULT 0 --양음력           --양력 0 , 음력 1
, TELEPHONE     VARCHAR2(40)        --전화번호
, DEPARTMENTID  NUMBER              --부서 아이디 --FK
, POSITIONID    NUMBER              --직위 아이디 --FK
, REGIONID      NUMBER              --지역 아이디 --FK
, BASICPAY      NUMBER              --기본급
, EXTRAPAY      NUMBER              --수당
, CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY(EMPLOYEEID)
, CONSTRAINT EMPLOYEE_DEPARTMENTID_FK FOREIGN KEY(DEPARTMENTID)
                                REFERENCES DEPARTMENT(DEPARTMENTID)
, CONSTRAINT EMPLOYEE_POSITIONID_FK FOREIGN KEY(POSITIONID)
            REFERENCES POSITION(POSITIONID)
, CONSTRAINT EMPLOYEE_REGIONID_FK FOREIGN KEY(REGIONID)
            REFERENCES REGION(REGIONID)
, CONSTRAINT EMPLOYEE_LUNAR_CK CHECK(LUNAR=0 OR LUNAR =1)
);

-- 단 주민번호는 암호화 되어야 하므로 입력할때 암호화를 거쳐 입력되도록한다.

--데이터 입력 직원 데이터 입력

INSERT INTO EMPLOYEE(EMPLOYEEID, NAME, SSN, BIRTHDAY, LUNAR, TELEPHONE
                       , DEPARTMENTID, POSITIONID, REGIONID, BASICPAY, EXTRAPAY)
VALUES(EMPLOYEESEQ.NEXTVAL, '엄소연', CRYPTPACK.ENCRYPT('9411242234567', '9411242234567')
        , TO_DATE('1994-11-24','YYYY-MM-DD'), 0, '010-7193-4562', 1,1,1, 1500000, 1500000);



로그인/로그아웃기능

--■■■ 로그인 / 로그아웃 기능 적용 과정 추가 ■■■-----

--현재 우리가 구성한 테이블 구조는 ID와 PW 컬럼 정보를 담고있는 테이블이
-- 별도로 존재하지 않는 상황이다.
-- 이와 관련하여 EMPLOYEEID(사원아이디) 와 SSN(주민번호) 뒷자리 7자리의 숫자를
-- 활용하여 기능을 구현할 수 있도록 처리한다.

--※ 기존 테이블 구조 변경
--①
-- EMPLOYEE (직원 데이터 테이블)의 SSN(주민번호) 컬럼을 분리한다.
-- SSN ------------------->SSN1, SSN2
-- SSN1 ---------------->주민번호 앞 6자리
-- SSN2 ----------------->주민번호 뒷 7자리 -> 암호화 적용

--②
-- EMPLOYEE(직원 테이블)의 GRADE(등급) 컬럼을 추가한다.
-- GRADE → 0:관리자, 1:일반사원


--○ 컬럼 분할 SSN → SSN1, SSN2

-- 컬럼 추가
ALTER TABLE EMPLOYEE
ADD(SSN1 CHAR(6), SSN2 VARCHAR2(50));
--==>> Table EMPLOYEE이(가) 변경되었습니다.


SELECT *
FROM EMPLOYEE;
--1	엄소연	?`??$?	1994-11-24	0	010-7193-4562	1	1	1	1500000	150000  NULL  NULL

-- SSN → 암호화('9610041234567', '9610041234567')

-- SSN1 → SUBSTR(복호화(대상, '9610041234567'), 1, 6) → '961004'
-- SSN2 → 암호화(SUBSTR(복호화(대상, '9610041234567'), 7, 7)
--              , SUBSTR(복호화(대상, '9610041234567'), 7, 7)) → '1234567'

UPDATE EMPLOYEE
SET  SSN1 = SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567') ,1,6)
    , SSN2 = CRYPTPACK.ENCRYPT(SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567'),7,7),SUBSTR(CRYPTPACK.DECRYPT(SSN,'9411242234567'),7,7))
WHERE EMPLOYEEID = 1;


--○데이터 입력 (직원 데이터 입력)
INSERT INTO EMPLOYEE(EMPLOYEEID, NAME, SSN1, SSN2, BIRTHDAY, LUNAR, TELEPHONE
                       , DEPARTMENTID, POSITIONID, REGIONID, BASICPAY, EXTRAPAY)
VALUES(EMPLOYEESEQ.NEXTVAL, '정미경', '981009',CRYPTPACK.ENCRYPT('2345678', '2345678')
        , TO_DATE('1998-10-09','YYYY-MM-DD'), 0, '010-3098-3091', 1,1,8, 1500000, 1500000);

--①-1 기존 주민번호 컬럼(SSN) 제거
ALTER TABLE EMPLOYEE
DROP COLUMN SSN;
--==>> Table EMPLOYEE이(가) 변경되었습니다.

--②-1 GRADE 컬럼 추가 → 기본값을 1(일반사원)로 구성
ALTER TABLE EMPLOYEE
ADD GRADE NUMBER(1) DEFAULT 1;
--==>>Table EMPLOYEE이(가) 변경되었습니다.

SELECT *
FROM EMPLOYEE;



--②-2 엄소연 사원을 관리자로 임명(설정)
UPDATE EMPLOYEE
SET GRADE=0
WHERE EMPLOYEEID = 1;
--==>>1행 이 업데이트 되었습니다.

SELECT *
FROM EMPLOYEE;
--1	엄소연	1994-11-24	0	010-7193-4562	1	1	1	1500000	1500000	941124	Y{?7?	0

로그인 방식 구현

--일반사원 로그인 쿼리문 구성(아이디, 패스워드)
--아이디 : 사원번호
-- 패스워드 : 주민번호 뒷자리
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 아이디
   AND SSN2 = 패스워드;
      
SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 2
   AND SSN2 = CRYPTPACK.ENCRYPT('2345678','2345678');     
--==>정미경
-->로그인 성공~!!!!

SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 2
   AND SSN2 = CRYPTPACK.ENCRYPT('1234566','1234566');     
--==>
-->로그인 실패~!!!!


SELECT NAME
FROM EMPLOYEE
WHERE EMPLOYEEID = 1
   AND SSN2 = CRYPTPACK.ENCRYPT('2234567','2234567');     
--==>엄소연
-->로그인 성공~!!!!

--일반 사원 로그인 쿼리문 한 줄 구성
SELECT NAME FROM EMPLOYEE WHERE EMPLOYEEID = 'ID문자열' AND SSN2 = CRYPTPACK.ENCRYPT('PW문자열','PW문자열')
;



뷰 생성

CREATE OR REPLACE VIEW EMPLOYEEVIEW
AS
SELECT E.EMPLOYEEID AS EMPLOYEEID
     , E.NAME AS NAME
     , E.SSN1 AS SSN
     , TO_CHAR(E.BIRTHDAY, 'YYYY-MM-DD') AS BIRTHDAY
     , E.LUNAR AS LUNAR
     , DECODE(E.LUNAR, 0, '양력', 1, '음력') AS LUNARNAME
     , E.TELEPHONE AS TELEPHONE
     , E.DEPARTMENTID AS DEPARTMENTID
     , (SELECT DEPARTMENTNAME
        FROM DEPARTMENT
        WHERE DEPARTMENTID = E.DEPARTMENTID) AS DEPARTMENTNAME
     , E.POSITIONID AS POSITIONID
     , (SELECT POSITIONNAME
        FROM POSITION
        WHERE POSITIONID = E.POSITIONID) AS POSITIONNAME
     , E.REGIONID AS REGIONID
     , (SELECT REGIONNAME
        FROM REGION
        WHERE REGIONID = E.REGIONID) AS REGIONNAME
     , E.BASICPAY AS BASICPAY
     , E.EXTRAPAY AS EXTRAPAY
     , NVL(E.BASICPAY, 0) + NVL(E.EXTRAPAY, 0) AS PAY
     , E.GRADE AS GRADE
FROM EMPLOYEE E;

SELECT EMPLOYEEID, NAME, SSN, BIRTHDAY, LUNAR, LUNARNAME, TELEPHONE, DEPARTMENTID, DEPARTMENTNAME, POSITIONID, POSITIONNAME, REGIONID, REGIONNAME, BASICPAY, EXTRAPAY, PAY, GRADE FROM EMPLOYEEVIEW
;

-- 1	엄소연	941124	1994-11-24	0	양력	010-7193-4562	1	개발부	1	사원	1	서울	1500000	1500000	3000000	0
profile
유동현
이전 포스트

Spring FrameWork 실습-3

다음 포스트

Spring Framework 3-2(컨트롤러,모델,뷰)

0개의 댓글