[Java,DB] DML여러 클래스로 실행

Day27-1. 230601

DML을 실행 할 때 여러 클래스로 나누어 작업을 해 주었다.

 

DBMain>

package db;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
//시작클래스
public class DBMain {
 
    public static void main(String[] args) {
        //1. JDBC Driver등록 &2. 연결 Connection얻기
        Connection conn = JDBCUtil.getConnction();
        
        //3. 객체준비&4. 쿼리실행
        //목록조회->입력->목록조회->수정->상세조회->삭제
        
        //입력
        DeptDAO deptDao = new DeptDAO();
        //deptDao.insertDept(conn, "부서1", "대전");
        
        //수정
        //deptDao.updateDept(conn, "서울", "부서2",63 );
        
        //삭제
        //deptDao.deleteDept(conn, 63);
    
        //부서번호 상세조회
        DeptDTO deptDTO = deptDao.getDeptByDeptno(conn, 65);
        
        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("해당 부서가 존재하지 않습니다.");
        }
        
        //부서명 목록조회
        System.out.println();
        System.out.println("--부서명목룍조회 결과--");
        List<String> list= deptDao.getDeptNameList(conn);
        for(int i=0;i<list.size() ;i++) {
            String dname = list.get(i);
            System.out.println(dname);
        }
        
        //모든 부서 목록조회
        System.out.println();
        System.out.println("--모든부서조회 결과--");
        List<DeptDTO> deptList= deptDao.getDeptList(conn);
        if(deptList.size()>0){
            System.out.println("부서번호\t부서명\t\t위치");
            System.out.println("-----------------------------------");
        for(DeptDTO dept : deptList) {
            System.out.println(dept.toString()); //오버라이딩된 toString() 호출
            
            /*    System.out.printf("%5d\t %14s\t %13s\r\n", 
                                                dept.getDeptno(),
                                                dept.getDname(),
                                                dept.getLoc());
            */
            }//for
        }else {
            System.out.println("부서가 존재하지 않습니다.");
        }//if
        
        
        //5. conn자원반납
        JDBCUtil.close(conn);
    }
    
 
}
 

//1. JDBC Driver등록 &2. 연결 Connection얻기
//3. 객체준비&4. 쿼리실행
  //목록조회->입력->목록조회->수정->상세조회->삭제
//입력
//수정
//삭제
//부서번호 상세조회
//부서명 목록조회
//모든 부서 목록조회
//5. conn자원반납

메인 클래스로 호출 해 여러 클래스를 실행 할 수 있다.

 

 

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 getConnction() {
        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();
        }
    }
}

DB연동을 위한 클래스로 JDBC드라이버를 등록하고 연결 connection을 얻는 역할을 하는 클래스이다.

 

 

DeptDTO>

package db;
 
//new DeptDTO(int deptno, String dname, String loc)
public class DeptDTO {
    //field
    private int deptno; //부서번호 number deptno
    private String dname; //부서명 varchar2 dname
    private String loc;//위치 varchar2 loc
 
    //constructor
    public DeptDTO(String dname, String loc) {
        this.dname = dname;
        this.loc = loc;
    }
    
    public DeptDTO(int deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }
    
    //getter&setter
    public int getDeptno() {
        return deptno;
    }
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }
    public String getLoc() {
        return loc;
    }
    public void setLoc(String loc) {
        this.loc = loc;
    }
 
    @Override
    public String toString() {
        return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
    }
    
}

CRUD에 사용될 DTO가 선언된 클래스이다.

 

 

DeptDAO>

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;
        
    }
    
}

CRUD에 사용될 쿼리문이 담긴 DAO클래스로 객체준비, 쿼리실행, 자원반납의 과정을 거친다.