10.12 데이터베이스 연동 과제(수정1)

[천해찬]

1. sungjuk 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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

package homework;   import java.util.Scanner;   public class SungjukRun {     public static void main(String[] args) {         Scanner sc = new Scanner(System.in);                  //생성과 동시에 DB연결         Sungjuk sungjuk = new Sungjuk();                  int no = 0;                  while(true) {             System.out.print("작업 선택? 1: 성적입력 2:개별 조회 3:전체조회 4:수정 5:삭제 6:종료 ");         no = sc.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 = sc.next();                 System.out.print("국어점수를 입력하세요 ");                 kor = sc.nextInt();               System.out.print("영어점수를 입력하세요 ");                 eng = sc.nextInt();                 System.out.print("수학을 입력하세요 ");                 mat = sc.nextInt();                  // 아래 계산은 서비스 객체를 이용하는것이 좋다 이곳에서는 DB객체에서 계산처리했다.                                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';               sungjuk.input(name,kor,eng,mat,tot,avg,grade);               break;                             case 2:                 System.out.print("조회할 성명을 입력하세요 ");                 name = sc.next();                 sungjuk.searchName(name);                 break;                              case 3:                 sungjuk.sunjukList();                 break;                              case 4:  //개별자료 수정                 System.out.print("수정하고 싶은 사람의 성명을 입력하세요 ");                 name = sc.next();                 sungjuk.FixList(name);                 break;                              default:                 System.out.print("삭제하고싶은 사람의 성명을 입력하세요 ");                 name = sc.next();                 sungjuk.deleteList(name);                 break;         }     }     System.out.println("====================================");     System.out.println("작업종료.....");     sc.close();  } } Colored by Color Scripter

cs

2. sungjuk

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

package homework;   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) {}         }     }     //rs객체 close     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("성적자료가 입력되었습니다");             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.getDouble("avg")+ "\t");                 System.out.print(rs.getString("grade"));                 System.out.println();             }             System.out.println("==================================================");           System.out.println();         } catch (SQLException e) {             System.out.println("SQL 에러 : " + e.getMessage());         } finally {             stmtClose();         }     }       public void searchName(String name) {         try {             stmt = conn.createStatement();             sql = "select * from sungjuk where name = '"+name+"'";             rs = stmt.executeQuery(sql);             if(rs.next()) {                 System.out.println("성적을 출력합니다");                 int kor = rs.getInt("kor");                 int eng = rs.getInt("eng");                 int mat = rs.getInt("mat");                 int tot = rs.getInt("tot");                 double avg = rs.getDouble("avg");                 String grade = rs.getString("grade");                 System.out.println("==================================================");                 System.out.println(" 성명\t국어\t영어\t수학\t총점\t평균\t학점");                 System.out.println("--------------------------------------------------");                 System.out.print(name + "\t");                 System.out.print(kor + "\t");                 System.out.print(eng + "\t");                 System.out.print(mat+ "\t" );                 System.out.print(tot + "\t");                 System.out.printf(avg+ "\t");                 System.out.println(grade);                 System.out.println("==================================================");               System.out.println();               }             else {                 System.out.println("자료를 찾지 못했습니다.");               System.out.println();             }         } catch (SQLException e) {             System.out.println("SQL 오류: " + e.getMessage());           }     }          public void deleteList(String name) {         try {             stmt = conn.createStatement();             sql = "delete from sungjuk where name='"+name+"'";             stmt.executeUpdate(sql);             System.out.println("삭제가 완료되었습니다!");           System.out.println();         } catch (SQLException e) {             System.out.println("SQL 에러" + e.getMessage());         } finally {             stmtClose();         }     }       public void FixList(String name) {         Scanner sc = new Scanner(System.in);                  try {             searchName(name);             System.out.println();             int kor = 0;             int eng = 0;             int mat = 0;             int tot =0;             int tot2 = 0;             System.out.println(name + "자료를 수정합니다.");             System.out.print("국어 성적을 입력하세요"); kor = sc.nextInt();             System.out.print("영어 성적을 입력하세요"); eng = sc.nextInt();             System.out.print("수학 성적을 입력하세요"); mat = sc.nextInt();                                      double avg;             double avg2;             char grade;             char grade2;             tot2 = kor + eng + mat;             avg2 = tot2/3.0;             if(avg2>=90) grade2 = 'A';             else if (avg2>=80) grade2 = 'B';             else if (avg2>=80) grade2 = 'C';             else if (avg2>=80) grade2 = 'D';             else grade2 = 'F';                          stmt = conn.createStatement();             sql = "update sungjuk set kor='"+kor+"', eng='"+eng+"', mat='"+mat+"',tot='"+tot2+"',avg='"+avg2+"',grade='"+grade2+"' where name='"+name+"'";             stmt.executeUpdate(sql);             System.out.println("수정완료!!!!!!!!!!!!!!!");             System.out.println();                      } catch (SQLException e) {             System.out.println("SQL 에러 : " + e.getMessage());         }     } }   Colored by Color Scripter

cs

compute 메소드를 만들지 않고 바로 수정메소드에서 값을 바꾸는 걸로 연습해봤는데 compute 메소드도 활용해서 추가해보겠습니다!!

========================================================

수정한것 

1. sungjuk

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 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235

package homework;   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 Sungjuk2 {     Connection conn = null;     Statement stmt = null;     ResultSet rs = null;          String sql = "";          public Sungjuk2() { //생성함과 동시에 데이터베이스 연동함.         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() { //Connection 닫기         try {             conn.close();         } catch (SQLException e) {}     }          public void stmtClose() { //Statement 닫기         if(stmt != null) {             try {                 stmt.close();             } catch (SQLException e) {}         }     }          public void rsClose() { //ResultSet 닫기         if(rs != null) {             try {                 rs.close();                 stmt.close();             } catch (SQLException e) {}         }     }          //입력     public void input(String name, int kor, int eng, int mat, int tot, double avg, String grade) {         try {             stmt = conn.createStatement();             sql = "insert into sungjuk values ('"+name+"',"+kor+","+eng+","+mat+","+tot+","+avg+",'"+grade+"')";             stmt.executeUpdate(sql);         } catch (SQLException e) {             System.out.println("SQL 에러 발생 : "+e.getMessage());         } finally {             stmtClose();         }     }       //개별조회     public boolean searchName(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");                 double avg = rs.getDouble("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.printf("평균 : %.2f\n",avg);                 System.out.println("학점 : "+grade);                 System.out.println("==========================================");                 return true;             }             else {                 System.out.println("자료를 찾지 못했습니다.");                 return false;             }         } catch (SQLException e) {             System.out.println("SQL 에러 발생 : "+e.getMessage());         }         return false;     }     // 전체 내역 조회     public void listName() {         try {             stmt = conn.createStatement();             sql = "select * from sungjuk";             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.printf("%.2f\t",rs.getDouble("avg"));                 System.out.print(rs.getString("grade")+"\t");                 System.out.println();             }             System.out.println("======================================================");         } catch (SQLException e) {             System.out.println("SQL 에러 발생 : "+e.getMessage());         }     }       // 전체 정렬 내역 조회     public void sortlistName() {         try {             stmt = conn.createStatement();             sql = "select * from sungjuk order by tot desc";             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.printf("%.2f\t",rs.getDouble("avg"));                 System.out.print(rs.getString("grade")+"\t");                 System.out.println();             }             System.out.println("======================================================");         } catch (SQLException e) {             System.out.println("SQL 에러 발생 : "+e.getMessage());         }     }       @SuppressWarnings("resource")         public void editName(String name) {       Scanner scanner = new Scanner(System.in);       if(!searchName(name)) return;              try {           int kor=0, eng=0, mat=0;         // 검색된 자료가 있다면 기존에 값들을 DB에서 가져와야한다.         stmt = conn.createStatement();         sql = "select * from sungjuk where name = '"+name+"'";         rs = stmt.executeQuery(sql);         rs.next();                  kor = rs.getInt("kor");         eng = rs.getInt("eng");         mat = rs.getInt("mat");                  System.out.println("\n"+name+"님의 자료를 수정합니다.");           int sw=0;                                  while(sw == 0) {                     System.out.print("=> 수정할 항목은? 1:국어  2:영어  3:수학 4.종료시 ");                     int no = scanner.nextInt();                     if(no < 1 || no > 3) sw = 1;                                          switch(no) {                         case 1:                             System.out.print("수정할 국어 점수를 입력하세요 : "); kor = scanner.nextInt();                             break;                         case 2:                             System.out.print("수정할 영어 점수를 입력하세요 : "); eng = scanner.nextInt();                             break;                         case 3:                             System.out.print("수정할 수학 점수를 입력하세요 : "); mat = scanner.nextInt();                             break;                     }                     compute(name,kor,eng,mat,"edit");                 }             } catch (SQLException e) {                 System.out.println("SQL오류 : " + e.getMessage());             }     } //                    sql = "update sungjuk set kor = "+kor+" where name = '"+name+"'";              public void deleteName(String name) {         try {             stmt = conn.createStatement();             sql = "delete from sungjuk where name='"+name+"'";             stmt.executeUpdate(sql);             System.out.println(name+"님 삭제가 완료 되었습니다.");         } catch (SQLException e) {             System.out.println("SQL 에러 발생 : "+e.getMessage());         }                       }       public void compute(String name, int kor, int eng, int mat, String check) {         int tot = kor + eng + mat;         double avg = tot / 3.0;         String grade;         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";         if(check.equals("input")) input(name,kor,eng,mat,tot,avg,grade);         else if(check.equals("edit")) updateName(name,kor,eng,mat,tot,avg,grade);              }       private void updateName(String name, int kor, int eng, int mat, int tot, double avg, String grade) {         try {             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());         }  } } Colored by Color Scripter

cs

2. sungjukRun

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

package homework;   import java.util.Scanner;   public class SungjukRun2 {     public static void main(String[] args) {         Scanner scanner = new Scanner(System.in);         int input = 0;         Sungjuk2 sungjuk = new Sungjuk2();                  while(true) {             System.out.println("\t\t 작 업 선 택");             System.out.print("1.자료입력  2.개별조회  3.전체조회  4.정렬조회  5.자료수정  6.자료삭제  7.종료 : ");             input = scanner.nextInt();             if(input < 1 || input > 6) {                 System.out.println("프로그램을 종료합니다.");                 break;             }             String name="";             int kor=0, eng=0, mat=0, tot=0;             double avg=0.0;             char grade=' ';             switch(input) {                 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();                     sungjuk.compute(name,kor,eng,mat,"input");                     break;                 case 2: // 개별 조회                     System.out.print("자료를 조회 할 성명을 입력하세요 : ");                     name = scanner.next();                     sungjuk.searchName(name);                     break;                 case 3: // 전체 조회                     sungjuk.listName();                     break;                 case 4: // 전체 정렬 내역 조회                     sungjuk.sortlistName();                     break;                 case 5: // 자료 수정                     System.out.print("자료를 수정 할 성명을 입력하세요 : ");                     name = scanner.next();                     sungjuk.editName(name);                     break;                 case 6: // 자료 삭제                     System.out.print("자료를 삭제 할 성명을 입력하세요 : ");                     name = scanner.next();                     sungjuk.deleteName(name);                     break;             }         }         scanner.close();     } }   Colored by Color Scripter

cs

[행복]

앞으로 service객체를 배우게되면 그곳에서 계산과 같은 처리를 하면 됩니다. 수고하셨습니다.