DB- 학사관리 프로그램
sql 테이블 관계형모델이다.
SQL
먼저 SQL 문으로 교수테이블, 학생테이블, 강좌테이블, 수강신청테이블 을 생성해준다. 그리고 값을 넣어준다. (생략함)
--교수테이블 생성
create table professors(
pcode char(3) not null,
pname varchar(15) not null,
dept varchar(30),
hiredate date,
title varchar(15),
salary int default 0,
primary key(pcode)
);
--학생테이블
create table students(
scode char(8) not null,
sname varchar(15) not null,
dept varchar(30),
year int default 1,
birthday date,
advisor char(3),
primary key(scode),
foreign key(advisor) references professors(pcode)
);
--강좌테이블
create table courses(
lcode char(4) not null,
lname varchar(50) not null,
hours int,
room char(3),
instructor char(3),
capacity int default 0,
persons int default 0,
primary key(lcode),
foreign key(instructor) references professors(pcode)
);
--수강신청 테이블
create table enrollments(
lcode char(4) not null,
scode char(8) not null,
edate date,
grade int default 0,
primary key(lcode, scode),
foreign key(lcode) references courses(lcode),
foreign key(scode) references students(scode)
);
VIEW 생성 (복잡한 SQL문을 쉽게 불러오기위해서)
--학생 테이블의 모든 컬럼과 지도교수 이름,학과출력
create view view_students as
(select s.*, pname, p.dept pdept
from students s, professors p
where advisor=pcode);
--강좌테이블의 모든컬럼과 담당교수이름, 담당교수학과
create view view_courses as
(select c.*, pname, p.dept pdept
from courses c , professors p
where instructor=pcode);
--수강신청테이블의 모든 컬럼과 강좌테이블의 강좌명, 학생테이블의 학생명 출력
create view view_enrollments as
(select e.lcode, c.lname, e.scode, s.sname, edate, grade
from enrollments e , courses c, students s
where e.lcode=c.lcode and e.scode =s.scode);
강좌삭제 프로시저
create or replace PROCEDURE DEL_COURSES
(
i_lcode in varchar, --삭제할 강좌번호
o_count out int --수강신청 인원
)
AS
BEGIN
select count(*) into o_count from enrollments where lcode=i_lcode;
if(o_count=0)then
delete from courses where lcode =i_lcode;
end if;
END DEL_COURSES;
수강신청 삭제 프로시저
create or replace PROCEDURE DEL_ENROLLMENTS (
i_scode in varchar,
i_lcode in varchar,
o_count out int
)
AS
BEGIN
select count(*) into o_count from enrollments where scode=i_scode and lcode=i_lcode;
if(o_count =1)then
delete from enrollments where scode=i_scode and lcode=i_lcode;
end if;
END DEL_ENROLLMENTS;
교수삭제 프로시저
create or replace PROCEDURE DEL_PROFESSOR(
i_pcode in varchar,
s_count out int, --지도하는 학생이 몇명인지
c_count out int -- 담당하는 과목이 몇과목인지
)
AS
BEGIN
select count(*) into s_count from students where advisor=i_pcode;
select count(*) into c_count from courses where instructor=i_pcode;
if(s_count=0 and c_count=0) then
delete from professors where pcode = i_pcode;
end if;
END DEL_PROFESSOR;
수강신청 프로시저
create or replace PROCEDURE IN_ENROLLMENTS (
i_scode in varchar,
i_lcode in varchar,
c_count out int, -- 강좌테이블에 없으면 0, 있으면 1
e_count out int -- 수강신청테이블에 없으면 0, 있으면 1
)
AS
BEGIN
select count(*) into c_count from courses where lcode=i_lcode;
select count(*) into e_count from enrollments where scode=i_scode and lcode=i_lcode;
IF(c_count=1 AND e_count=0)THEN
insert into enrollments(scode,lcode,edate,grade) values(i_scode,i_lcode,sysdate,0);
END IF;
END IN_ENROLLMENTS;
메인메뉴 - 학사관리
public class Main {
public static void main(String[] args) {
Connection CON=Database.CON;
Scanner s = new Scanner(System.in);
boolean run = true;
while(run) {
System.out.println("┌───────────────────────┐");
System.out.println("│ 학사관리 │ ");
System.out.println("└───────────────────────┘");
System.out.print("1.교수관리 ");
System.out.print("2.학생관리 ");
System.out.print("3.강좌관리 ");
System.out.print("0.종료 \n ");
System.out.println("");
System.out.print("선택>");
String menu = s.nextLine();
switch(menu) {
case "1":
Professors.menu();
break;
case "2":
Students.menu(); //static 으로 선언하면 new 객체를 안만들어도된다.
break;
case "3":
Courses.menu();
break;
case "0":
run = false;
break;
default:
System.out.println("0~2번 메뉴를 선택하세요.");
}
}
System.out.println("프로그램종료!");
}
}
메인메뉴로 1번은 교수관리 2번은 학생관리 3번은 강좌관리고 0번은 종료이다.
교수관리
Professors
public class Professors {
private String pcode;
private String pname;
private String dept;
public String getPcode() {
return pcode;
}
public void setPcode(String pcode) {
this.pcode = pcode;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Professors [pcode=" + pcode + ", pname=" + pname + ", dept=" + dept + "]";
}
public static void menu() {
Scanner s = new Scanner(System.in);
boolean run =true;
ProfessorsDAO pdao =new ProfessorsDAO();
while(run) {
System.out.println("┌──────────────────────────────┐");
System.out.println("│ 교수관리 │ ");
System.out.println("└──────────────────────────────┘");
System.out.print("1.등록|2.목록|3.조회|4.삭제|0.메인메뉴\n");
System.out.println("");
System.out.print("선택>");
String menu = s.nextLine();
switch(menu) {
case "1":
Professors vo=new Professors();
System.out.print("교수이름 :"); vo.setPname(s.nextLine());
System.out.print("교수학과 :"); vo.setDept(s.nextLine());
pdao.insert(vo);
break;
case "2":
ArrayList<Professors> array = pdao.list();
for(Professors p:array ) {
System.out.printf("%s\t%s\t%s\n",
p.getDept(),p.getPcode(),p.getPname());
}
break;
case "3":
String pcode ;
do {
System.out.print("교수번호>"); pcode = s.nextLine();
vo=pdao.read(pcode);
if(pcode.equals("")) { // 입력값이 없으면 빠져나간다.
System.out.println("입력값이 없어 조회를 종료합니다.");
break;}
if(vo.getPname()==null)
System.out.println("등록된 교수가 없습니다.");
}while(vo.getPname()==null) ; //교수번호가 없으면 반복하라
if(vo.getPname()!=null) {
System.out.println("교수이름:"+vo.getPname());
System.out.println("교수학과:"+vo.getDept());
System.out.println("────────────────────────────────────────────────");
//지도학생목록출력
ArrayList<Students> sarray= pdao.slist(pcode);
if(sarray.size()==0) {
System.out.println("지도하는 학생이 없습니다.");
} else {
for(Students svo:sarray) {
System.out.printf("%s\t%s\t%s\t%s\t%s\n"
,svo.getScode()
,svo.getSname()
,svo.getDept(),
svo.getBirthday().substring(2,10)
,svo.getPname());
}
}
//강좌목록 출력
ArrayList<Courses> carray= pdao.clist(pcode);
System.out.println("┌─────────┐");
System.out.println("│담당강좌목록│");
System.out.println("└─────────┘");
if(carray.size()==0) {
System.out.println("담당하는 강좌가 없습니다.");
} else {
for(Courses cvo:carray) {
System.out.printf("%s\t%-10s\t%-10s\t%s\n"
,cvo.getLcode(), cvo.getLname(),
cvo.getRoom(), cvo.getHours());
}
}
}
break;
case "4":
String pcode1 ;
do {
System.out.print("교수번호>"); pcode1 = s.nextLine();
vo=pdao.read(pcode1);
if(pcode1.equals("")) { // 입력값이 없으면 빠져나간다.
System.out.println("입력값이 없어 삭제를 종료합니다.");
break;}
if(vo.getPname()==null)
System.out.println("등록된 교수가 없습니다.");
}while(vo.getPname()==null) ;
if(vo.getPname()!=null) {
System.out.println("교수이름:"+vo.getPname());
System.out.println("교수학과:"+vo.getDept());
System.out.print("정말로삭제하시겠습니까? (y/ㅛ)");
String sel = s.nextLine();
if(sel.equals("y")||sel.equals("ㅛ")) {
int result = pdao.delete(pcode1);
if(result ==1) {
System.out.println("교수삭제성공!");
}else {
System.out.println("지도학생이나 담당과목이 있어서 삭제 할 수 없습니다.");
}
}
}
break;
case "0":
run=false;
break;
default:
System.out.println("0~3번 메뉴를 선택하세요!");
}
}
System.out.println("메인메뉴로 돌아갑니다!");
}
}
- 등록 : 교수이름, 학과를 입력받아서 insert해준다.
- 목록 : array 에 담겨있는 list() 함수를 불러와서 for문을 돌려 출력해준다.
- 조회 : 교수번호를 입력받아서 read 값으로 입력받아서 입력값이 없으면 종료하고 교수번호가 없으면 반복하는데 do while 문을 써서 반복해게 처리했다. 교수이름과 학과를 출력하고 그밑에는 지도하는 학생과 담당강좌목록을 출력한다.
- 삭제 : do while 문을 써서 교수번호를 읽어와서 등록된 교수가있다면 정보를 읽어와서 삭제할지 물어보고 삭제한다.
ProfessorsDAO
public class ProfessorsDAO {
//교수목록
public ArrayList<Professors> list(){
ArrayList<Professors> array = new ArrayList<>();
try {
String sql ="select * from professors";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Professors vo = new Professors();
vo.setPcode(rs.getString("pcode"));
vo.setPname(rs.getString("pname"));
vo.setDept(rs.getString("dept"));
array.add(vo);
}
}catch(Exception e) {
System.out.println("list:"+e.toString());
}
return array;
}
//교수등록
public void insert(Professors vo) {
try {
String sql = "select max(pcode)+1 code from professors";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
String code =rs.getString("code");
vo.setPcode(code);
sql = "insert into professors(pcode,pname,dept) values(?,?,?)";
ps = Database.CON.prepareStatement(sql);
ps.setString(1, code);
ps.setString(2, vo.getPname());
ps.setString(3, vo.getDept());
ps.execute();
System.out.println("등록완료!");
}
}catch(Exception e) {
System.out.println("insert:"+e.toString());
}
}
//교수조회
public Professors read(String pcode) {
Professors vo = new Professors();
try {
String sql = "select * from professors where pcode=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, pcode);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
vo.setPcode(rs.getString("pcode"));
vo.setPname(rs.getString("pname"));
vo.setDept(rs.getString("dept"));
}
}catch(Exception e) {
System.out.println("read:"+e.toString());
}
return vo;
}
//특정 교수가 지도하는 학생목록
public ArrayList<Students> slist(String pcode) {
ArrayList<Students> slist = new ArrayList<>();
try {
String sql = "select * from view_students where advisor=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, pcode);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Students vo = new Students();
vo.setScode(rs.getString("scode"));
vo.setSname(rs.getString("sname"));
vo.setDept(rs.getString("dept"));
vo.setBirthday(rs.getString("birthday"));
vo.setPname(rs.getString("pname"));
slist.add(vo);
}
}catch(Exception e) {
System.out.println("slist:"+e.toString());
}
return slist;
}
//특정 교수가 담당하는 강좌목록
public ArrayList<Courses> clist(String pcode) {
ArrayList<Courses> array = new ArrayList<>();
try {
String sql = "select * from courses where instructor=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, pcode);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Courses vo = new Courses();
vo.setLcode(rs.getString("lcode"));
vo.setLname(rs.getString("lname"));
vo.setRoom(rs.getString("room"));
vo.setInstructor(rs.getString("instructor"));
vo.setHours(rs.getInt("hours"));
array.add(vo);
}
}catch(Exception e) {
System.out.println("clist:"+e.toString());
}
return array;
}
//교수삭제
public int delete(String pcode) {
int result=0;
try {
String sql = "call del_professor(?,?,?)";
CallableStatement cs =Database.CON.prepareCall(sql);
cs.setString(1, pcode);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
cs.execute();
int s_count = (int) cs.getObject(2);
int c_count = (int) cs.getObject(3);
if(s_count==0 && c_count==0) {
result = 1;
}else {
result =2;
}
}catch(Exception e) {
System.out.println("delete:"+e.toString());
}
return result;
}
}
학생관리
Students
public class Students extends Professors {
private String scode;
private String sname;
private String year;
private String birthday;
private String advisor;
public String getScode() {
return scode;
}
public void setScode(String scode) {
this.scode = scode;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getAdvisor() {
return advisor;
}
public void setAdvisor(String advisor) {
this.advisor = advisor;
}
@Override
public String toString() {
return "Students [scode=" + scode + ", sname=" + sname + ", year=" + year + ", birthday=" + birthday
+ ", advisor=" + advisor + "]";
}
public static void menu() {
Scanner s = new Scanner(System.in);
boolean run =true;
StudentsDAO sdao =new StudentsDAO();
ProfessorsDAO pdao =new ProfessorsDAO();
SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
while(run) {
System.out.println("┌──────────────────────────────┐");
System.out.println("│ 학생관리 │ ");
System.out.println("└──────────────────────────────┘");
System.out.print("1.등록|2.목록|3.조회|4.삭제|0.메인메뉴\n");
System.out.println("");
System.out.print("선택>");
String menu = s.nextLine();
switch(menu) {
case "1":
Students v=new Students();
Professors pvo=new Professors();
System.out.println("┌──────┐");
System.out.println("│학생등록│");
System.out.println("└──────┘");
System.out.print("학생이름>");v.setSname(s.nextLine());
System.out.print("학생학과>");v.setDept(s.nextLine());
do {
System.out.print("지도교수>");
String pcode=s.nextLine();
v.setAdvisor(pcode);
pvo=pdao.read(pcode);
if(pvo.getPname()==null)
System.out.println("담당교수가 없습니다!");
}while(pvo.getPname()==null);
if(pvo.getPname()!=null) {
sdao.insert(v);
}
break;
case "2":
ArrayList<Students> array= sdao.list();
System.out.printf("%s\t\t%s\t%s\t%s\t%s\n",
"학번","이름","학과","학년","지도교수이름");
System.out.println("──────────────────────────────────────────────────────");
for(Students stu:array) {
System.out.printf("%s\t%s\t%s\t%s\t%s\n",
stu.getScode(), stu.getSname(), stu.getDept(),
stu.getYear(),stu.getPname());
}
break;
case "3":
System.out.print("학생이름>");
String sname =s.nextLine();
Students vo =sdao.read(sname);
if(vo.getSname()==null) {
System.out.println("해당학생을 찾을수없습니다.");
}else {
System.out.println("학생번호:" +vo.getScode());
System.out.println("학생학과:" +vo.getDept());
System.out.println("지도교수:"+vo.getPname());
//수강신청 과목
System.out.println("┌────────────────┐");
System.out.println("│"+vo.getSname()+"의 수강신청과목 │");
System.out.println("└────────────────┘");
ArrayList<HashMap<String, Object>> carray = sdao.clist(sname);
if(carray.size()==0) {
System.out.println("수강 신청한 과목이 없습니다.");
}else {
for(HashMap<String, Object> map:carray) {
String edate= String.valueOf(map.get("edate"));
Date date = new Date();
try {
date = sdf.parse(edate);
} catch (ParseException e) {
e.printStackTrace();
}
System.out.printf("%s\t%-20s\t%s\t%s\n",
map.get("lcode"), map.get("lname")
,sdf.format(date), map.get("grade"));
}
}
// 수강신청또는 수강취소
boolean erun =true;
System.out.println("┌───────────────────────────────┐");
System.out.println("│1.수강신청 2.수강취소 3.신청목록 0.취소 │");
System.out.println("└───────────────────────────────┘");
while(erun) {
System.out.print("메뉴선택>"); String emenu = s.nextLine();
String lcode;
switch(emenu) {
case "1":
System.out.print("신청할 강좌번호>"); lcode=s.nextLine();
int result = sdao.inEnrollments(vo.getScode(), lcode);
if(result == 0) {
System.out.println("등록된 강좌가 없습니다.");
}else if(result ==2) {
System.out.println("이미수강이 되었습니다.");
}else if(result == 1) {
System.out.println("수강신청완료!");
}
break;
case "2": //수강취소
System.out.print("취소할 강좌번호>"); lcode= s.nextLine();
result= sdao.delEnrollments(vo.getScode(), lcode);
if(result ==0) {
System.out.println("수강취소 데이터가 없습니다.");
}else {
System.out.println("수강취소완료!");
}
break;
case "3":
ArrayList<HashMap<String, Object>> aray = sdao.clist(sname);
if(aray.size()==0) {
System.out.println("수강 신청한 과목이 없습니다.");
}else {
for(HashMap<String, Object> map:aray) {
String edate= String.valueOf(map.get("edate"));
Date date = new Date();
try {
date = sdf.parse(edate);
} catch (ParseException e) {
e.printStackTrace();
}
System.out.printf("%s\t%-20s\t%s\t%s\n",
map.get("lcode"), map.get("lname")
,sdf.format(date), map.get("grade"));
}
}
break;
case "0":
erun =false;
break;
default:
System.out.println("0~2번 메뉴를 선택하세요!");
}
}
}
break;
case "4":
System.out.println("┌──────┐");
System.out.println("│학생삭제│");
System.out.println("└──────┘");
System.out.print("학생이름>");
sname =s.nextLine();
vo =sdao.read(sname);
if(vo.getSname()==null) {
System.out.println("해당학생을 찾을수없습니다.");
}else {
System.out.println("학생번호:" +vo.getScode());
System.out.println("학생학과:" +vo.getDept());
System.out.println("지도교수:"+vo.getPname());
System.out.println("──────────────────────────");
System.out.print("삭제하실래요? >"); String sel=s.nextLine();
if(sel.equals("y")||sel.equals("Y")||sel.equals("ㅛ")) {
int count =sdao.delete(sname);
if(count==0) {
System.out.println("학생삭제완료!");
}else {
System.out.println("수강신청한 강좌가 "+count +"개존재합니다.");
}
}
}
break;
case "0":
run= false;
break;
default:
System.out.println("0~4번 메뉴를 선택하세요!");
}
}
System.out.println("<메인메뉴>로 돌아갑니다.");
}
}
- 등록 : 학생이름, 학과, 지도교수를 입력으로 받는데 지도교수 부분에서 do while 문을 사용해 교수가 있는지없는지 교수dao에서 값을 읽어와서 없다면 다시 입력값을 받는다 있다면 insert되어 등록이 완료된다.
- 목록 : list()에서 값을 읽어와서 for문으로 출력한다.
- 조회 : 여기서는 총 3가지 의 출력이있는데 조회한 값을 보여주고 수강신청과목을 보여준다. 그리고 수강신청, 취소, 목록 부분이있는데 한번더 while 문을 돌린다. 수강신청은 수강신청할 강좌가없는경우와 수강을 이미 신청한경우 그리고 수강신청완료가 된다. 수강취소부분은 프로시저를 사용해서 수강취소를해주고 신청목록 부분은 2번목록과 똑같다.
- 삭제 : 삭제할 학생이름을 받아서 정보를 출력해준후 삭제할것인지 묻고 삭제한다. 삭제를 할수없는 경우 (수강신청강좌가 존재하는경우)에는 수강신청한강좌의 갯수를 보여준다.
StudentsDAO
public class StudentsDAO {
//학생목록출력
public ArrayList<Students> list(){
ArrayList<Students> array = new ArrayList<>();
try {
String sql = "select * from view_students";
PreparedStatement ps =Database.CON.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Students vo = new Students();
vo.setScode(rs.getString("scode"));
vo.setSname(rs.getString("sname"));
vo.setDept(rs.getString("dept"));
vo.setYear(rs.getString("year"));
vo.setPname(rs.getString("pname"));
array.add(vo);
}
} catch(Exception e) {
System.out.println("list:"+e.toString());
}
return array;
}
//학생등록
public void insert(Students vo) {
try {
String sql="select max(scode)+1 code from students";
PreparedStatement ps=Database.CON.prepareStatement(sql);
ResultSet rs=ps.executeQuery();
if(rs.next()) {
vo.setScode(rs.getString("code"));
sql="insert into students(scode,sname,dept,advisor) values(?,?,?,?)";
ps=Database.CON.prepareStatement(sql);
ps.setString(1, vo.getScode());
ps.setString(2, vo.getSname());
ps.setString(3, vo.getDept());
ps.setString(4, vo.getAdvisor());
ps.execute();
System.out.println("학생등록완료!");
}
}catch(Exception e) {
System.out.println("insert:" + e.toString());
}
}
//조회
public Students read(String sname) {
Students vo = new Students();
try {
String sql ="select * from view_students where sname=?";
PreparedStatement ps=Database.CON.prepareStatement(sql);
ps.setString(1, sname);
ResultSet rs = ps.executeQuery();
if(rs.next()){
vo.setScode(rs.getString("scode"));
vo.setSname(rs.getString("sname"));
vo.setDept(rs.getString("dept"));
vo.setPname(rs.getString("pname"));
}
}catch(Exception e) {
System.out.println("read:" + e.toString());
}
return vo;
}
//특정학생이 수강신청한 강좌목록
public ArrayList<HashMap<String, Object>> clist(String sname){
ArrayList<HashMap<String, Object>> array = new ArrayList<>();
try {
String sql = "select * from view_enrollments where sname=?";
PreparedStatement ps=Database.CON.prepareStatement(sql);
ps.setString(1, sname);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
HashMap<String, Object> map = new HashMap<>();
map.put("lcode", rs.getString("lcode"));
map.put("lname", rs.getString("lname"));
map.put("edate", rs.getString("edate"));
map.put("grade", rs.getString("grade"));
array.add(map);
}
}catch(Exception e) {
System.out.println("clist:" + e.toString());
}
return array;
}
//학생삭제
public int delete(String sname) {
int count=0; //수강신청한 과목수
try {
String sql ="select count(*) cnt from view_enrollments where sname=?";
PreparedStatement ps=Database.CON.prepareStatement(sql);
ps.setString(1, sname);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
count=rs.getInt("cnt");
if(count ==0) {
sql = "delete from students where sname=?";
ps=Database.CON.prepareStatement(sql);
ps.setString(1, sname);
ps.execute();
}
}
}catch(Exception e) {
System.out.println("delete:" + e.toString());
}
return count;
}
//수강신청
public int inEnrollments(String scode, String lcode) {
int result = 0; //강좌테이블에 강좌가 없는경우
try {
String sql = "call in_enrollments(?,?,?,?)";
CallableStatement cs = Database.CON.prepareCall(sql);
cs.setString(1, scode);
cs.setString(2, lcode);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.execute();
int c_count=(int) cs.getObject(3);
int e_count=(int) cs.getObject(4);
if(c_count==1) {
if(e_count==0) {
result = 1; //강좌테이블에 강좌가있고 수강신청이 없는경우
} else {
result =2; //이미 수강신청이 있는경우
}
}
}catch(Exception e) {
System.out.println("inEnrollments:"+e.toString());
}
return result;
}
//수강취소
public int delEnrollments(String scode, String lcode) {
int result =0;
try {
String sql = "call del_enrollments(?,?,?)";
CallableStatement cs = Database.CON.prepareCall(sql);
cs.setString(1, scode);
cs.setString(2, lcode);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
cs.execute();
result = (int)cs.getObject(3);
}catch(Exception e) {
System.out.println("inEnrollments:"+e.toString());
}
return result;
}
}
강좌관리
Courses
public class Courses extends Professors {
private String lcode;
private String lname;
private String room;
private int hours;
private String instructor;
public String getLcode() {
return lcode;
}
public void setLcode(String lcode) {
this.lcode = lcode;
}
public String getLname() {
return lname;
}
public void setLname(String lname) {
this.lname = lname;
}
public String getRoom() {
return room;
}
public void setRoom(String room) {
this.room = room;
}
public int getHours() {
return hours;
}
public void setHours(int hours) {
this.hours = hours;
}
public String getInstructor() {
return instructor;
}
public void setInstructor(String instructor) {
this.instructor = instructor;
}
@Override
public String toString() {
return "Courses [lcode=" + lcode + ", lname=" + lname + ", room=" + room + ", hours=" + hours + ", instructor="
+ instructor + "]";
}
public static void menu() {
Scanner s = new Scanner(System.in);
SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
boolean run =true;
StudentsDAO sdao =new StudentsDAO();
ProfessorsDAO pdao =new ProfessorsDAO();
CoursesDAO cdao= new CoursesDAO();
while(run) {
System.out.println("┌──────────────────────────────┐");
System.out.println("│ 강좌관리 │ ");
System.out.println("└──────────────────────────────┘");
System.out.print("1.등록|2.목록|3.조회|4.삭제|0.메인메뉴\n");
System.out.println("");
System.out.print("선택>");
String menu = s.nextLine();
switch(menu) {
case "1":
Courses cvo = new Courses();
cvo.setLcode(cdao.getCode());
System.out.println("강좌번호:"+cdao.getCode());
System.out.print("강좌이름>"); cvo.setLname(s.nextLine());
System.out.print("강의실>"); cvo.setRoom(s.nextLine());
//입력한교수가 존재하는지 체크
Professors pvo = new Professors();
do {
System.out.print("담당교수>"); String pcode = s.nextLine();
cvo.setInstructor(pcode);
pvo = pdao.read(pcode);
if(pvo.getPname()==null) System.out.println("교수가 없습니다.");
}while(pvo.getPname()==null);
if(pvo.getPname()!=null) {
System.out.print(pvo.getPname()+"교수의 과목을 저장하실래요?");
String sel=s.nextLine();
if(sel.equals("y")||sel.equals("Y")||sel.equals("ㅛ")) {
cdao.insert(cvo);
}
}
break;
case "2":
ArrayList<Courses> array =cdao.list();
for(Courses vo:array) {
System.out.printf("%s\t%-20s\t%s\t%d\n",
vo.getLcode(),
vo.getLname(),
vo.getRoom(),
vo.getHours(),
vo.getPname());
}
break;
case "3":
System.out.print("강좌명>"); String lname= s.nextLine();
cvo=cdao.read(lname);
if(cvo.getLname()==null) {
System.out.println("해당강좌가 존재하지않습니다.");
}else {
System.out.println("강좌코드:"+cvo.getLcode());
System.out.println("강좌이름:"+cvo.getLname());
System.out.println("강의실:" +cvo.getRoom());
System.out.println("담당교수:" +cvo.getPname());
System.out.println("──────────────────────────────────────────");
ArrayList<HashMap<String,Object>> sarray =cdao.slist(lname);
if(sarray.size()==0) {
System.out.println("수강신청한 학생이 없습니다.");
}else {
System.out.printf("%s\t%s\t\t%s\t\t%s\n", "학번" , "이름" ,"생년월일" ,"점수");
for(HashMap<String, Object> map:sarray) {
String edate= String.valueOf(map.get("edate"));
Date date = new Date();
try {
date = sdf.parse(edate);
} catch (ParseException e) {
e.printStackTrace();
}
System.out.printf("%s\t%s\t%s\t%s\n",
map.get("scode"),
map.get("sname"),
sdf.format(date),
map.get("grade"));
}
}
}
break;
case "4":
System.out.print("강좌명>"); lname =s.nextLine();
cvo=cdao.read(lname);
if(cvo.getLname()==null) {
System.out.println("해당강좌가 존재하지않습니다.");
}else {
System.out.println("강좌코드:"+cvo.getLcode());
System.out.println("강좌이름:"+cvo.getLname());
System.out.println("강의실:" +cvo.getRoom());
System.out.println("담당교수:" +cvo.getPname());
System.out.print(cvo.getLname()+"강좌를 삭제하실래요? >");
String sel = s.nextLine();
if(sel.equals("y")||sel.equals("Y")||sel.equals("ㅛ")) {
int count =cdao.delete(cvo.getLcode());
if(count==0) {
System.out.println("강좌삭제완료!");
}else {
System.out.println(count +"명 수강신청 데이터가 있습니다.");
}
}
}
break;
case "0":
run=false;
break;
default:
System.out.println("0~4번 메뉴를 선택하세요!");
}
}
System.out.println("<메인메뉴>로 돌아갑니다.");
}
}
- 등록 : 강좌번호, 강좌이름, 강의실을 입력받는다. 담당교수는 DOWHILE문으로 입력한 교수가 존재하는지 체크한다.
- 목록 : LIST로 값을 불러와서 FOR문으로 출력해준다.
- 조회 : 강좌명을 입력받아서 일치한게 있다면 강좌정보를 출력해주고 수강신청한 학생목록을 보여준다.
- 삭제 : 강좌명을 입력받아 일치한게있다면 정보를보여주고 강좌를 듣는 학생의 데이터가있다면 몇명이있다고보여준다. (삭제불가능) 데이터가없다면 삭제완료가된다.
CoursesDAO
public class CoursesDAO {
//강좌목록
public ArrayList<Courses> list(){
ArrayList<Courses> array= new ArrayList<>();
try {
String sql ="select * from view_courses";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Courses vo = new Courses();
vo.setLcode(rs.getString("lcode"));
vo.setLname(rs.getString("lname"));
vo.setHours(rs.getInt("hours"));
vo.setRoom(rs.getString("room"));
vo.setPname(rs.getString("pname"));
array.add(vo);
}
}catch(Exception e) {
System.out.println("list" +e.toString());
}
return array;
}
//새로운 강좌번호 구하기
public String getCode() {
String code="";
try {
String sql = "select max(lcode) code from courses";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
String max =rs.getString("code").substring(1);
int newCode=Integer.parseInt(max)+1;
code = "N" +newCode;
}
}catch(Exception e) {
System.out.println("getCode" +e.toString());
}
return code;
}
//강좌등록
public void insert (Courses vo) {
try {
String sql ="insert into courses(lcode,lname,room,instructor) values(?,?,?,?)";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, vo.getLcode());
ps.setString(2, vo.getLname());
ps.setString(3, vo.getRoom());
ps.setString(4, vo.getInstructor());
ps.execute();
System.out.println("강좌등록완료!");
}catch(Exception e) {
System.out.println("insert" +e.toString());
}
}
//강좌조회
public Courses read(String lname) {
Courses vo = new Courses();
try {
String sql ="select * from view_courses where lname like ?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, "%"+lname +"%");
ResultSet rs = ps.executeQuery();
if(rs.next()) {
vo.setLcode(rs.getString("lcode"));
vo.setLname(rs.getString("lname"));
vo.setRoom(rs.getString("room"));
vo.setPname(rs.getString("pname"));
}
}catch(Exception e) {
System.out.println("insert" +e.toString());
}
return vo;
}
//특정강좌를 신청한 학생목록
public ArrayList<HashMap<String,Object>> slist (String lname) {
ArrayList<HashMap<String,Object>> array = new ArrayList<>();
try {
String sql ="select * from view_enrollments where lname like ?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, "%"+lname +"%");
ResultSet rs = ps.executeQuery();
while(rs.next()) {
HashMap<String, Object> map = new HashMap<>();
map.put("scode", rs.getString("scode"));
map.put("sname", rs.getString("sname"));
map.put("grade", rs.getString("grade"));
map.put("edate", rs.getString("edate"));
array.add(map);
}
}catch(Exception e) {
System.out.println("insert" +e.toString());
}
return array;
}
//강좌 삭제
public int delete(String lcode) {
int count = 0;
try {
String sql = "call del_courses(?,?)";
CallableStatement cs = Database.CON.prepareCall(sql);
cs.setString(1, lcode);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
cs.execute();
count = (int)cs.getObject(2);
}catch(Exception e) {
System.out.println("delete" +e.toString());
}
return count;
}
}
댓글남기기