1일차_JDBC 환경설정, Sample Code

--------------------------------
원격 접속을 위한 오라클 서버 환경 설정.

1. 팀원은 오라클 로컬 서버는 서비스 중지 상태를 만든다.

2. 팀장(SQL 서버 담당자)은 오라클 서버의 관리자 계정(SYS)에 대한 암호를 변경한다.
ALTER USER sys IDENTIFIED BY 새로운암호;

3. 팀장(SQL 서버 담당자)은 원격 서버 연결을 위해서 오라클 서버에 팀원들의 계정을 생성한다(자기 자신 계정 포함).
CREATE USER 계정이름 IDENTIFIED BY 1234
 DEFAULT TABLESPACE users
 QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE, CREATE VIEW TO 계정이름;

--계정 삭제할 때 사용할 것
DROP USER 계정이름;
DROP USER 계정이름 CASCADE;

4. 팀장(SQL 서버 담당자)은 원격 서버 연결을 위해서 방화벽에 1521 포트 허용을 추가한다.

5. 팀원은 SQL Developer에서 팀장(SQL 서버 담당자) 컴퓨터의 IP 번호를 이용해서  원격 연결을 설정한다.

6. 팀원은 원격 연결이 되면 본인의 암호를 변경한다.
ALTER USER 계정이름 IDENTIFIED BY 새로운암호;

 

 

---------------------------------
JDBC(Java Database Connectivity) 환경 설정

1. JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.

2. 구성 요소
JDBC API: This provides the application-to-JDBC Manager connection.
JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

3. 오라클 전용 JDBC Driver 준비
www.oracle.com 사이트에서 ojdbc6.jar 다운로드

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html?ssSourceSiteId=ocomen

ojdbc6.jar 파일을 아래 경로에 복사한다(이클립스 Java Project 생성시 JRE System Library 항목의 경로 참조)
C:\Program Files\Java\jre7\lib\ext

---------------------------------------------
오라클 데이터베이스에 생성할 테이블 샘플

CREATE TABLE employees (
 id NUMBER NOT NULL,
 age NUMBER NOT NULL,
 first VARCHAR2(255),
 last VARCHAR2(255)
);
 
 
INSERT INTO employees VALUES (100, 18, 'Zara', 'Ali');
INSERT INTO employees VALUES (101, 25, 'Mahnaz', 'Fatma');
INSERT INTO employees VALUES (102, 30, 'Zaid', 'Khan');
INSERT INTO employees VALUES (103, 28, 'Sumit', 'Mittal');
COMMIT;

--테이블 존재 확인
SELECT * FROM user_tables;

--데이터 확인
SELECT * FROM employees;

--테이블 구조 확인
SELECT * FROM user_tab_columns
  WHERE table_name='EMPLOYEES';

--테이블 제약조건 확인
SELECT * FROM constraint_check
  WHERE table_name='EMPLOYEES';

 

---------------------------------------------
JDBC 응용 프로그램 생성 (오라클 서버 연결 테스트)

1. Import the packages . Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

2. Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the database.

static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver"; 
Class.forName(JDBC_DRIVER);

3. Open a connection . Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.

static final String DB_URL = "jdbc:oracle:thin:@IP주소:포트번호:SID";
static final String USER = "username";
static final String PASS = "password";
DriverManager.getConnection(DB_URL,USER,PASS);

4. Clean up the environment . Requires explicitly closing all database resources versus relying on the JVM's garbage collection.

//JDBC01.java
package com.test;

//STEP 1. Import required packages
import java.sql.*;

public class JDBC01 {

 //  Database credentials
 static final String USER = "사용자이름";
 static final String PASS = "패스워드";
 static final String DB_URL = "jdbc:oracle:thin:@오라클서버IP주소:1521:xe";
 
 public static void main(String[] args) {

  Connection conn = null;
  Statement stmt = null;
  
  //STEP 2: Register JDBC driver
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");

   
   //STEP 3: Open a connection
   System.out.println("Connecting to database...");
   conn = DriverManager.getConnection(DB_URL,USER,PASS);   
   
   //STEP 4: Execute a query
   System.out.println("Creating statement...");
   stmt = conn.createStatement();
   String sql;
   sql = "SELECT id, first, last, age FROM Employees";
   ResultSet rs = stmt.executeQuery(sql);   
   
   //STEP 5: Extract data from result set
   while(rs.next()){
    //Retrieve by column name
    int id  = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    
    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
   } 
   
      //STEP 6: Clean-up environment
      rs.close();   
  
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   //STEP 6: Clean-up environment
       try{
           if(stmt!=null)
              stmt.close();
       }catch(Exception se2){
       }
       try{
           if(conn!=null)
              conn.close();
       }catch(Exception se){
           se.printStackTrace();
       }
  }
  
  
 }

}

 

 

 

------------------------------------------
JDBC 응용 프로그램 생성 (오라클 서버 연결 및 쿼리 실행 테스트)

1. Import the packages
2. Register the JDBC driver
3. Open a connection
4. Execute a query . Requires using an object of type Statement for building and submitting an SQL statement to the database.

 stmt = conn.createStatement();
 String sql;
 sql = "SELECT id, first, last, age FROM Employees";
 stmt.executeQuery(sql);

5. Extract data from result set . Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.

 ResultSet rs = stmt.executeQuery(sql);

 //STEP 5: Extract data from result set
 while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
  }
  //STEP 6: Clean-up environment
  rs.close();

6. Clean up the environment

//Sample02.java
package com.test;

//STEP 1. Import required packages
import java.sql.*;

public class Sample02 {

 //상수 선언
 static final String DB_URL = "jdbc:oracle:thin:@211.63.89.XX:1521:xe";
 static final String USER = "아이디";
 static final String PASS = "패스워드";  
 
 public static void main(String[] args) {

  Connection conn = null;
  Statement stmt = null;
  try {
   
   //STEP 2: Register JDBC driver
   Class.forName("oracle.jdbc.driver.OracleDriver");
   
   //STEP 3: Open a connection
   conn = DriverManager.getConnection(DB_URL,USER,PASS);
   
   if (conn != null) {
    System.out.println("오라클 서버 연결 성공!");
   }
   
   //STEP 4: Execute a query
   stmt = conn.createStatement();
   String sql;
   //주의. 문장 끝에 ;(semicolon) 없다.
   sql = "SELECT id, first, last, age FROM employees";
   ResultSet rs = stmt.executeQuery(sql);
   while(rs.next()) {
    
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    
          System.out.print("ID: " + id);
          System.out.print(", Age: " + age);
          System.out.print(", First: " + first);
          System.out.println(", Last: " + last);    
           
   }
   rs.close();
   
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
     //STEP 6: Clean-up environment
        //finally block used to close resources
     try{
         if(stmt!=null)
            stmt.close();
     }catch(SQLException se){
           se.printStackTrace();
     }
        try{
           if(conn!=null)
              conn.close();
        }catch(SQLException se){
           se.printStackTrace();
        }//end finally try
   }//end try

  
 }

}

 

 

 

//JDBC02.java
package com.test;

//STEP 1. Import required packages
import java.sql.*;

public class JDBC02 {

 //  Database credentials
 static final String USER = "사용자이름";
 static final String PASS = "패스워드";
 static final String DB_URL = "jdbc:oracle:thin:@오라클서버IP주소:1521:xe";

 public static void main(String[] args) {

  Connection conn = null;
  Statement stmt = null;
  
  try {

   //STEP 2: Register JDBC driver
   Class.forName("oracle.jdbc.driver.OracleDriver");
   
   //STEP 3: Open a connection
   System.out.println("Connecting to database...");
   conn = DriverManager.getConnection(DB_URL,USER,PASS);   
   
   //STEP 4: Execute a query
   System.out.println("Creating statement...");
   stmt = conn.createStatement();
   String sql;
   //주의. 문장 끝에 ;(semicolon) 없다.
   sql = "SELECT bid, buseo FROM buseo";
   ResultSet rs = stmt.executeQuery(sql);   

   //STEP 5: Extract data from result set
   while(rs.next()) {
    String bid = rs.getString("bid");
    String buseo = rs.getString("buseo");
    
    System.out.printf("%s %s %n"
      , bid, buseo);
   }
   
   //STEP 6: Clean-up environment
   rs.close();
   
  }catch(Exception e){
   System.out.println(e.toString());
  }finally{
   //STEP 6: Clean-up environment
       try{
           if(stmt!=null)
              stmt.close();
       }catch(Exception se2){
       }
       try{
           if(conn!=null)
              conn.close();
       }catch(Exception se){
           se.printStackTrace();
       }   
  }

  
 }

}

 

------------------------------------------------
요약

1. JDBC 환경 설정
- 원격 오라클 서버 세팅
- ojbc6.jar

2. JDBC 클래스
- Class : Class.forName(오라클JDBC드라이버클래스)
- Connection : DriverManger.getConnection(오라클연결정보)
- Statement : 쿼리 실행 및 결과 반환. conn.createStatement(). stmt.executeQuery(쿼리문)
- ResultSet : 결과 집합 객체. rs.next(), rs.getXXX()

3. 오라클 쿼리
- SELECT, JOIN, SUB QUERY
- TABLE, VIEW

4. JDBC Application
01. Import the packages
02. Register the JDBC driver
03. Open a connection
04. Execute a query
05. Extract data from result set
06. Clean-up environment

----------------------------------------