2024.03.22_숙제(CRUD_본봉/인사/급여관리 프로그램)

[이규희]

table

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 32 33 34 35 36 37 38 39 40 41 42 43 44

show tables;   -- 직급별 본봉테이블 create table salary (     jikkub char(8) not null primary key, /* 직급 */     bonbong int not null ); desc salary; drop table salary; insert into salary values ('부장', 5000000); insert into salary values ('과장', 4000000); insert into salary values ('대리', 3000000); insert into salary values ('사원', 2000000); select * from salary;   -- 인사관리테이블 create table insa (     idx int not null auto_increment primary key,      /* 인사관리 고유번호 */     sabun char(8) not null,                              /* 직급코드(년(2) 월(2) 일(2) 일련번호(2)) */     buseo varchar(10) not null,                      /* 인사과/총무과/생산과/영업과 */     name varchar(20) not null,                          /* 성명 */     jikkub char(8) not null,                          /* 부장/과장/대리/사원 */     age int default 25,                              /* 나이, 기본값 25 */     ipsail datetime default now(),                      /* 입사일 */     gender char(2) default '여자',                     /* 성별 */     address varchar(30),                             /* 주소 */     unique key (sabun),                                 /* 중복불가키 : 사번 */     foreign key (jikkub) references salary (jikkub)     /* 외래키 */ ); desc insa; drop table insa; insert into insa values (default, '24032101', '인사과', '홍길동', '과장', 35, '2000-1-5', '남자', '서울'); insert into insa values (default, '24032102', '영업과', '김말숙', '대리', 31, '2007-11-25', default, '청주'); insert into insa values (default, '24032201', '총무과', '이기자', '사원', 25, '2022-8-22', '남자', '서울');   select * from insa; drop table insa;                                                                           -- 이기자의 급여????? -- 사원의 본봉? select *from salary where jikkub = '사원'; -- '이기자'의 본봉? select * from salary s, insa i where s.jikkub = i.jikkub; /* 두 테이블을 join시킨 것  */ select i.name as irum, i.jikkub, s.bonbong from salary s, insa i where s.jikkub = i.jikkub;

cs

NetpayVo

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

package hw_0322_CRUD;   public class NetpayVO {     private int idx;     private String sabun;     private String buseo;     private String name;     private String jikkub;     private int age;     private String ipsail;     private String gender;     private String address;     private int bonbong;          public int getIdx() {         return idx;     }     public void setIdx(int idx) {         this.idx = idx;     }     public String getSabun() {         return sabun;     }     public void setSabun(String sabun) {         this.sabun = sabun;     }     public String getBuseo() {         return buseo;     }     public void setBuseo(String buseo) {         this.buseo = buseo;     }     public String getName() {         return name;     }     public void setName(String name) {         this.name = name;     }     public String getJikkub() {         return jikkub;     }     public void setJikkub(String jikkub) {         this.jikkub = jikkub;     }     public int getAge() {         return age;     }     public void setAge(int age) {         this.age = age;     }     public String getIpsail() {         return ipsail;     }     public void setIpsail(String ipsail) {         this.ipsail = ipsail;     }     public String getGender() {         return gender;     }     public void setGender(String gender) {         this.gender = gender;     }     public String getAddress() {         return address;     }     public void setAddress(String address) {         this.address = address;     }     public int getBonbong() {         return bonbong;     }     public void setBonbong(int bonbong) {         this.bonbong = bonbong;     }          @Override     public String toString() {         return "NetpayVO [idx=" + idx + ", sabun=" + sabun + ", buseo=" + buseo + ", name=" + name + ", jikkub=" + jikkub                 + ", age=" + age + ", ipsail=" + ipsail + ", gender=" + gender + ", address=" + address + ", bonbong=" + bonbong                 + "]";         }     }     Colored by Color Scripter

cs

NetpayDAO

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178

package hw_0322_CRUD;       import java.sql.Connection;     import java.sql.DriverManager;     import java.sql.PreparedStatement;     import java.sql.ResultSet;     import java.sql.SQLException;       import java.util.ArrayList;       public class NetpayDAO {         Connection conn = null;         PreparedStatement pstmt = null;         ResultSet rs = null;                  NetpayVO vo = null;         String sql = "";                  public NetpayDAO() {             String url = "jdbc:mysql://localhost:3306/javaclass";             String user = "atom";             String password = "1234";                          try {                 Class.forName("com.mysql.jdbc.Driver");                 conn = DriverManager.getConnection(url, user, password);             } catch (ClassNotFoundException e) {                 System.out.println("드라이버 검색 실패" + e.getMessage());             } catch (SQLException e) {                 System.out.println("데이터베이스 연동 실패" + e.getMessage());             }         }                  //conn객체 Close()         public void connClose() {             try {                 conn.close();             } catch (SQLException e) {}         }                  //pstmt객체 Close         public void pstmtClose() {             try {                 if(pstmt != null) pstmt.close();                 pstmt.close();             } catch (SQLException e) {}         }                  //rs객체 Close         public void rsClose() {             try {                 if(rs != null) rs.close();                 rs.close();             } catch (SQLException e) {}         }           // 직급/본봉테이블 입력         public int setSalaryInput(NetpayVO vo) {             int res = 0;             try {                 sql = "insert into salary values (?,?)";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, vo.getJikkub());                 pstmt.setInt(2, vo.getBonbong());                 res = pstmt.executeUpdate();             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 pstmtClose();             }             return res;         }           // 직급/본봉 조회         public ArrayList<NetpayVO> getSalaryList() {             ArrayList<NetpayVO> vos = new ArrayList<>();                          try {                 sql = "select * from salary order by bonbong desc";                 pstmt = conn.prepareStatement(sql);                 rs = pstmt.executeQuery();                                  while(rs.next()) {                     vo = new NetpayVO();                     vo.setJikkub(rs.getString("jikkub"));                     vo.setBonbong(rs.getInt("bonbong"));                                          vos.add(vo);                 }             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 rsClose();             }             return vos;         }           // 본봉 수정         public int setSalaryUpdate(NetpayVO vo) {             int res = 0;             try {                 sql = "update salary set bonbong=? where jikkub=?";                 pstmt = conn.prepareStatement(sql);                 pstmt.setInt(1, vo.getBonbong());                 pstmt.setString(2, vo.getJikkub());                 res = pstmt.executeUpdate();             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 pstmtClose();             }             return res;         }           //직급 조회         public NetpayVO getjikkubSearch(String jikkub) {             NetpayVO vo = new NetpayVO();                          try {                 sql = "select * from salary where jikkub=?";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, jikkub);                 rs = pstmt.executeQuery();                                  if(rs.next()) {                     vo.setJikkub(jikkub);                     vo.setBonbong(rs.getInt("bonbong"));                 }                 else vo = null;             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 rsClose();             }             return vo;         }           // 삭제처리         public int setSalaryDelete(String jikkub) {             int res = 0;             try {                 sql = "delete from salary where jikkub=?";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, jikkub);                 res = pstmt.executeUpdate();             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 pstmtClose();             }             return res;         }           // 성명조회         public NetpayVO getJikkubSearch(String jikkub) {             NetpayVO vo = new NetpayVO();             try {                 sql = "select * from salary where jikkub=?";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, jikkub);                 rs = pstmt.executeQuery();                                  if(rs.next()) {                     vo.setJikkub(jikkub);                     vo.setBonbong(rs.getInt("bonbong"));                 }                 else vo = null;             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 rsClose();             }             return vo;         }     }                  Colored by Color Scripter

cs

SalaryService

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104

package hw_0322_CRUD;   import java.util.ArrayList; import java.util.Scanner;   public class SalaryService {     Scanner sc = new Scanner(System.in);     NetpayDAO dao = new NetpayDAO();     NetpayVO vo = null;          String ans = "N";     int choice = 0, res = 0;          // 직급/본봉입력     public void setSalaryInput() {         System.out.println("\n [직급/본봉테이블 입력]");         String jikkub = "";                  while(true) {             System.out.print("직급 : ");             jikkub = sc.next();             vo = dao.getJikkubSearch(jikkub);             if(vo == null) break;             else System.out.println("같은 직급이 존재합니다. 다시 입력해주세요.");         }         vo = new NetpayVO();         vo.setJikkub(jikkub);         System.out.print("본봉 : ");         vo.setBonbong(sc.nextInt());                  int res = dao.setSalaryInput(vo);                  if(res != 0) {             System.out.println("본봉테이블에 성공적으로 등록되었습니다.");         }         else System.out.println("본봉테이블 등록에 실패하셨습니다.");     }       // 직급/본봉 조회     public void getSalaryList() {         ArrayList<NetpayVO> vos = dao.getSalaryList();                  System.out.println("\n     [직급/본봉테이블 조회]");         System.out.println("===========================");         System.out.println("사번\t직급\t본봉");         System.out.println("---------------------------");         for(int i=0; i<vos.size(); i++) {             vo = vos.get(i);             System.out.print(" " + (i+1) + "\t");             System.out.print(vo.getJikkub() + "\t");             System.out.println(vo.getBonbong() + "\t");         }         System.out.println("---------------------------");         System.out.println("\t\t총 건수 : " + vos.size() + "명");         System.out.println("===========================");     }       // 본봉 수정     public void setSalaryUpdate() {         salaryBasicSearch();                  System.out.println("\n[본봉 수정]");         System.out.print("수정할 본봉 금액을 입력하세요? ==> ");         vo.setBonbong(sc.nextInt());                  res = dao.setSalaryUpdate(vo);         if(res != 0) {             System.out.println("본봉이 성공적으로 수정되었습니다.");         }         else System.out.println("수정된 내용이 없습니다.");              }       //직급 조회     private void salaryBasicSearch() {         System.out.print("\n조회할 직급을 입력하세요? ==> ");         String jikkub = sc.next();                  vo = dao.getjikkubSearch(jikkub);                  if(vo != null) {             System.out.println("\n직급 : " + vo.getJikkub());             System.out.println("본봉 : " + vo.getBonbong());         }         else System.out.println("검색하신 "+jikkub+"은 없습니다.");     }       // 직급삭제     public void setSalaryDelete() {         salaryBasicSearch();         if(vo != null) {             System.out.print("삭제하시겠습니까? (y/n) ==> ");             ans = sc.next();             if(ans.toUpperCase().equals("Y")) {                 res = dao.setSalaryDelete(vo.getJikkub());                 if(res != 0) System.out.println(vo.getJikkub() + "자료가 삭제되었습니다.");                 else System.out.println(vo.getJikkub() + "자료가 없습니다.");             }             else System.out.println("삭제취소");         }     }   }   Colored by Color Scripter

cs

InsaDAO

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134

package hw_0322_CRUD;   import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList;   public class InsaDAO {     Connection conn = null;     PreparedStatement pstmt = null;     ResultSet rs = null;          NetpayVO vo = null;     String sql = "";          public InsaDAO() {         String url = "jdbc:mysql://localhost:3306/javaclass";         String user = "atom";         String password = "1234";                  try {             Class.forName("com.mysql.jdbc.Driver");             conn = DriverManager.getConnection(url, user, password);         } catch (ClassNotFoundException e) {             System.out.println("드라이버 검색 실패" + e.getMessage());         } catch (SQLException e) {             System.out.println("데이터베이스 연동 실패" + e.getMessage());         }     }              //conn객체 Close()         public void connClose() {             try {                 conn.close();             } catch (SQLException e) {}         }                  //pstmt객체 Close         public void pstmtClose() {             try {                 if(pstmt != null) pstmt.close();                 pstmt.close();             } catch (SQLException e) {}         }                  //rs객체 Close         public void rsClose() {             try {                 if(rs != null) rs.close();                 rs.close();             } catch (SQLException e) {}         }           //전체사원조회         public ArrayList<NetpayVO> getInsaList() {             ArrayList<NetpayVO> vos = new ArrayList<NetpayVO>();                          try {                 sql = "select * from insa";                 pstmt = conn.prepareStatement(sql);                 rs = pstmt.executeQuery();                                  while(rs.next()) {                     vo = new NetpayVO();                     vo.setIdx(rs.getInt("idx"));                     vo.setBuseo(rs.getString("buseo"));                     vo.setName(rs.getString("name"));                     vo.setJikkub(rs.getString("jikkub"));                     vo.setAge(rs.getInt("age"));                     vo.setIpsail(rs.getString("ipsail"));                     vo.setGender(rs.getString("gender"));                     vo.setAddress(rs.getString("address"));                                          vos.add(vo);                 }             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 rsClose();             }             return vos;         }           //성명조회         public NetpayVO getSungjukSearch(String name) {             vo = new NetpayVO();             try {                 sql = "select * from Insa where name=?";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, name);                 rs = pstmt.executeQuery();                                  if(rs.next()) {                     vo.setIdx(rs.getInt("idx"));                     vo.setBuseo(rs.getString("buseo"));                     vo.setName(rs.getString("name"));                     vo.setJikkub(rs.getString("jikkub"));                     vo.setAge(rs.getInt("age"));                     vo.setGender(rs.getString("gender"));                     vo.setAddress(rs.getString("address"));                 }                 else vo = null;             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 rsClose();             }             return vo;         }           //사원자료 입력처리         public int setInsaInput(NetpayVO vo) {             int res = 0;             try {                 sql = "insert into insa values (default,?,?,?,?,?,?)";                 pstmt = conn.prepareStatement(sql);                 pstmt.setString(1, vo.getBuseo());                 pstmt.setString(2, vo.getName());                 pstmt.setString(3, vo.getJikkub());                 pstmt.setInt(4, vo.getAge());                 pstmt.setString(5, vo.getGender());                 pstmt.setString(6, vo.getAddress());                 res = pstmt.executeUpdate();             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             } finally {                 pstmtClose();             }             return res;         } }   Colored by Color Scripter

cs

InsaService

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

package hw_0322_CRUD;   import java.util.ArrayList; import java.util.Scanner;   public class InsaService {     Scanner sc = new Scanner(System.in);     InsaDAO dao = new InsaDAO();     NetpayVO vo = null;          String ans = "N";     int choice = 0, res = 0;       //사원등록     public void setInsaInput() {         vo = new NetpayVO();                  while(true) {             System.out.println("\n[사원등록]");             String name = "" ,buseo = "", jikkub = "", gender = "", address = "";             int age = 0;                          System.out.print("이름 : "); name = sc.next();             System.out.print("부서 : "); buseo = sc.next();             System.out.print("직급 : "); jikkub = sc.next();             System.out.print("나이 : "); age = sc.nextInt();             System.out.print("성별 : "); gender = sc.next();             System.out.print("주소 : "); address = sc.next();                          vo = new NetpayVO();             vo.setName(name);             vo.setBuseo(buseo);             vo.setJikkub(jikkub);             vo.setAge(age);             vo.setGender(gender);             vo.setAddress(address);                          int res = dao.setInsaInput(vo);                          if(res != 0) System.out.println("사원등록이 완료되었습니다.");             else System.out.println("사원등록 실패");                          System.out.print("계속하시겠습니까? (y/n) ==> ");             ans = sc.next();             if(!ans.toUpperCase().equals("Y")) break;         }              }       // 사원 전체조회     public void getInsaList() {          ArrayList<NetpayVO> vos = dao.getInsaList();                    System.out.println("\n\t\t[전체사원조회]");          System.out.println("번호\t부서\t이름\t직급\t나이\t성별\t주소");          System.out.println("-----------------------------------------------------");          for(int i=0; i<vos.size(); i++) {              vo = vos.get(i);              System.out.print(" " + (i+1) + "\t");              System.out.print(vo.getBuseo() + "\t");              System.out.print(vo.getName() + "\t");              System.out.print(vo.getJikkub() + "\t");              System.out.print(vo.getAge() + "\t"); //             System.out.print(vo.getIpsail() + "\t");              System.out.print(vo.getGender() + "\t");              System.out.print(vo.getAddress() + "\n");          }          System.out.println("-----------------------------------------------------");          System.out.println("\t\t\t\t\t총 인원수 : " + vos.size()+"명");          System.out.println("=====================================================");               }     }   Colored by Color Scripter

cs

NetpayRun

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

package hw_0322_CRUD;   import java.util.Scanner;   public class NetpayRun {     public static void main(String[] args) {         Scanner sc = new Scanner(System.in);         SalaryService salaryService = new SalaryService();         InsaService insaService = new InsaService();         NetpayService netpayService = new NetpayService();                  int choice = 0;         boolean run = true;                  while(run) {             System.out.println("\n\t[본봉/인사/급여관리 프로그램]");             System.out.print("[메뉴] 1.본봉관리 2.인사관리 3.급여관리 0.종료 ==> ");             choice = sc.nextInt();                          switch(choice) {                 case 1:                     while(true) {                         System.out.println("\n\t[본봉관리 프로그램입니다]");                         System.out.print("[메뉴] 1.직급/본봉입력  2:직급/본봉조회  3:본봉수정  4:직급삭제  0:종료 ==> ");                         choice = sc.nextInt();                         if(choice == 1) salaryService.setSalaryInput();                         else if(choice == 2) salaryService.getSalaryList();                         else if(choice == 3) salaryService.setSalaryUpdate();                         else if(choice == 4) salaryService.setSalaryDelete();                         else if (choice == 0)    {                             System.out.println("본봉관리 프로그램을 종료합니다.");                             break;                         }                         else System.out.println("1/2/3/4/0 중 입력해주세요");                         }                     break;                 case 2:                     while(true) {                         System.out.println("\n\t *** 인사관리 ***");                         System.out.print("메뉴선택? 1.사원등록  2:사원전체조회  3:사원개별조회  0:종료 ==> ");                         choice = sc.nextInt();                         if(choice == 1) insaService.setInsaInput();                         else if(choice == 2) insaService.getInsaList(); //                        else if(choice == 3) insaService.getInsaSearch();                         else if(choice == 0) {                             System.out.println("인사관리 프로그램을 종료합니다.");                             break;                         }                         else System.out.println("1/2/3/0중 입력해주세요.");                     }                     break;                 case 3:                     while(true) {                         System.out.println("\n\t *** 급여관리 ***");                         System.out.print("1. 사번을 입력하세요? ==> ");                         System.out.print("2. 초과시간을 입력하세요? ==> "); //                        netpayService.getNetpaySearch();                     }                     //break;                 default:                     run = false;             }         }         System.out.println("=========================================================");         System.out.println("프로그램을 종료합니다.");                  sc.close();     } }     Colored by Color Scripter

cs

실행화면

인사관리 테이블의 1(사원등록), 3(사원개별조회)와 급여관리 테이블은 완성하지 못했습니다 ㅠ ㅠ

[행복]

사번처리에 따른 insert부분의 컬럼수도 체크해 보시고요... 수고하셨어요