티스토리 뷰
테이블 생성
병원 업무 관리 프로젝트 테이블 생성
제약 조건에서 걸리는 부분이 있길래 테이블을 한번 뒤집어 엎었는데
알고보니 숫자로 이루어진 ID 들에 대한 오타가 발생하여 문제가 발생한 것이였음
그래서
1. 테이블 생성
2. 제약 조건 설정
3. 값을 하나씩 입력 (안들어간다면 맞지 않는 정보가 있었을 경우 발생하는 문제임)
CREATE USER hospital
IDENTIFIED BY hospital
Default Tablespace users
Temporary Tablespace temp
profile default;
GRANT connect, RESOURCE TO hospital;
GRANT create view, create SYNONYM to hospital;
ALTER USER hospital ACCOUNT unlock;
ALTER USER hospital DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
/* connect to user madang */
CONNECT hospital/hospital
CREATE TABLE Doctors
(
doc_id NUMBER(10) NOT NULL,
mager_treat VARCHAR2(25) NOT NULL,
doc_name VARCHAR2(20) NOT NULL,
doc_gen CHAR(1) NOT NULL,
doc_phone VARCHAR2(15) NOT NULL,
doc_email VARCHAR2(50) UNIQUE,
doc_posistion VARCHAR2(20) NOT NULL
);
ALTER TABLE Doctors ADD CONSTRAINT doc_id_pk PRIMARY KEY (doc_id);
INSERT INTO Doctors VALUES (980312, '소아과', '이태정', 'M', '010-333-1340', 'ltj@hambh.com', '과장');
INSERT INTO Doctors VALUES (000601, '내과', '안성기', 'M', '011-222-0987', 'ask@hambh.com', '과장');
INSERT INTO Doctors VALUES (001208, '외과', '김민종', 'M', '010-333-8743', 'kmj@hambh.com', '과장');
INSERT INTO Doctors VALUES (020403, '피부과', '이태서', 'M', '019-777-3764', 'lts@hambh.com', '과장');
INSERT INTO Doctors VALUES (050900, '소아과', '김연아', 'F', '010-555-3746', 'kya@hambh.com', '전문의');
INSERT INTO Doctors VALUES (050101, '내과', '차태현', 'M', '011-222-7643', 'cth@hambh.com', '전문의');
INSERT INTO Doctors VALUES (062019, '소아과', '전지현', 'F', '010-999-1265', 'jjh@hambh.com', '전문의');
INSERT INTO Doctors VALUES (070576, '피부과', '홍길동', 'M', '016-333-7263', 'hgd@hambh.com', '전문의');
INSERT INTO Doctors VALUES (080543, '방사선과', '유재석', 'M', '010-222-1263', 'yjs@hambh.com', '과장');
INSERT INTO Doctors VALUES (091001, '외과', '김병만', 'M', '010-555-3542', 'kbm@hambh.com', '전문의');
SELECT *
FROM Doctors;
commit;
CREATE TABLE Nurses
(
nur_ID NUMBER(10) NOT NULL,
major_job VARCHAR2(25) NOT NULL,
nur_name VARCHAR2(20) NOT NULL,
nur_gen CHAR(1) NOT NULL,
nur_phone VARCHAR2(15) NOT NULL,
nur_email VARCHAR2(50) UNIQUE,
nur_posistion VARCHAR2(20) NOT NULL
);
ALTER TABLE Nurses ADD CONSTRAINT nur_id_pk PRIMARY KEY (nur_id);
INSERT INTO Nurses VALUES (050302, '소아과', '김은영', 'F', '010-555-8751', 'key@hambh.com', '수간호사');
INSERT INTO Nurses VALUES (050021, '내과', '윤성애', 'F', '016-333-8745', 'ysa@hambh.com', '수간호사');
INSERT INTO Nurses VALUES (040089, '피부과', '신지원', 'M', '010-666-7646', 'sjw@hambh.com', '주임');
INSERT INTO Nurses VALUES (070605, '방사선과', '유정화', 'F', '010-333-4588', 'yjh@hambh.com', '주임');
INSERT INTO Nurses VALUES (070804, '내과', '라하나', 'F', '010-222-1340', 'nhn@hambh.com', '주임');
INSERT INTO Nurses VALUES (071018, '소아과', '김화경', 'F', '019-888-4116', 'khk@hambh.com', '주임');
INSERT INTO Nurses VALUES (100356, '소아과', '이선용', 'M', '010-777-1234', 'lsy@hambh.com', '간호사');
INSERT INTO Nurses VALUES (104145, '외과', '김현', 'M', '010-999-8520', 'kh@hambh.com', '간호사');
INSERT INTO Nurses VALUES (120309, '피부과', '박성완', 'M', '010-777-4996', 'psw@hambh.com', '간호사');
INSERT INTO Nurses VALUES (130211, '외과', '이서연', 'F', '010-222-3214', 'lsy2@hambh.com', '간호사');
SELECT *
FROM Nurses;
commit;
CREATE TABLE Patients
(
pat_id NUMBER(10) NOT NULL,
nur_id NUMBER(10) NOT NULL,
doc_id NUMBER(10) NOT NULL,
pat_name VARCHAR2(20) NOT NULL,
pat_gen CHAR(1) NOT NULL,
pat_jumin VARCHAR2(14) NOT NULL,
pat_addr VARCHAR2(100) NOT NULL,
pat_phone VARCHAR2(15) NOT NULL,
pat_email VARCHAR2(50) UNIQUE,
pat_job VARCHAR2(20) NOT NULL
);
ALTER TABLE Patients ADD CONSTRAINT pat_id_pk PRIMARY KEY (pat_id);
ALTER TABLE Patients ADD (CONSTRAINT R_2 FOREIGN KEY (doc_id) REFERENCES Doctors (doc_id));
ALTER TABLE Patients ADD (CONSTRAINT R_3 FOREIGN KEY (nur_id) REFERENCES Nurses (nur_id));
INSERT INTO PATIENTS VALUES (2345, 050302, 980312, '안상건', 'M', '232345', '서울', '010-555-7845', 'ask@ab.com', '회사원');
INSERT INTO PATIENTS VALUES (3545, 040089, 020403, '김성룡', 'M', '543545', '서울', '010-555-7845', 'ksr@bb.com', '자영업');
INSERT INTO PATIENTS VALUES (3424, 070605, 080543, '이종진', 'M', '433424', '부산', '010-555-7845', 'ljj@ab.com', '회사원');
INSERT INTO PATIENTS VALUES (7675, 100356, 050900, '최광석', 'M', '677675', '당진', '010-555-7845', 'cks@cc.com', '회사원');
INSERT INTO PATIENTS VALUES (4533, 070804, 000601, '정한경', 'M', '744533', '강릉', '010-555-7845', 'jhk@ab.com', '교수');
INSERT INTO PATIENTS VALUES (5546, 120309, 070576, '유원현', 'M', '765546', '대구', '010-555-7845', 'ywh@cc.com', '자영업');
INSERT INTO PATIENTS VALUES (4543, 070804, 050101, '최재정', 'M', '454543', '부산', '010-555-7845', 'cjj@bb.com', '회사원');
INSERT INTO PATIENTS VALUES (9768, 130211, 091001, '이진희', 'F', '119768', '서울', '010-555-7845', 'ljh@ab.com', '교수');
INSERT INTO PATIENTS VALUES (4234, 130211, 091001, '오나미', 'F', '234234', '속초', '010-555-7845', 'onm@cc.com', '학생');
INSERT INTO PATIENTS VALUES (7643, 071018, 062019, '송성묵', 'M', '987643', '서울', '010-555-7845', 'ssm@bb.com', '학성');
SELECT *
FROM PATIENTS;
commit;
CREATE TABLE Treatments
(
treat_id NUMBER(15) NOT NULL,
pat_id NUMBER(10) NOT NULL,
doc_id NUMBER(10) NOT NULL,
treat_contents VARCHAR2(1000) NOT NULL,
treat_data DATE NOT NULL
);
ALTER TABLE Treatments ADD CONSTRAINT treat_pat_doc_id_pk PRIMARY KEY (treat_id, pat_id, doc_id);
ALTER TABLE Treatments ADD (CONSTRAINT R_5 FOREIGN KEY (pat_id) REFERENCES Patients (pat_id));
ALTER TABLE Treatments ADD (CONSTRAINT R_6 FOREIGN KEY (doc_id) REFERENCES Doctors (doc_id));
INSERT INTO TREATMENTS VALUES(130516023, 2345, 980312, '감기, 몸살', '2013-05-16');
INSERT INTO TREATMENTS VALUES(130618100, 3545, 020403, '피부 트러블 치료', '2013-06-28');
INSERT INTO TREATMENTS VALUES(131205056, 3424, 080543, '목 디스크로 MRI 촬영', '2013-12-05');
INSERT INTO TREATMENTS VALUES(131218024, 7675, 050900, '중이염', '2013-12-18');
INSERT INTO TREATMENTS VALUES(131224012, 4533, 000601, '장염', '2013-12-24');
INSERT INTO TREATMENTS VALUES(140103001, 5546, 070576, '여드름 치료', '2014-01-03');
INSERT INTO TREATMENTS VALUES(140109026, 4543, 050101, '위염', '2014-01-09');
INSERT INTO TREATMENTS VALUES(140226102, 9768, 091001, '화상치료', '2014-02-26');
INSERT INTO TREATMENTS VALUES(140303003, 4234, 091001, '교통사고 외상치료', '2014-03-03');
INSERT INTO TREATMENTS VALUES(140308087, 7643, 062019, '장염', '2014-03-08');
SELECT *
FROM Treatments;
commit;
CREATE TABLE Charts
(
chart_id VARCHAR2(20) NOT NULL,
treat_id NUMBER(15) NOT NULL,
doc_id NUMBER(10) NOT NULL,
pat_id NUMBER(10) NOT NULL,
nur_id NUMBER(10) NOT NULL,
chart_contents VARCHAR2(1000) NOT NULL
);
ALTER TABLE charts ADD CONSTRAINT chart_treat_doc_pat_id_pk PRIMARY KEY (chart_id, treat_id, doc_id, pat_id);
ALTER TABLE charts ADD (CONSTRAINT R_4 FOREIGN KEY (nur_id) REFERENCES Nurses (nur_id));
ALTER TABLE charts ADD (CONSTRAINT R_7 FOREIGN KEY (treat_id,pat_id,doc_id) REFERENCES Treatments (treat_id,pat_id,doc_id));
INSERT INTO Charts VALUES('p_130516023', 130516023, 980312, 2345, 050302, '감기주사 및 약 처방');
INSERT INTO Charts VALUES('d_130628100', 130618100, 020403, 3545, 040089, '피부 감염 방지 주사');
INSERT INTO Charts VALUES('r_131205056', 131205056, 080543, 3424, 070605, '주사 처방');
INSERT INTO Charts VALUES('p_131218024', 131218024, 050900, 7675, 100356, '귓속청소 및 약 처방');
INSERT INTO Charts VALUES('i_131224012', 131224012, 000601, 4533, 070804, '장염 입원치료');
INSERT INTO Charts VALUES('d_140103001', 140103001, 070576, 5546, 120309, '여드름 치료약 처방');
INSERT INTO Charts VALUES('i_140109026', 140109026, 050101, 4543, 070804, '위내시경');
INSERT INTO Charts VALUES('s_140226102', 140226102, 091001, 9768, 130211, '화상 크림약 처방');
INSERT INTO Charts VALUES('s_140303003', 140303003, 091001, 4234, 130211, '입원치료');
INSERT INTO Charts VALUES('p_140308087', 140308087, 062019, 7643, 071018, '장염 입원치료');
SELECT *
FROM Charts;
commit;
JDBC 연결 및 실행
아니 기껏 다 만들어놨더니 쓰는게 Doctor 하나 밖에 없다는 사실에 때려칠까 진지하게 생각함
내다버린 시간...
DoctorsDB
package Hospital_homework;
public class Doctors {
private int doc_id;
private String mager_treat;
private String doc_name;
private String doc_gen;
private String doc_phone;
private String doc_email;
private String doc_posistion;
public Doctors() {
// TODO Auto-generated constructor stub
}
public Doctors(int doc_id, String mager_treat, String doc_name,String doc_gen, String doc_phone,String doc_email, String doc_posistion) {
// TODO Auto-generated constructor stub
this.doc_id = doc_id;
this.mager_treat = mager_treat;
this.doc_name = doc_name;
this.doc_gen = doc_gen;
this.doc_phone = doc_phone;
this.doc_email = doc_email;
this.doc_posistion = doc_posistion;
}
public int getDoc_id() {
return doc_id;
}
public void setDoc_id(int doc_id) {
this.doc_id = doc_id;
}
public String getMager_treat() {
return mager_treat;
}
public void setMager_treat(String mager_treat) {
this.mager_treat = mager_treat;
}
public String getDoc_name() {
return doc_name;
}
public void setDoc_name(String doc_name) {
this.doc_name = doc_name;
}
public String getDoc_gen() {
return doc_gen;
}
public void setDoc_gen(String doc_gen) {
this.doc_gen = doc_gen;
}
public String getDoc_phone() {
return doc_phone;
}
public void setDoc_phone(String doc_phone) {
this.doc_phone = doc_phone;
}
public String getDoc_email() {
return doc_email;
}
public void setDoc_email(String doc_email) {
this.doc_email = doc_email;
}
public String getDoc_posistion() {
return doc_posistion;
}
public void setDoc_posistion(String doc_posistion) {
this.doc_posistion = doc_posistion;
}
}
DoctorsDB
package Hospital_homework;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class DoctorEx {
public static void main(String[] args) {
// TODO Auto-generated method stub
DoctorsDB ddb = new DoctorsDB();
Scanner scan = new Scanner(System.in);
Doctors docl;
System.out.println( "-- 병원 관리 --");
System.out.println("1. 전체 조회");
System.out.println("2. 의사 추가");
System.out.println("3. 의사 정보 수정");
System.out.println("4. 의사 정보 삭제");
System.out.println("5. 종료");
boolean check = true;
while (check) {
System.out.println("원하는 번호 입력 (1~5) : " );
int sel = scan.nextInt();
switch (sel) {
case 1:
ddb.sqlRun();
break;
case 2:
System.out.println("추가할 의사 정보를 입력하세요");
System.out.println("의사번호 :");
int no = scan.nextInt();
System.out.println("전공 :");
String treat = scan.next();
System.out.println("이름 :");
String name = scan.next();
System.out.println("성별 :");
String gen = scan.next();
System.out.println("전화번호 :");
String phone = scan.next();
System.out.println("이메일 :");
String email = scan.next();
System.out.println("포지션 :");
String posistion = scan.next();
docl = new Doctors(no, treat, name, gen, phone, email, posistion);
check = ddb.DocInsert(docl);
if (check) {
System.out.println("insert success");
}
else {
System.out.println("insert failure");
}
break;
case 3:
System.out.println("수정할 의사 정보를 입력하세요");
System.out.println("의사번호 :");
int no1 = scan.nextInt();
docl = new Doctors(no1);
System.out.println("의사번호 :");
no = scan.nextInt();
System.out.println("전공 :");
treat = scan.next();
System.out.println("이름 :");
name = scan.next();
System.out.println("성별 :");
gen = scan.next();
System.out.println("전화번호 :");
phone = scan.next();
System.out.println("이메일 :");
email = scan.next();
System.out.println("포지션 :");
posistion = scan.next();
docl = new Doctors(no, treat, name, gen, phone, email, posistion);
check = ddb.DocUpdate(docl);
if (check) {
System.out.println("update success");
} else {
System.out.println("update failure");
}
break;
case 4:
System.out.println("삭제할 의사번호를 입력하세요");
System.out.println("의사번호 :");
no1 = scan.nextInt();
check = ddb.DocDelete(no1);
if (check) {
System.out.println("insert success");
}
else {
System.out.println("insert failure");
}
System.out.println("테스트 4");
break;
case 5:
System.out.println("종료합니다.");
ddb.close();
check = false;
break;
}
}
}
}
Doctor EX
package Hospital_homework;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class DoctorEx {
public static void main(String[] args) {
// TODO Auto-generated method stub
DoctorsDB ddb = new DoctorsDB();
Scanner scan = new Scanner(System.in);
Doctors docl;
System.out.println( "-- 병원 관리 --");
System.out.println("1. 전체 조회");
System.out.println("2. 의사 추가");
System.out.println("3. 의사 정보 수정");
System.out.println("4. 의사 정보 삭제");
System.out.println("5. 종료");
boolean check = true;
while (check) {
System.out.println("원하는 번호 입력 (1~5) : " );
int sel = scan.nextInt();
switch (sel) {
case 1:
ddb.sqlRun();
break;
case 2:
System.out.println("추가할 의사 정보를 입력하세요");
System.out.println("의사번호 :");
int no = scan.nextInt();
System.out.println("전공 :");
String treat = scan.next();
System.out.println("이름 :");
String name = scan.next();
System.out.println("성별 :");
String gen = scan.next();
System.out.println("전화번호 :");
String phone = scan.next();
System.out.println("이메일 :");
String email = scan.next();
System.out.println("포지션 :");
String posistion = scan.next();
docl = new Doctors(no, treat, name, gen, phone, email, posistion);
check = ddb.DocInsert(docl);
if (check) {
System.out.println("insert success");
}
else {
System.out.println("insert failure");
}
break;
case 3:
System.out.println("수정할 의사 정보를 입력하세요");
System.out.println("의사번호 :");
int no1 = scan.nextInt();
docl = new Doctors(no1);
System.out.println("의사번호 :");
no = scan.nextInt();
System.out.println("전공 :");
treat = scan.next();
System.out.println("이름 :");
name = scan.next();
System.out.println("성별 :");
gen = scan.next();
System.out.println("전화번호 :");
phone = scan.next();
System.out.println("이메일 :");
email = scan.next();
System.out.println("포지션 :");
posistion = scan.next();
docl = new Doctors(no, treat, name, gen, phone, email, posistion);
check = ddb.DocUpdate(docl);
if (check) {
System.out.println("update success");
} else {
System.out.println("update failure");
}
break;
case 4:
System.out.println("삭제할 의사번호를 입력하세요");
System.out.println("의사번호 :");
no1 = scan.nextInt();
check = ddb.DocDelete(no1);
if (check) {
System.out.println("insert success");
}
else {
System.out.println("insert failure");
}
System.out.println("테스트 4");
break;
case 5:
System.out.println("종료합니다.");
ddb.close();
check = false;
break;
}
}
}
}
</pre결과
내부에 넣을 내용을 입력해주세요
'Programming? > JDBC' 카테고리의 다른 글
| JDBC 세팅 (0) | 2022.06.16 |
|---|---|
| Quantum DB 설치 (0) | 2012.09.05 |
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
TAG
- jdbc
- GOD EATER2
- DnF_카인
- 무사헌터G
- XML
- Talesweaver_Palshu
- PS VITA
- oracle
- spring
- League of legends
- C Programming
- HTML
- java
- Android
- Unleashed
- 이건사야되!
- 포세리앙 시밤...
- jsp
- PSP
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
글 보관함