- [Java 기본] JDBC를 이용한 Oracle 연동

출처 : http://cafe.naver.com/litave/407

//===========================================================================//
   Java 기본 마지막 자료군요.

   기존 실습때는 MS-Access로 실습 했었는데 그다지 의미가 없어보여,

   Oracle에 접속하여 SELECT 및 INSERT 하는 프로그램을 작성하여 올립니다.

   역시 말로 장황하게 설명 하기 보다는 이게 편하네요. ㅎㅎ..

   작성자 : litwave
//---------------------------------------------------------------------------//

// DBConn.java

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;


/**
* JDBC를 이용하여 Database Connection을 얻어오는 클래스
* @author lit
*
*/
public class DBConn {
    private final String driver = "oracle.jdbc.driver.OracleDriver";
    private final String url = "jdbc:oracle:thin:@192.168.1.100:1521:ORA9i";
    private final String id = "scott";
    private final String pw = "tiger";
    
    public DBConn() {
        loadDriver();
    }
    
    public void loadDriver() {
        try {
            /** 1.Driver 로딩 */
            Class.forName(this.driver);
            System.out.println("1. Driver loading 성공(Class.forName(this.driver))!!");
        } catch(ClassNotFoundException cnf) {
            System.out.println("1. Driver loading 실패!!");
        }
    }
    
    /**
     * DriverManager를 통하여 데이터 베이스의 Connection을 얻어온다.
     * @return
     * @throws SQLException
     */
    public Connection getConnection() throws SQLException {
        Connection conn = null;

        conn = DriverManager.getConnection(this.url, this.id, this.pw);
        System.out.println("2. Database Connection 얻기 성공(DriverManager.getConnection(this.url, this.id, this.pw)) !!");
        return conn;
    }
    
    /**
     * 전달 받은 인자로 데이터를 조회한다.
     * @param hiredate
     * @param sal
     * @param empno
     * @return
     */
    public List selectEmp(String hiredate, int sal, String empno) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 수행할 SQL 준비
        StringBuffer query = new StringBuffer();
        query
        .append(" SELECT EMPNO, ENAME, JOB, to_char(HIREDATE, 'RRRR-MM-DD HH24:MI:SS') HIREDATE, SAL  \n")
        .append(" FROM EMP  \n")
        .append(" WHERE HIREDATE > to_date(?, 'RRRRMMDD')  \n")
        .append("   AND SAL >= ?  \n")
        .append("   AND EMPNO like ?  \n");
        
        List dataList = new ArrayList();
        try {
            int pstmtCount = 1;
            conn = getConnection();
            
            System.out.println("3. SQL 수행 준비 및 바인드 변수 설정(conn.prepareStatement(query.toString())) !!");
            pstmt = conn.prepareStatement(query.toString());
            pstmt.setString(pstmtCount++, hiredate.substring(0, 8));
            pstmt.setInt(pstmtCount++, sal);
            pstmt.setString(pstmtCount++, empno + "%");
            System.out.println("4. SQL 실행(pstmt.executeQuery()) !!");
            rs = pstmt.executeQuery();
            
            EmpInfo info = null;
            System.out.println("5. SQL 실행 결과 패치 및 저장(rs.next()) !!");
            while(rs.next()) {
                info = new EmpInfo(
                    rs.getInt("EMPNO"),
                    rs.getString("ENAME"),
                    rs.getString("JOB"),
                    rs.getString("HIREDATE"),
                    rs.getInt("SAL")
                );
                
                dataList.add(info);
            }
            
            rs.close();
            pstmt.close();
            conn.close();
        
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if(rs    != null) try { rs.close();    } catch (SQLException e) { }
            if(pstmt != null) try { pstmt.close(); } catch (SQLException e) { }
            if(conn  != null) try { conn.close();  } catch (SQLException e) { }
        }
        return dataList;
    }
    
    /**
     * 전달받은 Emp 정보를 EMP 테이블에 삽입한다.
     * @param info
     * @return
     */
    public int insertEmp(EmpInfo info) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        // 수행할 SQL 준비
        StringBuffer query = new StringBuffer();
        query
        .append(" INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  \n")
        .append(" VALUES(nvl((SELECT max(EMPNO) FROM EMP), 1) + 1, ?, ?, ?, to_date(?, 'RRRRMMDD'), ?, ?, ?)  \n");

        int executeCount = 0;
        try {
            int pstmtCount = 1;
            conn = getConnection();
            
            // Transaction 을 수동으로 조작
            conn.setAutoCommit(false);
            
            System.out.println("3. SQL 수행 준비 및 바인드 변수 설정(conn.prepareStatement(query.toString())) !!");
            pstmt = conn.prepareStatement(query.toString());
            pstmt.setString(pstmtCount++, info.getEmpName());
            pstmt.setString(pstmtCount++, info.getJob());
            pstmt.setInt(pstmtCount++, info.getMgr());
            pstmt.setString(pstmtCount++, info.getHiredate().substring(0, 8));
            pstmt.setInt(pstmtCount++, info.getSal());
            pstmt.setInt(pstmtCount++, info.getComm());
            pstmt.setInt(pstmtCount++, info.getDeptno());
            System.out.println("4. SQL 실행(pstmt.executeUpdate()) !!");
            executeCount = pstmt.executeUpdate();
            
            // Transaction 정상 수행 시 commit 처리
            conn.commit();
            pstmt.close();
            conn.close();
        
        } catch (SQLException e) {
            // Transaction 오류 발생 시 rollback 처리
            try { conn.rollback(); } catch (SQLException e1) { }
            e.printStackTrace();

        } finally {
            if(pstmt != null) try { pstmt.close(); } catch (SQLException e) { }
            if(conn  != null) try { conn.close();  } catch (SQLException e) { }
        }
        return executeCount;
    }
    
    
    /**
     * DB연결 테스트
     * @param args
     */
    public static void main(String [] args) {
        DBConn dbConn = new DBConn();
        
        // EMP 테이블에서 데이터 조회
        List dataList = dbConn.selectEmp("19810401", 1500, "7");
        System.out.println("\n-----------------------------------------------------------");
        for(int index=0; index < dataList.size(); index++) {
            System.out.println(dataList.get(index));
        }
        
        // EMP 테이블에 Data Insert 테스트
        EmpInfo info = new EmpInfo("LITWAVE", "PROGRAMER", 7902, "19940711", 1400, 200, 20);
        dbConn.insertEmp(info);
    }
}

/**
* EMP 테이블의 정보를 담을 수 있는 Value Object
*/
class EmpInfo {
    private int empNo;
    private String empName;
    private String job;
    private int mgr;
    private String hiredate;
    private int sal;
    private int comm;
    private int deptno;

    public EmpInfo(int empNo, String empName, String job, String hiredate,
            int sal) {
        this.empNo = empNo;
        this.empName = empName;
        this.job = job;
        this.hiredate = hiredate;
        this.sal = sal;
    }

    public EmpInfo(String empName, String job, int mgr,
            String hiredate, int sal, int comm, int deptno) {
        this.empName = empName;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public int getEmpNo() {
        return empNo;
    }
    public String getEmpName() {
        return empName;
    }
    public String getJob() {
        return job;
    }
    public String getHiredate() {
        return hiredate;
    }
    public int getSal() {
        return sal;
    }
    public int getMgr() {
        return mgr;
    }
    public int getComm() {
        return comm;
    }
    public int getDeptno() {
        return deptno;
    }

    public String toString() {
        return "EMPNO : " + this.empNo
            + ", EMP_NAME : " + this.empName
            + ", JOB : " + this.hiredate
            + ", HIREDATE : " + this.hiredate
            + ", SAL : " + this.sal;
    }
}

//---------------------------------------------------------------------------//