[21.08.19]Ch.10-1 성적관리 프로그램 만들기2 - DB연동(Main, DAO, ServiceDB)

////////SungJukMain

 

package collection;                                

 

public class SungJukMain {

 

         public static void main(String[ ] args) {

                  //DB처리

                  SungJukServiceDB serviceDB = new SungJukServiceDB( );

                  serviceDB.menu();

                 

                  System.out.println("프로그램을 종료합니다");

         }

}

 

/*

 * 테이블명 : sungjuk

 * number ....unique, -- 중복허용X

 * name varchar2(20) not null, -- 반드시 이름을 입력

 * kor number,

 * eng number,

 * mat number,

 * tot number,

 * avg number(5, 2) ....ex) 99.99

 */

 

////////SungJukServiceDB

 

package collection;

 

import java.util.ArrayList;

import java.util.List;

import java.util.Scanner;

 

public class SungJukServiceDB {

         Scanner scan = new Scanner(System.in);

         private List<SungJukDTO> list;

 

         public SungJukServiceDB( ) {

                  list = new ArrayList<SungJukDTO>( ); // 부모 = 자식

         }

 

         public void menu( ) {

                  int num;

                  while (true) {

                           System.out.println();

                           System.out.println("****************");

                           System.out.println("   1. 입력");

                           System.out.println("   2. 출력");

                           System.out.println("   3. 검색");

                           System.out.println("   4. 삭제");

                           System.out.println("   5. 정렬");

                           System.out.println("   6. 끝");

                           System.out.println("****************");

                           System.out.print(" 번호 : ");

                           num = scan.nextInt();

 

                           if (num == 6)

                                   break;

                           if (num == 1)

                                   insertArticle();

                           else if (num == 2)

                                   printArticle();

                           else if (num == 3)

                                   searchArticle(); // 번호 검색

                           else if (num == 4)

                                   deleteArticle(); // 번호 검색 후 삭제

                           else if (num == 5)

                                   sortArticle();

                           else

                                   System.out.println("1~6중에 선택하세요");

                  } // while

         }// menu()

 

         private void sortArticle( ) {

                  System.out.println();

                  int num;

                  SungJukDAO dao = new SungJukDAO( );

 

                  while (true) {

                           System.out.println( );

                           System.out.println("*********************");

                           System.out.println("1. 이름으로 오름차순");

                           System.out.println("2. 총점으로 내림차순");

                           System.out.println("3. 이전메뉴");

                           System.out.println("*********************");

                           System.out.print("번호 입력 : ");

                           num = scan.nextInt( );

                           if (num == 3)

                                   break;

 

//                if (num == 1) {

//                         List<SungJukDTO> list = dao.sortName();

//                } else if (num == 2) {

//                         List<SungJukDTO> list = dao.sortTot();

//                }

                 

// 한번에 해결하고 싶은데 어떤 번호가 눌렸는지 모르니 num을 가져가라                         

                           List<SungJukDTO> list = dao.sortArticle(num);

                          

                           System.out.println("번호\t이름\t국어\t영어\t수학\t총점\t평균");

                           for (SungJukDTO dto : list) {

                                   System.out.println(dto);

                           }

                  } // while

         }// sortArticle

 

         private void deleteArticle( ) { // 삭제를 할 때는 번호로 삭제하는 것이 낫다

                  System.out.println( );

                  System.out.print("삭제 할 번호 입력 : ");

                  int number = scan.nextInt( );

 

                  // DB

                  SungJukDAO dao = new SungJukDAO( );

                  int su = dao.deleteArticle(number);

 

                  if (su == 0)

                           System.out.println("찾고자 하는 번호가 없습니다.");

                  else

                           System.out.println("삭제완료");

         } // deleteArticle

 

         private void searchArticle( ) {

                  System.out.println( );

                  System.out.print("검색 할 번호 입력 : ");

                  int number = scan.nextInt( );

 

                  // DB

                  SungJukDAO dao = new SungJukDAO( );

                  SungJukDTO dto = dao.searchArticle(number);

                  // 가져올 줄이 여러 줄이면 list에 담아오는데, 여기선 한줄만 가져오면 되니 dto에 담아오는 거

 

                  if (dto == null) {

                           System.out.println("찾고자 하는 번호가 없습니다");

                  } else {

                           System.out.println("번호\t이름\t국어\t영어\t수학\t총점\t평균");

                           System.out.println(dto);

                  }

         } // searchArticle                                                                             

 

         private void printArticle( ) {

                  System.out.println( );

                  // DB

                  SungJukDAO dao = new SungJukDAO( );

                  List<SungJukDTO> list = dao.printArticle( );

 

                  System.out.println("번호\t이름\t국어\t영어\t수학\t총점\t평균");

                  for (SungJukDTO dto : list) {

                           System.out.println(dto);

                  }

         } // printArticle()

 

         private void insertArticle( ) {

                  // 데이터

                  System.out.println( );

                  System.out.print("번호 입력 : ");

                  int number = scan.nextInt( );

                  System.out.print("이름 입력 : ");

                  String name = scan.next( );

                  System.out.print("국어 입력 : ");

                  int kor = scan.nextInt( );

                  System.out.print("영어 입력 : ");

                  int eng = scan.nextInt();

                  System.out.print("수학 입력 : ");

                  int math = scan.nextInt( );

 

                  // 데이터를 생성자 또는 setter 저장

                  SungJukDTO dto = new SungJukDTO( );

                  dto.setNumber(number);

                  dto.setName(name);

                  dto.setKor(kor);

                  dto.setEng(eng);

                  dto.setMath(math);

 

                  dto.calc( );

 

                  // DB

                  SungJukDAO dao = new SungJukDAO( );

                  dao.insertArticle(dto);

 

                  System.out.println("등록 완료");

         }// insertArticle()

}

 

////////SungJukDAO

 

package collection;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

 

public class SungJukDAO {

         private Connection conn;

         private PreparedStatement pstmt;

         private ResultSet rs; // select한 결과물은 항상 resultset에 보관된다

 

         private String driver = "oracle.jdbc.driver.OracleDriver";

         private String url = "jdbc:oracle:thin:@localhost:1521:xe";

         private String username = "c##java";

         private String password = "bit";

 

         public SungJukDAO( ) { // 드라이버 로딩

                  try {

                           Class.forName(driver);

                  } catch (ClassNotFoundException e) {

                           e.printStackTrace( );

                  }

         }

 

         public void getConnection( ) { // 접속

                  try {

                           conn = DriverManager.getConnection(url, username, password);

                  } catch (SQLException e) {

                           e.printStackTrace( );

                  }

         }

 

         public void insertArticle(SungJukDTO dto) {

                  String sql = "insert into sungjuk values(?, ?, ?, ?, ?, ?, ?)";

                  getConnection( );

 

                  try {

                           pstmt = conn.prepareStatement(sql);

                           pstmt.setInt(1, dto.getNumber( ));

                           pstmt.setString(2, dto.getName( ));

                           pstmt.setInt(3, dto.getKor( ));

                           pstmt.setInt(4, dto.getEng( ));

                           pstmt.setInt(5, dto.getMath( ));

                           pstmt.setInt(6, dto.getTot( ));

                           pstmt.setDouble(7, dto.getAvg( ));

 

                           pstmt.executeUpdate( );// 실행

                  } catch (SQLException e) {

                           e.printStackTrace();

                  } finally {

                           try {

                                   if (pstmt != null)

                                            pstmt.close( );

                                   if (conn != null)

                                            conn.close( );

                           } catch (SQLException e) {

                                   e.printStackTrace( );

                           }

                  }

         }// insertArticle(SungJukDTO dto)

 

         public List<SungJukDTO> printArticle( ) {

                  List<SungJukDTO> list = new ArrayList<SungJukDTO>( );

                  String sql = "select * from sungjuk";

                  getConnection( );

                  try {

                           pstmt = conn.prepareStatement(sql);

                           rs = pstmt.executeQuery( );

 

                           while (rs.next( )) {

                                   SungJukDTO dto = new SungJukDTO( );

                                   dto.setNumber(rs.getInt("number"));

                                   // rs.getInt에서 number를 꺼내와 dto.setNumber에게 넘겨주기

                                   dto.setName(rs.getString("name"));

                                   dto.setKor(rs.getInt("kor"));

                                   dto.setEng(rs.getInt("eng"));

                                   dto.setMath(rs.getInt("math"));

                                   dto.setTot(rs.getInt("tot"));

                                   dto.setAvg(rs.getDouble("avg"));

 

                                   list.add(dto);

                           } // while

 

                  } catch (SQLException e) {

                           e.printStackTrace( );

                  } finally {

                           try {

                                   if (rs != null)

                                            rs.close( );

                                   if (pstmt != null)

                                            pstmt.close( );

                                   if (conn != null)

                                            conn.close( );

 

                           } catch (SQLException e) {

                                   e.printStackTrace( );

                           }

                  }

                  return list;

         } 

 

         public SungJukDTO searchArticle(int number) {

                  SungJukDTO dto = null;

                  String sql = "select * from sungjuk where number=?";

 

                  getConnection( );

                  try {

                           pstmt = conn.prepareStatement(sql);

                           pstmt.setInt(1, number);

 

                           rs = pstmt.executeQuery( );

                           if (rs.next()) {

                                   dto = new SungJukDTO( );

                                   dto.setNumber(rs.getInt("number"));

                                   dto.setName(rs.getString("name"));

                                   dto.setKor(rs.getInt("kor"));

                                   dto.setEng(rs.getInt("eng"));

                                   dto.setMath(rs.getInt("math"));

                                   dto.setTot(rs.getInt("tot"));

                                   dto.setAvg(rs.getDouble("avg"));

                           }

                  } catch (SQLException e) {

                           e.printStackTrace();

 

                  } finally {

                           try {

                                   if (rs != null)

                                            rs.close();

                                   if (pstmt != null)

                                            pstmt.close();

                                   if (conn != null)

                                            conn.close();

 

                           } catch (SQLException e) {

                                   e.printStackTrace();

                           }

                  }

                  return dto;

         }

 

         public int deleteArticle(int number) {

                  // int가 아닌 void로 해놓으면 return값이 없다는 얘기

                  String sql = "delete sungjuk where number=?";

                  getConnection( );

                  int su = 0;

 

                  try {

                           pstmt = conn.prepareStatement(sql);

                           pstmt.setInt(1, number);

                           su = pstmt.executeUpdate();

 

                  } catch (SQLException e) {

                           e.printStackTrace( );

                  } finally {

                           try {

                                   if (pstmt != null)

                                            pstmt.close();

                                   if (conn != null)

                                            conn.close();

 

                           } catch (SQLException e) {

                                   e.printStackTrace();

                           }

                  }

                  return su;

         }

 

         public List<SungJukDTO> sortArticle(int num) {

                  List<SungJukDTO> list = new ArrayList<SungJukDTO>( );

                  String sql = null;

 

                  if (num == 1)

                           sql = "select * from sungjuk order by name asc";

                  else

                           sql = "select * from sungjuk order by tot desc";

 

                  getConnection( );

 

                  try {

                           pstmt = conn.prepareStatement(sql);

                           rs = pstmt.executeQuery( ); // 실행

 

                           while (rs.next( )) {

                                   SungJukDTO dto = new SungJukDTO( );

                                   dto.setNumber(rs.getInt("number"));

                                   dto.setName(rs.getString("name"));

                                   dto.setKor(rs.getInt("kor"));

                                   dto.setEng(rs.getInt("eng"));

                                   dto.setMath(rs.getInt("math"));

                                   dto.setTot(rs.getInt("tot"));

                                   dto.setAvg(rs.getDouble("avg"));

 

                                   list.add(dto);

                           }

                  } catch (SQLException e) {

                           e.printStackTrace( );

                           list = null; // try catch에서 error가 날 경우, list를 그냥 보낼 수 없으니

                           // 더이상 진행하지 못하게 list를 초기화 시켜버려야 한다

                  } finally {

                           try {

                                   if (rs != null)

                                            rs.close( );

                                   if (pstmt != null)

                                            pstmt.close( );

                                   if (conn != null)

                                            conn.close( );

 

                           } catch (SQLException e) {

                                   e.printStackTrace( );

                           }

                           return list;

                  }

         }

}