RUN
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 | package t6_Bank; import java.util.Scanner; public class BankRun { public static void main(String[] args) { Scanner scanner = new Scanner(System.in); BankService service = new BankService(); boolean run = true; int number; while(run) { System.out.println("\n****원하시는 작업을 선택하세요 ****"); System.out.print("1.입력 2.수입내역 3.지출내역 4.총내역 5.수정 6.삭제 7.종료 ==> "); number = scanner.nextInt(); switch (number) { case 1: // 입력 service.input(); break; case 2: // 수입내역조회 service.suip(); break; case 3: // 지출내역조회 service.jichul(); break; case 4: // 총내역조회 service.list(); break; case 5: // 수정 service.update(); break; case 6: // 삭제 service.delete(); break; case 7: // 종료 System.out.println("프로그램을 종료합니다."); run = false; break; default: System.out.println("잘못된 값을 입력하였습니다. 다시 입력해 주세요."); run = true; } } System.out.println("======================================="); System.out.println("\t작업 끝!"); scanner.close(); } } | cs |
VO
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 | package t6_Bank; public class BankVO { private int idx; private String wDate; private int suip; private int jichul; private String bigo; public int getIdx() { return idx; } public void setIdx(int idx) { this.idx = idx; } public String getwDate() { return wDate; } public void setwDate(String wDate) { this.wDate = wDate; } public int getSuip() { return suip; } public void setSuip(int suip) { this.suip = suip; } public int getJichul() { return jichul; } public void setJichul(int jichul) { this.jichul = jichul; } public String getBigo() { return bigo; } public void setBigo(String bigo) { this.bigo = bigo; } @Override public String toString() { return "BankVO [idx=" + idx + ", wDate=" + wDate + ", suip=" + suip + ", jichul=" + jichul + ", bigo=" + bigo + "]"; } } | cs |
SERVICE
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 | package t6_Bank; import java.util.ArrayList; import java.util.Scanner; public class BankService { Scanner scanner = new Scanner(System.in); BankDAO dao = new BankDAO(); BankVO vo = new BankVO(); public void input() { while(true) { System.out.println("입력할 내용을 선택하세요."); System.out.print("1:수입 2:지출 [종료:999] ==> "); int sel = scanner.nextInt(); if(sel == 999) { System.out.println("입력을 종료합니다."); break; } else if(sel < 1 || sel > 2) { System.out.println("잘못된 값을 입력하였습니다. 다시 입력해 주세요."); continue; } else if(sel == 1) { System.out.print("수입 날짜를 입력하세요 : "); vo.setwDate(scanner.next()); System.out.print("수입 금액을 입력하세요 : "); vo.setSuip(scanner.nextInt()); vo.setJichul(0); System.out.print("비고란을 입력하세요 : "); vo.setBigo(scanner.next()); } else { System.out.print("지출 날짜를 입력하세요 : "); vo.setwDate(scanner.next()); System.out.print("지출 금액을 입력하세요 : "); vo.setJichul(scanner.nextInt()); vo.setSuip(0); System.out.print("비고란을 입력하세요 : "); vo.setBigo(scanner.next()); } dao.input(vo); } } public void suip() { int suip=0; ArrayList<BankVO> vos = dao.suiplist(); System.out.println("======================================"); System.out.println("날짜\t 수입금액\t비고\t고유번호"); System.out.println("--------------------------------------"); for(int i=0; i<vos.size(); i++) { vo=vos.get(i); System.out.println(vo.getwDate().substring(0,10)+"\t"+vo.getSuip()+"\t"+vo.getBigo()+"\t"+vo.getIdx()); suip += vo.getSuip(); } System.out.println("======================================"); System.out.println("수입 합계 : "+suip); System.out.println("======================================"); } public void jichul() { int jichul=0; ArrayList<BankVO> vos = dao.jichullist(); System.out.println("======================================"); System.out.println("날짜\t 지출금액\t비고\t고유번호"); System.out.println("--------------------------------------"); for(int i=0; i<vos.size(); i++) { vo=vos.get(i); System.out.println(vo.getwDate().substring(0, 10)+"\t"+vo.getJichul()+"\t"+vo.getBigo()+"\t"+vo.getIdx()); jichul += vo.getJichul(); } System.out.println("======================================"); System.out.println("지출 합계 : "+jichul); System.out.println("======================================"); } public void list() { int suip=0, jichul=0; ArrayList<BankVO> vos = dao.list(); System.out.println("================================================"); System.out.println(" 날짜\t 수입금액\t지출금액\t비고\t고유번호"); System.out.println("------------------------------------------------"); for(int i=0; i<vos.size(); i++) { vo=vos.get(i); System.out.println(vo.getwDate().subSequence(0, 10)+"\t"+vo.getSuip()+"\t"+vo.getJichul()+"\t"+vo.getBigo()+"\t"+vo.getIdx()); suip += vo.getSuip(); jichul += vo.getJichul(); } System.out.println("================================================"); System.out.println("수입 합계 : "+suip); System.out.println("지출 합계 : "+jichul); System.out.println("================================================"); } public void updateProcess(BankVO vo) { boolean run = true; while(run) { vo = dao.idxSearch(vo.getIdx()); System.out.println(); System.out.println("고유번호 "+vo.getIdx()+"번의 수정할 항목을 선택하세요"); System.out.println(vo.getwDate().substring(0,10)+" "+vo.getSuip()+" "+vo.getJichul()+" "+vo.getBigo()); System.out.print(" 1:날짜\t 2:수입 3:지출 4:비고 5:종료 ==> "); int sel = scanner.nextInt(); switch (sel) { case 1: System.out.print("수정할 날짜를 입력하세요 ==>"); vo.setwDate(scanner.next()); System.out.println("--->"+vo.getIdx()+"번 날짜가 수정되었습니다."); break; case 2: System.out.print("수정할 수입을 입력하세요 ==>"); vo.setSuip(scanner.nextInt()); System.out.println("--->"+vo.getIdx()+"번 수입이 수정되었습니다."); break; case 3: System.out.print("수정할 지출을 입력하세요 ==>"); vo.setJichul(scanner.nextInt()); System.out.println("--->"+vo.getIdx()+"번 지출이 수정되었습니다."); break; case 4: System.out.print("수정할 비고를 입력하세요 ==>"); vo.setBigo(scanner.next()); System.out.println("--->"+vo.getIdx()+"번 비고란이 수정되었습니다."); break; case 5: System.out.println("프로그램을 종료합니다."); run = false; break; default: System.out.println("잘못된 값을 입력하였습니다. 다시 입력해 주세요."); run = true; } dao.updateProcess(vo); } System.out.println("수정을 종료합니다."); } // 고유번호 검색 public void update() { System.out.print("수정할 내용의 고유번호를 입력하세요 ==> "); int idx = scanner.nextInt(); BankVO vo = dao.idxSearch(idx); if(vo.getwDate() == null) System.out.println("---> 검색 된 자료가 없습니다. 다시 입력해 주세요."); else updateProcess(vo); } public void delete() { System.out.print("삭제 할 내역의 고유번호를 입력하세요 ==> "); int idx = scanner.nextInt(); int result = dao.delete(idx); if(result == 1) System.out.println("--->"+idx+"번의 자료가 삭제되었습니다."); else System.out.println("--->"+idx+"번의 자료가 검색되지 않아 삭제되지 않았습니다."); } } | cs |
DAO
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 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | package t6_Bank; 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 BankDAO { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql; BankVO vo = null; //써먹을때 생성해야함. 객체 통일을 위함. public BankDAO() { try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/javaworks"; String user = "javaworks"; String password = "1234"; conn = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException e) { System.out.println("드라이버 연결 실패!"); } catch (SQLException e) { System.out.println("데이터베이스 연결 실패!"); } } public void connClose() { try { conn.close(); } catch (SQLException e) {} } public void pstmtClose() { try { if(pstmt != null) { pstmt.close(); } } catch (SQLException e) {} } public void rsClose() { try { if(rs != null) { rs.close(); pstmt.close(); } } catch (SQLException e) {} } public void input(BankVO vo) { try { sql = "insert into bank values (default,?,?,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, vo.getwDate()); pstmt.setInt(2, vo.getSuip()); pstmt.setInt(3, vo.getJichul()); pstmt.setString(4, vo.getBigo()); pstmt.executeUpdate(); System.out.println("자료가 등록되었습니다."); } catch (Exception e) { System.out.println("SQL 에러발생 : "+e.getMessage()); } finally { pstmtClose(); } } public ArrayList<BankVO> suiplist() { ArrayList<BankVO> vos = new ArrayList<>(); try { sql = "select wDate,suip,bigo,idx from bank"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()) { BankVO vo = new BankVO(); vo.setwDate(rs.getString("wDate")); vo.setSuip(rs.getInt("suip")); vo.setBigo(rs.getString("bigo")); vo.setIdx(rs.getInt("idx")); vos.add(vo); } } catch (SQLException e) { System.out.println("SQL 에러 발생222 : "+e.getMessage()); } finally { rsClose(); } return vos; } public ArrayList<BankVO> jichullist() { ArrayList<BankVO> vos = new ArrayList<>(); try { sql = "select wDate,jichul,bigo,idx from bank"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()) { BankVO vo = new BankVO(); vo.setwDate(rs.getString("wDate")); vo.setJichul(rs.getInt("jichul")); vo.setBigo(rs.getString("bigo")); vo.setIdx(rs.getInt("idx")); vos.add(vo); } } catch (SQLException e) { System.out.println("SQL 에러 발생 : "+e.getMessage()); } finally { rsClose(); } return vos; } public ArrayList<BankVO> list() { ArrayList<BankVO> vos = new ArrayList<>(); try { sql = "select * from bank"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()) { BankVO vo = new BankVO(); vo.setIdx(rs.getInt("idx")); vo.setwDate(rs.getString("wDate")); vo.setSuip(rs.getInt("suip")); vo.setJichul(rs.getInt("jichul")); vo.setBigo(rs.getString("bigo")); vos.add(vo); } } catch (SQLException e) { System.out.println("SQL 에러 발생 : "+e.getMessage()); } finally { rsClose(); } return vos; } public BankVO idxSearch(int idx) { vo = new BankVO(); try { sql="select * from bank where idx=?"; pstmt=conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); if(rs.next()) { vo.setIdx(rs.getInt("idx")); vo.setwDate(rs.getString("wDate")); vo.setSuip(rs.getInt("suip")); vo.setJichul(rs.getInt("jichul")); vo.setBigo(rs.getString("bigo")); } } catch (SQLException e) { System.out.println("SQL 에러 발생 : "+e.getMessage()); }finally { rsClose(); } return vo; } public void updateProcess(BankVO vo) { try { sql ="update bank set wDate=?,suip=?,jichul=?,bigo=? where idx=?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, vo.getwDate()); pstmt.setInt(2, vo.getSuip()); pstmt.setInt(3, vo.getJichul()); pstmt.setString(4, vo.getBigo()); pstmt.setInt(5, vo.getIdx()); pstmt.executeUpdate(); } catch (SQLException e) { System.out.println("SQL 에러 발생 : "+e.getMessage()); }finally { pstmtClose(); } } public int delete(int idx) { int check = 0; try { sql="select * from bank where idx=? "; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); if(rs.next()) { rs.close(); //stmt 다시 쓰면 에러나기에 rs닫으며 stmt 같이 닫기. sql = "delete from bank where idx=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); check = 1; //삭제했으면 1로 리턴 } } catch (SQLException e) { System.out.println("SQL 에러 발생 : "+e.getMessage()); }finally { pstmtClose(); } return check; } } | cs |
SQL
결과
첫댓글 좋습니다... 시간이 많이 걸리죠?... 조금 연습하면 익숙해 지실겁니다.
깔끔하게 잘 처리하셨네요. 수고하셨어요
아직은 어색한데, 연습하다보면 익숙해질 것 같습니다! 감사합니다