위 소스코드 중 WebContent/WEB-INF/sql/database.sql 파일을 참조한다.
다음과 같이 데이터베이스 및 사용자를 생성하고 권한을 부여해준다. (root 권한으로 실행한다.)
-- 은노기 shoppingmall 프로젝트
create database jsptest;
create user 'jspid'@'localhost' identified by 'jsppass';
create user 'jspid'@'%' identified by 'jsppass';
grant all privileges on jsptest.* to 'jspid'@'localhost';
grant all privileges on jsptest.* to 'jspid'@'%';
commit;
다음과 같이 테이블과 사용자 데이터를 추가한다.
create table member(
id varchar(50) not null primary key,
passwd varchar(16) not null,
name varchar(10) not null,
reg_date datetime not null
);
alter table member
add (address varchar(100) not null,
tel varchar(20) not null);
desc member;
insert into member(id, passwd, name, reg_date, address, tel)
values('kingdora@dragon.com','1234','김개동', now(), '서울시', '010-1111-1111');
insert into member(id, passwd, name, reg_date, address, tel)
values('hongkd@aaa.com','1111','홍길동', now(), '경기도', '010-2222-2222');
select * from member;
alter table member modify passwd varchar(60) not null;
create table board(
num int not null primary key auto_increment,
writer varchar(50) not null,
subject varchar(50) not null,
content text not null,
passwd varchar(60) not null,
reg_date datetime not null,
ip varchar(30) not null,
readcount int default 0,
ref int not null,
re_step smallint not null,
re_level smallint not null
);
desc board;
--쇼핑몰
create table manager(
managerId varchar(50) not null primary key,
managerPasswd varchar(60) not null
);
insert into manager(managerId,managerPasswd)
values('bookmaster@shop.com','123456');
insert into manager(managerId,managerPasswd)
values('ksseo63@naver.com','ekffksxm0');
create table book(
book_id int not null primary key auto_increment,
book_kind varchar(3) not null,
book_title varchar(100) not null,
book_price int not null,
book_count smallint not null,
author varchar(40) not null,
publishing_com varchar(30) not null,
publishing_date varchar(15) not null,
book_image varchar(16) default 'nothing.jpg',
book_content text not null,
discount_rate tinyint default 10,
reg_date datetime not null
);
create table qna(
qna_id int not null primary key auto_increment,
book_id int not null,
book_title varchar(100) not null,
qna_writer varchar(50) not null,
qna_content text not null,
group_id int not null,
qora tinyint not null,
reply tinyint default 0,
reg_date datetime not null
);
create table bank(
account varchar(30) not null,
bank varchar(10) not null,
name varchar(10) not null
);
insert into bank(account, bank, name)
values('11111-111-11111','내일은행','오내일');
create table cart(
cart_id int not null primary key auto_increment,
buyer varchar(50) not null,
book_id int not null,
book_title varchar(100) not null,
buy_price int not null,
buy_count tinyint not null,
book_image varchar(16) default 'nothing.jpg'
);
create table buy(
buy_id bigint not null,
buyer varchar(50) not null,
book_id varchar(12) not null,
book_title varchar(100) not null,
buy_price int not null,
buy_count tinyint not null,
book_image varchar(16) default 'nothing.jpg',
buy_date datetime not null,
account varchar(50) not null,
deliveryName varchar(10) not null,
deliveryTel varchar(20) not null,
deliveryAddress varchar(100) not null,
sanction varchar(10) default '상품준비중'
);
데이터베이스 연동을 위한 설정은 WebContent/META-INF/context.xml을 참조한다.
또한 MySQL JDBC Connector 파일도 자신이 사용하는 MySQL Server 버전에 맞추어 변경해 주어야 한다. 이것은 studyjsp와 shoppingmall 프로젝트의 WebContent/WEB-INF/lib 폴더 밑에 있는 JDBC Connecctor 라이브러리에 대하여 맞추어 주어야 한다. 나는 v8.0.13을 사용하므로 mysql-connector-java-8.0.13.jar로 변경해 주었다. (기존에는 5.x.x 버전을 사용)
사용자에 대한 암호를 암호화하기 위해서는 9장에 있는 cryptProcess.jsp를 구동해 주어야 한다. (http://localhost:8080/shoppingmall/ch09/cryptProcess.jsp)
이는 studyjsp 프로젝트 폴더에 있는 소스코드를 shoppingmall 프로젝트 폴더로 복사하여 사용하였다.
- 사전 전제조건 : Oracle 데이터베이스 19c가 설치되어 있다. SQL developer에서 dba로 접속하여 다음과 SQL 작업을 한다.
- 테이블 스페이스 생성
+ 오라클 데이터베이스가 D:\oraclexe에 설치되어 oradata\ORCL 밑에 테이블 스페이스를 위한 dbf 파일을 생성한다.
create tablespace quickts
datafile 'D:\oraclexe\oradata\ORCL\quickts.dbf' size 100M
autoextend on next 5M;
- 사용자 생성 및 권한 부여
+ username : quick, password : quick
+ quick에게 일반 개발자 권한을 부여한다.
alter session set "_ORACLE_SCRIPT"=true;
create user quick identified by quick
default tablespace quickts temporary tablespace temp;
grant connect, resource to quick; -- 접속, 자원 사용에 대한 권한
alter user quick quota unlimited on quickts; -- 테이블스페이스 사용 권한
commit;
+ quick 계정을 사용하여 sql developer에 접속 (SID = orcl)
2. 데이터베이스 테이블 생성
여기서는 Day 01 Class 6 비지니스 컴포넌트 실습 1을 실습하기 위하여 board 테이블을 생성한다. sql developer를 사용하여 사용자 quick로 접속하여 다음 SQL을 수행한다. seq 컬럼은 12c 이후에 추가된 기능을 사용하여 MySQL과 같이 auto_increment 기능을 적용하였다.
create table board (
seq INTEGER GENERATED AS IDENTITY,
title VARCHAR(100) not null,
writer varchar2(50) not null,
content varchar2(4000) not null,
regDate date default sysdate,
cnt integer default 0
);
alter table board add constraint pk_board primary key (seq);
3. JDBC 라이브러리를 사용하여 오라클 데이터베이스를 사용하기 위하여 다음과 같이 Connection을 가져온다. 이때 필요한 driver name, URL, username, password를 설정한다.
- JDBC 드라이브 이름 : oracle.jdbc.driver.OracleDriver
- 데이터베이스 URL : jdbc:oracle:thin:@localhost:1521:orcl
/* Drop Triggers */
DROP TRIGGER TRI_Dept_dno;
DROP TRIGGER TRI_Graduate_ssn;
DROP TRIGGER TRI_Professor_ssn;
DROP TRIGGER TRI_Project_pid;
/* Drop Tables */
DROP TABLE work_prog CASCADE CONSTRAINTS;
DROP TABLE Graduate CASCADE CONSTRAINTS;
DROP TABLE work_dept CASCADE CONSTRAINTS;
DROP TABLE Dept CASCADE CONSTRAINTS;
DROP TABLE work_in CASCADE CONSTRAINTS;
DROP TABLE Project CASCADE CONSTRAINTS;
DROP TABLE Professor CASCADE CONSTRAINTS;
/* Drop Sequences */
DROP SEQUENCE SEQ_Dept_dno;
DROP SEQUENCE SEQ_Graduate_ssn;
DROP SEQUENCE SEQ_Professor_ssn;
DROP SEQUENCE SEQ_Project_pid;
/* Create Sequences */
CREATE SEQUENCE SEQ_Dept_dno INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Graduate_ssn INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Professor_ssn INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Project_pid INCREMENT BY 1 START WITH 1;
/* Create Tables */
CREATE TABLE Dept
(
dno number NOT NULL,
dname varchar2(30) NOT NULL,
office varchar2(50) NOT NULL,
ssn number NOT NULL,
PRIMARY KEY (dno)
);
CREATE TABLE Graduate
(
ssn number NOT NULL,
name varchar2(15) NOT NULL,
age number NOT NULL,
deg_prog varchar2(10) NOT NULL,
dno number NOT NULL,
graduatessn number NOT NULL,
PRIMARY KEY (ssn)
);
CREATE TABLE Professor
(
ssn number NOT NULL,
name varchar2(15) NOT NULL,
rank varchar2(15) NOT NULL,
speciality varchar2(30) NOT NULL,
age number(3,0) NOT NULL,
PRIMARY KEY (ssn)
);
CREATE TABLE Project
(
pid number NOT NULL,
sponsor varchar2(30) NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL,
budget number NOT NULL,
ssn number NOT NULL,
PRIMARY KEY (pid)
);
CREATE TABLE work_dept
(
ssn number NOT NULL,
dno number NOT NULL,
pct_time number(3,0) NOT NULL
);
CREATE TABLE work_in
(
ssn number NOT NULL,
pid number NOT NULL
);
CREATE TABLE work_prog
(
pid number NOT NULL,
ssn number NOT NULL
);
/* Create Foreign Keys */
ALTER TABLE Graduate
ADD FOREIGN KEY (dno)
REFERENCES Dept (dno)
;
ALTER TABLE work_dept
ADD FOREIGN KEY (dno)
REFERENCES Dept (dno)
;
ALTER TABLE Graduate
ADD FOREIGN KEY (graduatessn)
REFERENCES Graduate (ssn)
;
ALTER TABLE work_prog
ADD FOREIGN KEY (ssn)
REFERENCES Graduate (ssn)
;
ALTER TABLE Dept
ADD FOREIGN KEY (ssn)
REFERENCES Professor (ssn)
;
ALTER TABLE Project
ADD FOREIGN KEY (ssn)
REFERENCES Professor (ssn)
;
ALTER TABLE work_dept
ADD FOREIGN KEY (ssn)
REFERENCES Professor (ssn)
;
ALTER TABLE work_in
ADD FOREIGN KEY (ssn)
REFERENCES Professor (ssn)
;
ALTER TABLE work_in
ADD FOREIGN KEY (pid)
REFERENCES Project (pid)
;
ALTER TABLE work_prog
ADD FOREIGN KEY (pid)
REFERENCES Project (pid)
;
/* Create Triggers */
CREATE OR REPLACE TRIGGER TRI_Dept_dno BEFORE INSERT ON Dept
FOR EACH ROW
BEGIN
SELECT SEQ_Dept_dno.nextval
INTO :new.dno
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_Graduate_ssn BEFORE INSERT ON Graduate
FOR EACH ROW
BEGIN
SELECT SEQ_Graduate_ssn.nextval
INTO :new.ssn
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_Professor_ssn BEFORE INSERT ON Professor
FOR EACH ROW
BEGIN
SELECT SEQ_Professor_ssn.nextval
INTO :new.ssn
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_Project_pid BEFORE INSERT ON Project
FOR EACH ROW
BEGIN
SELECT SEQ_Project_pid.nextval
INTO :new.pid
FROM dual;
END;
/
이를 물리적 SQL 파일로 만들기 위해서는 이클립스에서 오른쪽 마우스를 클릭하여 아래 그림과 같이 "내보내기->DDL" 메뉴를 선택하면 데이터베이스 연동을 위한 sql 파일을 생성할 수 있다.
이를 이용하여 데이터베이스 테이블을 생성할 수 있다.
/* Drop Triggers */
DROP TRIGGER TRI_author_id;
DROP TRIGGER TRI_comment_id;
DROP TRIGGER TRI_tauthor_id;
DROP TRIGGER TRI_tcomment_id;
DROP TRIGGER TRI_topic_id;
/* Drop Tables */
DROP TABLE dormant CASCADE CONSTRAINTS;
DROP TABLE tcomment CASCADE CONSTRAINTS;
DROP TABLE twrite CASCADE CONSTRAINTS;
DROP TABLE tauthor CASCADE CONSTRAINTS;
DROP TABLE topic CASCADE CONSTRAINTS;
/* Drop Sequences */
DROP SEQUENCE SEQ_author_id;
DROP SEQUENCE SEQ_comment_id;
DROP SEQUENCE SEQ_tauthor_id;
DROP SEQUENCE SEQ_tcomment_id;
DROP SEQUENCE SEQ_topic_id;
/* Create Sequences */
CREATE SEQUENCE SEQ_tauthor_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_tcomment_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_topic_id INCREMENT BY 1 START WITH 1;
/* Create Tables */
CREATE TABLE dormant
(
author_id number NOT NULL,
created date NOT NULL,
PRIMARY KEY (author_id)
);
CREATE TABLE tauthor
(
id number NOT NULL,
name varchar2(15) NOT NULL,
introduction varchar2(100) NOT NULL,
created date UNIQUE,
PRIMARY KEY (id)
);
CREATE TABLE tcomment
(
id number NOT NULL,
description varchar2(4000) NOT NULL,
created date NOT NULL,
topic_id number NOT NULL,
author_id number NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE topic
(
id number NOT NULL,
title varchar2(30) NOT NULL,
description varchar2(4000) NOT NULL,
created date NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE twrite
(
author_id number NOT NULL,
topic_id number NOT NULL,
created date NOT NULL,
PRIMARY KEY (author_id, topic_id)
);
/* Create Foreign Keys */
ALTER TABLE dormant
ADD FOREIGN KEY (author_id)
REFERENCES tauthor (id)
;
ALTER TABLE tcomment
ADD FOREIGN KEY (author_id)
REFERENCES tauthor (id)
;
ALTER TABLE twrite
ADD FOREIGN KEY (author_id)
REFERENCES tauthor (id)
;
ALTER TABLE tcomment
ADD FOREIGN KEY (topic_id)
REFERENCES topic (id)
;
ALTER TABLE twrite
ADD FOREIGN KEY (topic_id)
REFERENCES topic (id)
;
/* Create Triggers */
CREATE OR REPLACE TRIGGER TRI_author_id BEFORE INSERT ON author
FOR EACH ROW
BEGIN
SELECT SEQ_author_id.nextval
INTO :new.id
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_comment_id BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
SELECT SEQ_comment_id.nextval
INTO :new.id
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_tauthor_id BEFORE INSERT ON tauthor
FOR EACH ROW
BEGIN
SELECT SEQ_tauthor_id.nextval
INTO :new.id
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_tcomment_id BEFORE INSERT ON tcomment
FOR EACH ROW
BEGIN
SELECT SEQ_tcomment_id.nextval
INTO :new.id
FROM dual;
END;
/
CREATE OR REPLACE TRIGGER TRI_topic_id BEFORE INSERT ON topic
FOR EACH ROW
BEGIN
SELECT SEQ_topic_id.nextval
INTO :new.id
FROM dual;
END;
/
주님이 나에게 맡기신 달란트(헬라어로는 Talanton)를 남기고자 합니다. 주님에게 칭찬받는 종이 되기 위하여 주님이 주신 달란트를 땅에 묻어 두지 아니하고, 2배, 5배, 아니 10배를 남기고자 합니다. 나는 부족하지만, 주님께서 함께 하신다면 가능합니다. 하루를 기도로
