DB- 실습07
메인은 학사관리로 1. 교수관리 2.학생관리 3.강좌관리 가있는데
1번부터 하겠다.
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":
System.out.print("교수번호>"); String pcode = s.nextLine();
vo=pdao.read(pcode);
if(vo.getPname()==null) {
System.out.println("등록된 교수가 없습니다.");
}else {
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":
System.out.print("교수번호>"); pcode = s.nextLine();
vo=pdao.read(pcode);
if(vo.getPname()==null) {
System.out.println("등록된 교수가 없습니다.");
}else {
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(pcode);
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("메인메뉴로 돌아갑니다!");
}
}
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;
}
}
Professors
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 + "]";
}
}
Professors
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 + "]";
}
}
댓글남기기