DML 서비스 클래스 이용

Day28-1. 230608

기존에 여러 클래스로 실행하던 DML작업에 service클래스가 추가되었다.

메인클래스에서 서비스,DAO를 거쳐 DB와 연동된다.

Main <-> Service <-> DAO <-> DB

 

DBMain2>

package db;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Scanner;
 
//시작클래스
public class DBMain2 {
 
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        DeptService deptservice = new DeptService();
        boolean isRunnig = true;
        
        //입력
    /*    while(isRunnig) {
            System.out.println();
            System.out.println("메뉴 : 1.부서명,지역 입력  0.종료");
            System.out.print("=>메뉴 번호를 입력하세요: ");
            
            try {
                int menuno = sc.nextInt();
                
                switch(menuno) {
                case 1 : //1.부서명,위치 입력
                    System.out.println("1부서명입력");
                    System.out.println();
                    System.out.print("=>부서명를 입력하세요:  ");
                    String deptName = sc.next();
                    System.out.print("=>위치를 입력하세요:  ");
                    String deptLoc = sc.next();
                    deptservice.insertDept(deptName,deptLoc);
                    break;
                }
            }catch(InputMismatchException e) {
                System.out.println("=>올바른 입력 형식이 아닙니다. 정수 숫자 형태로 입력하세요.");
                sc.nextLine();
            
            }
        }sc.close();
    */    
 
    /*    
        //수정
        while(isRunnig) {
            System.out.println();
            System.out.println("메뉴 : 1.수정할 위치,부서명,번호 입력  0.종료");
            System.out.print("=>메뉴 번호를 입력하세요: ");
            
            try {
                int menuno = sc.nextInt();
                
                switch(menuno) {
                case 1 : //1.수정할 부서명,위치 입력
                    System.out.println();
                    
                    System.out.print("=>수정할 부서번호를 입력하세요:  ");
                    int deptN0 = sc.nextInt();
                    System.out.print("=>수정할 부서명를 입력하세요:  ");
                    String deptName = sc.next();
                    System.out.print("=>수정할 위치를 입력하세요:  ");
                    String deptLoc = sc.next();
                    deptservice.updateDept(deptName,deptLoc,deptN0);
                    break;
                }
            }catch(InputMismatchException e) {
                System.out.println("=>올바른 입력 형식이 아닙니다. 정수 숫자 형태로 입력하세요.");
                sc.nextLine();
            
            }
        }sc.close();
        
    */    
        
        //삭제
        while(isRunnig) {
            System.out.println();
            System.out.println("메뉴 : 1.삭제할 위치,부서명,번호 입력  0.종료");
            System.out.print("=>메뉴 번호를 입력하세요: ");
            
            try {
                int menuno = sc.nextInt();
                
                switch(menuno) {
                case 1 : //1.삭제할 부서번호 입력
                    System.out.println();
                    
                    System.out.print("=>삭제할 부서번호를 입력하세요:  ");
                    int deptN0 = sc.nextInt();
                    deptservice.deleteDept(deptN0);
                    break;
                }
            }catch(InputMismatchException e) {
                System.out.println("=>올바른 입력 형식이 아닙니다. 정수 숫자 형태로 입력하세요.");
                sc.nextLine();
            
            }
        }sc.close();
        
        
        //조회
        System.out.println("--<<부서관리시스템>>--");
        while(isRunnig) {
            System.out.println();
            System.out.println("메뉴 : 1.모든부서조회  2.부서명목록조회  3.부서상세조회 0.종료");
            System.out.print("=>메뉴 번호를 입력하세요: ");
            
            try {
                int     menuno = sc.nextInt();
                
                switch(menuno) {
                    case 1 : //모든부서조회
                                System.out.println("1모든부서조회");
                                System.out.println();
                                System.out.println("--모든부서조회 결과--");
                                List<DeptDTO> deptList= deptservice.getDeptList();
                                if(deptList.size()>0){
                                    System.out.println("부서번호\t부서명\t\t위치");
                                    System.out.println("-----------------------------------");
                                for(DeptDTO dept : deptList) {
                                    System.out.println(dept.toString()); //오버라이딩된 toString() 호출
                                    }//for
                            }else {
                                System.out.println("부서가 존재하지 않습니다.");
                            }//if
                                    break;
                                
                                
                    case 2: //부서명목록조회
                                System.out.println("2부서명목록조회");
                                System.out.println();
                                System.out.println("--부서명목룍조회 결과--");
                                List<String> list= deptservice.getDeptNameList();
                                for(int i=0;i<list.size() ;i++) {
                                    String dname = list.get(i);
                                    System.out.println(dname);
                                }
                                break;
                                
                    case 3: //부서상세조회
                                System.out.print("=>조회할 부서번호를 입력하세요:  ");
                                int deptNo = sc.nextInt();
                                //Service클래스의 getDeptByDeptno메서드호출
                                DeptDTO deptDTO= deptservice.getDeptByDeptno(deptNo);
                                if(deptDTO!=null){
                                    System.out.println("부서번호\t부서명\t\t위치");
                                    System.out.println("-----------------------------------");
                                    System.out.printf("%5d\t %14s\t %13s\r\n", 
                                                                        deptDTO.getDeptno(),
                                                                        deptDTO.getDname(),
                                                                        deptDTO.getLoc());
                                }else {
                                    System.out.println("해당 부서가 존재하지 않습니다.");
                                }
                                break;
                    case 0: System.out.println("종료합니다.");//종료
                                isRunnig = false; break;
                    default : System.out.println("=>유효하지않은 메뉴번호입니다.");
                                break;
                }//switch
                
            }catch(InputMismatchException e) {
                System.out.println("=>올바른 입력 형식이 아닙니다. 정수 숫자 형태로 입력하세요.");
                sc.nextLine();
            }
            
        }//while
        sc.close();
        
    }    
}

 

이런식으로 선택해서 결과를 볼 수 있음(위 콘솔창은 DB연동 전 콘솔창)

메뉴 번호 형식이 올바르지 않으면 예외처리 하도록 함.

 

 

DeptService>

package db;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
 
//Main <-> DAO <-> DB(원래)
//JDBCUtil : 공동모듈
//DTO
//Main <-> Service <-> DAO <-> DB
public class DeptService {
    //field
    DeptDAO deptDao = new DeptDAO();
     Object insertDept;
    
    //constructor
    //method
    //부서번호 상세조회
    public DeptDTO getDeptByDeptno(int dno){
        Connection conn = JDBCUtil.getConnection();    
        DeptDTO deptDTO= deptDao.getDeptByDeptno(conn, dno);
        return deptDTO;
    }
    
    //부서명 목록조회
    public  List<String> getDeptNameList(){
        Connection conn = JDBCUtil.getConnection();
         List<String> dnameList = deptDao.getDeptNameList(conn);
        return dnameList;
    }
    
    //모든 부서 목록조회
    public List<DeptDTO> getDeptList(){
        Connection conn = JDBCUtil.getConnection();
        //List<DeptDTO> deptDTOList = deptDao.getDeptList(conn);
        //return deptDTOList;
        
        return deptDao.getDeptList(conn); //위 두줄을 아래처럼 바꿔도 됨.
    }
        
        
    //입력
    public void insertDept(String dname, String loc) {
        Connection conn = JDBCUtil.getConnection();
        deptDao.insertDept(conn, dname, loc);
    }
    
    //수정
    public void updateDept(String location,String name, int dno) {
        Connection conn = JDBCUtil.getConnection();
        deptDao.updateDept(conn, location, name, dno);
    }
    
    //삭제
    public void deleteDept(int dno) {
        Connection conn = JDBCUtil.getConnection();
        deptDao.deleteDept(conn, dno);
    }    
        
}

package db;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
//DAO : (Data Access Object) : DB에 접근(Access)하여 쿼리실행 등을 하는 클래스
public class DeptDAO {
    //field
    //constructor
    //method
    
    //입력하기     insertDept("개발부97",우도);
    public void insertDept(Connection conn,String dname, String loc) {
        PreparedStatement stmt = null;
        String sql = "insert into dept(deptno,dname,loc) "
                + "values(seq_dno.NEXTVAL,?,?)";
        
        //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            System.out.println();
            e1.printStackTrace();
        }
        
        //4.쿼리실행
        try {
            stmt.setString(1,dname);
            stmt.setString(2,loc);
            int cnt = stmt.executeUpdate();
            if(cnt>0) {
                System.out.println("쿼리실행성공="+cnt);
            }
        } catch (SQLException e1) {
            System.out.println("executeUpdate()실행관련 에러발생");
            e1.printStackTrace();
        }finally {
        //5.자원반납
        JDBCUtil.close(stmt);
        }
        }//입력하기
    
    
    //수정하기
    public void updateDept(Connection conn,String location,String name, int dno) {
        PreparedStatement stmt = null;
        String sql = "update dept " + 
                            "set loc=?,dname=? " + 
                            " where deptno=?";
        //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        
        //4.쿼리실행
        try {
            stmt.setString(1,location);
            stmt.setString(2,name);
            stmt.setInt(3,dno);
            int cnt = stmt.executeUpdate();
            if(cnt>0) {
                System.out.println("쿼리실행성공="+cnt);
            }
        } catch (SQLException e1) {
            System.out.println("executeUpdate()실행관련 에러발생");
            e1.printStackTrace();
        }
        
        //5.자원반납
        try {
            if(stmt!=null) {stmt.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("5.자원반납");
    }
    
    //삭제하기
    public void deleteDept(Connection conn,int dno) {
        PreparedStatement stmt = null;
        String sql = " delete from dept " + 
                            "where deptno=?";
        //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        
        //4.쿼리실행
        try {
            stmt.setInt(1,dno);
            int cnt = stmt.executeUpdate();
            if(cnt>0) {
                System.out.println("쿼리실행성공="+cnt);
            }
        } catch (SQLException e1) {
            System.out.println("executeUpdate()실행관련 에러발생");
            e1.printStackTrace();
        }finally {
            //5.자원반납
            JDBCUtil.close(stmt);
            }
        
    }
 
    //상세조회
    //부서번호에 따른상세조회
    /*파라미터
     * Connection conn : 커넥션객체
     * int dno : 조회하고 싶은 부서번호
     * 리턴유형
     DeptDTO : 부서정보
     */
    public DeptDTO getDeptByDeptno(Connection conn,int dno) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DeptDTO deptDTO = null; //select결과를 row(=record=행)단위로 저장하기위한 객체선언 및 초기화
        String sql = "select deptno as dno, dname as name, loc as location "
                            +"from dept "
                            +"where deptno=?";
        //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            System.out.println("객체관련 에러발생="+e1);
            e1.printStackTrace();
        }
        
        //4.쿼리실행
        try {
            stmt.setInt(1, dno);
            rs = stmt.executeQuery();
            
            //select결과가 존재하면
                if(rs.next()) {
                    //컬럼에 접근에 값을 가져와 변수에 저장
                    int d_no = rs.getInt("dno");
                    String d_name = rs.getString("name");
                    String loc = rs.getString("location");
                    //저장된 변수의 값을 DeptDTO객체로 생성
                    deptDTO = new DeptDTO(d_no,d_name,loc);
                }
            
            
        } catch (SQLException e1) {
            System.out.println("executeUpdate()실행관련 에러발생");
            e1.printStackTrace();
        }finally {
            //5.자원반납
            JDBCUtil.close(rs);
            JDBCUtil.close(stmt);
        }
 
        return deptDTO;
    }//getDeptByDeptno()상세조회
 
    //부서명 목록조회
    public List<String> getDeptNameList(Connection conn) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<String>(); //부서명목록을 저장할  변수선언 및 초기화
        String sql = "select dname " +
                             "from dept " +
                             "order by dname asc";
    
    //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            System.out.println("객체관련 에러발생="+e1);
            e1.printStackTrace();
        }
        
        //4.쿼리실행
                try {
                    rs = stmt.executeQuery();
                    //select결과가 존재하는 동안
                        while(rs.next()) {
                            //컬럼에 접근하여 값을 가져와 목록에  추가
                            list.add( rs.getString("dname"));
                        }
                    
                } catch (SQLException e1) {
                    System.out.println("executeUpdate()실행관련 에러발생");
                    e1.printStackTrace();
                }finally {
                    //5.자원반납
                    JDBCUtil.close(rs);
                    JDBCUtil.close(stmt);
                }
                
                return list;
    }
    
    //모든 부서 목록조회
    public List<DeptDTO> getDeptList(Connection conn) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<DeptDTO> list = new ArrayList<DeptDTO>(); //부서명목록을 저장할  변수선언 및 초기화
        String sql = "select deptno, dname, loc "+
                             "from dept "+
                             "order by deptno asc";
    
    //3.객체준비
        try {
            stmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            System.out.println("객체관련 에러발생="+e1);
            e1.printStackTrace();
        }
        
        //4.쿼리실행
                try {
                    rs = stmt.executeQuery();
                    
                    /*select결과가 있으면
                    컬럼에 접근하여 값을 가져와 -> DeptDTO객체를 생성하여 -> 목록에 추가*/
                        while(rs.next()) {
                            //컬럼에 접근하여 값을 가져와 변수에 저장
                            int deptno = rs.getInt("deptno");
                            String dname = rs.getString("dname");
                            String loc = rs.getString("loc");
                            
                            //변수에 저장된 값을 이용하여 DeptDTO객체를 생성
                            DeptDTO deptDTO = new DeptDTO(deptno, dname, loc);
                            
                            //생성된 DeptDTO객체를 목록에 추가
                            list.add( deptDTO );
                        }
                    
                } catch (SQLException e1) {
                    System.out.println("executeUpdate()실행관련 에러발생");
                    e1.printStackTrace();
                }finally {
                    //5.자원반납
                    JDBCUtil.close(rs);
                    JDBCUtil.close(stmt);
                }
                
                
                return list;
        
    }
    
}

 

 

JDBCUtil>

package db;
 
//DB연동을 위한 클래스
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCUtil {
    //field
    static String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
    static String user = "scott";
    static String password = "tiger";
    
    //constructor
    
    //method
    public static Connection getConnection() {
        Connection conn = null;
        //1. JDBC Driver등록
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            //System.out.println("1.JDBC Driver등록");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //2. 연결 Connection얻기
        try {
            conn = DriverManager.getConnection(url, user, password);
            //System.out.println("2.연결 Connection얻기-성공");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
        
    }//getDBConnction()
    
    
    //커넥션반환
    public static void close(Connection conn) {
        try {
            if(conn!=null) {conn.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Connection자원반납");
    }
    
    //PreparedStatement반환
    public static void close(PreparedStatement stmt) {
        try {
            if(stmt!=null) {stmt.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    //Statement반환
    public static void close(Statement stmt) {
        try {
            if(stmt!=null) {stmt.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    //ResultSet반환
    public static void close(ResultSet rs) {
        try {
            if(rs!=null) {rs.close();}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}