JDBC 프로그래밍

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 JDBCOracleExample2 {
 
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int retrnRow = 0;
 
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
        String id = "SCOTT";
        String pw = "1234";
 
        // 4. 요청할 sql문- select문
        int empno = 7935;
        int mgr = 7369;
        String seleSql1 = "select * from emp order by empno";
        String seleSql2 = "select * from emp where empno = ?";
        String inserSql = "insert into emp values (?,?,?,?,?,?,?,?)";
        String updaSql = "update emp set mgr = ? where empno = ?";
        String deleSql = "delete emp where empno =?";
        
 
        try {
            // 1. 데이터베이스의 드라이버 로드
            Class.forName("oracle.jdbc.driver.OracleDriver");
 
            // 2. 데이터베이스 연결
            conn = DriverManager.getConnection(jdbcUrl, id, pw);
 
            System.out.println("Oracle 데이터 베이스에 연결되었습니다.");
            System.out.println("--------------------------------------------");
 
            stmt = conn.createStatement();
            rs = stmt.executeQuery(seleSql1);
 
            System.out.println("[직원리스트]");
            while (rs.next()) {
                System.out.println("-------------------------");
                System.out.println("사원번호: " + rs.getInt(1));
                System.out.println("사원명: " + rs.getString(2));
                System.out.println("직업 :" + rs.getString(3));
                System.out.println("상사명 :" + rs.getInt(4));
                System.out.println("입사일 :" + rs.getString(5));
                System.out.println("급여 :" + rs.getInt(6));
                System.out.println("커미션 :" + rs.getInt(7));
                System.out.println("부서번호 :" + rs.getString(8));
            }
 
            System.out.println("-------------------------");
 
            // #####################[  insert  ]##################
            System.out.println("[직원정보 추가하기]");
            String sql2 = "insert into emp values (?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql2);
 
            pstmt.setInt(1, 7935);
            pstmt.setString(2, "MINJEONG");
            pstmt.setString(3, "STUDENT");
            pstmt.setInt(4, 7934);
            pstmt.setString(5, "18/07/13");
            pstmt.setInt(6, 700);
            pstmt.setInt(7, 100);
            pstmt.setInt(8, 40);
 
            pstmt.executeUpdate();
 
            // ####################[  추가된 사원 정보 출력하기  ] #########################
            pstmt = conn.prepareStatement(seleSql2);
            pstmt.setInt(1, empno);
            // select의 결과를 resultSet 객체에 담는다.
 
            rs = pstmt.executeQuery();
 
            System.out.println("[추가된 직원 정보 출력하기]");
 
            while (rs.next()) {
                System.out.println("-------------------------");
                System.out.println("사원번호: " + rs.getInt(1));
                System.out.println("사원명: " + rs.getString(2));
                System.out.println("직업 :" + rs.getString(3));
                System.out.println("상사명 :" + rs.getInt(4));
                System.out.println("입사일 :" + rs.getString(5));
                System.out.println("급여 :" + rs.getInt(6));
                System.out.println("커미션 :" + rs.getInt(7));
                System.out.println("부서번호 :" + rs.getString(8));
            }
            System.out.println("-------------------------");
            
            
//            ###############[  사원 정보 수정하기  ]##################
            System.out.println("[사원 정보 수정하기]");
            System.out.println("-------------------------");
            pstmt = conn.prepareStatement(updaSql);
            pstmt.setInt(1,mgr);
            pstmt.setInt(2, empno);
            retrnRow = pstmt.executeUpdate();
            
            if(retrnRow != 0){
                System.out.println("업데이트가 성공하였습니다.");
            }
            
            pstmt = conn.prepareStatement(seleSql2);
            pstmt.setInt(1, empno);
            rs = pstmt.executeQuery();
            
            System.out.println("[수정된 사원 정보 출력하기]");
            
            while(rs.next()){
                System.out.println("-------------------------");
                System.out.println("사원번호: " + rs.getInt(1));
                System.out.println("사원명: " + rs.getString(2));
                System.out.println("직업 :" + rs.getString(3));
                System.out.println("상사명 :" + rs.getInt(4));
                System.out.println("입사일 :" + rs.getString(5));
                System.out.println("급여 :" + rs.getInt(6));
                System.out.println("커미션 :" + rs.getInt(7));
                System.out.println("부서번호 :" + rs.getString(8));
            }
            System.out.println("-------------------------");
            
            
//            ###############[  사원 정보 삭제하기  ]##################
            System.out.println("[사원 정보 삭제하기]");
            System.out.println("-------------------------");
            
            pstmt = conn.prepareStatement(deleSql);
            pstmt.setInt(1, empno);
            
            retrnRow = pstmt.executeUpdate();
            
            if(retrnRow == 1){
                System.out.println("해당 회원의 정보가 삭제되었습니다.");
            }
            
            
            // 3. 데이터 베이스의 연결 종료
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
 
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("데이터베이스 드라이버를 찾을 수 없습니다.");
            e.printStackTrace();
        }
 
    }
 
}