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 | package t4_Sungjuk; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class Sungjuk { Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql =""; public Sungjuk() { 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("DB 연동 실패~~~~~"); } } // DB객체 close public void connClose() { try { conn.close(); } catch (SQLException e) {} } // stmt객체 close public void stmtClose() { if(stmt != null); try { stmt.close(); } catch (SQLException e) {} } public void rsClose() { try { rs.close(); stmtClose(); } catch (SQLException e) {} } //성적자료입력 public void input(String name, int kor, int eng, int mat, int tot, double avg, char grade) { try { stmt = conn.createStatement(); sql = "insert into sungjuk values ('"+name+"',"+kor+","+eng+","+mat+","+tot+","+avg+",'"+grade+"')"; stmt.executeUpdate(sql); System.out.println("성적자료가 입력되었습니다."); } catch (SQLException e) { System.out.println("SQL 에러 :" + e.getMessage()); }finally { stmtClose(); } } // 전체조회처리 public void sunjukList() { try { stmt = conn.createStatement(); sql = "select * from sungjuk order by name"; rs=stmt.executeQuery(sql); System.out.println("\t\t 전 체 성 적 리 스 트"); System.out.println("======================================="); System.out.println("성명\t국어\t영어\t수학\t총점\t평균\t학점"); System.out.println("---------------------------------------"); while(rs.next()) { System.out.print(rs.getString("name")+"\t"); System.out.print(rs.getInt("kor")+"\t"); System.out.print(rs.getInt("eng")+"\t"); System.out.print(rs.getInt("mat")+"\t"); System.out.print(rs.getInt("tot")+"\t"); System.out.print(rs.getInt("avg")+"\t"); System.out.print(rs.getString("grade")); System.out.println(); } } catch (SQLException e) { System.out.println("SQL 에러 :" + e.getMessage()); }finally { stmtClose(); } } //삭제처리 public void delete(String name) { try { stmt = conn.createStatement(); sql = "delete from sungjuk where name ='"+name+"'"; stmt.executeUpdate(sql); } catch (SQLException e) { System.out.println("SQL 에러 : "+e.getMessage()); } finally { stmtClose(); } } //개별조회 public void SearchSunjuk(String name) { try { stmt = conn.createStatement(); sql = "select * from sungjuk where name = '"+name+"'"; rs = stmt.executeQuery(sql); if(rs.next()) { int kor = rs.getInt("kor"); int eng = rs.getInt("eng"); int mat = rs.getInt("mat"); int tot = rs.getInt("tot"); int avg = rs.getInt("avg"); String grade = rs.getString("grade"); System.out.println("==============================="); System.out.println("성 명 : " + name); System.out.println("국어점수 : " + kor); System.out.println("영어점수: " + eng); System.out.println("수학점수 : " + mat); System.out.println("총 점 : " + tot); System.out.println("평 균 : " + avg); System.out.println("학 점 : " + grade); System.out.println("==============================="); } else { System.out.println("자료를 찾지 못했습니다."); } } catch (SQLException e) { System.out.println("SQL오류 : " + e.getMessage()); } } //수정 public void update(String name) { Scanner scanner = new Scanner(System.in); try { SearchSunjuk(name); System.out.println(); int kor=0; int eng=0; int mat=0; int tot=0; double avg; char grade; System.out.println(name+ "자료를 수정합니다."); System.out.print("국어점수를 입력하세요 : "); kor = scanner.nextInt(); System.out.print("영어점수를 입력하세요 : "); eng = scanner.nextInt(); System.out.print("수학점수를 입력하세요 : "); mat = scanner.nextInt(); tot = kor + eng + mat; avg = tot/3.0; if(avg>=90) grade = 'A'; else if (avg>=80) grade = 'B'; else if (avg>=80) grade = 'C'; else if (avg>=80) grade = 'D'; else grade = 'F'; stmt = conn.createStatement(); sql = "update sungjuk set kor='"+kor+"', eng='"+eng+"', mat='"+mat+"',tot='"+tot+"',avg='"+avg+"',grade='"+grade+"' where name='"+name+"'"; stmt.executeUpdate(sql); System.out.println("수정완료 !!!!"); System.out.println(); } catch (SQLException e) { System.out.println("SQL 에러 : "+e.getMessage()); } } } | cs |
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 t4_Sungjuk; import java.util.Scanner; public class SungjukRun { public static void main(String[] args) { Scanner scanner = new Scanner(System.in); Sungjuk sungjuk = new Sungjuk(); int no =0; while(true) { System.out.print("작업 선택? 1.성적입력 2.개별조회 3.전체조회 4.수정 5.삭제 6.종료 "); no = scanner.nextInt(); if(no < 1 || no > 5) break; String name; int kor,eng,mat,tot; double avg; char grade; switch(no) { case 1: //성적입력 System.out.print("성명을 입력하세요?"); name = scanner.next(); System.out.print("국어 점수를 입력하세요"); kor = scanner.nextInt(); System.out.print("영어 점수를 입력하세요"); eng = scanner.nextInt(); System.out.print("수학 점수를 입력하세요"); mat = scanner.nextInt(); // 아래 계산은 서비스객체를 이용하여야 한다 tot = kor +eng +mat; avg = tot/3.0; if( avg>=90) grade ='A'; else if (avg >=80) grade = 'B'; else if (avg >=70) grade = 'C'; else if (avg >=60) grade = 'D'; else grade = 'F'; sungjuk.input(name,kor,eng,mat,tot,avg,grade); break; case 2: // 개별조회 System.out.print("이름을 입력하세요 : "); name = scanner.next(); sungjuk.SearchSunjuk(name); break; case 3: // 전체조회 sungjuk.sunjukList(); break; case 4: // 개별자료 수정 System.out.println("수정할 성명을 입력하세요"); name = scanner.next(); sungjuk.update(name); break; case 5: // 자료삭제 System.out.print("삭제할 성명을 입력하세요? "); name = scanner.next(); sungjuk.delete(name); break; default: break; } } System.out.println("======================================"); System.out.println("작업종료.........."); sungjuk.connClose(); scanner.close(); } } | cs |