jdbc 커넥션 풀 예제

출처 : http://aith.springnote.com/pages/3648889.xhtml

 

JDBC - 예제

JDBC - 책 예제 소스

JDBC 클래스 패스 설정

D:\oracle\ora92\jdbc\lib에서 C:\Java\jdk1.6\jre\lib\ext 폴더에 classes12.jar, ojdbc14.jar 파일을 복사해서 붙여넣는다.

Connection을 이용한 실습

예제 1) 데이터 베이스 연결

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
3. public class ConnectionTest {
   public static void main(String[] args) {
   Connection con = null; // DB연결을 위해 사용하는 객체를 선언
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver"); // 드라이브를 로드 함
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger"); // 각자의 설정에 맞춰 사용
   System.out.println("데이터베이스 연결 성공했습니다.");
   con.close();
   System.out.println("데이터베이스 연결 해제 성공했습니다.");
   } catch(ClassNotFoundException e) {
   System.out.println("JDBC Driver load fail!!");
   e.printStackTrace();
   } catch(SQLException e) {
   System.out.println("Connection fail!!");
   e.printStackTrace();
   } finally {
   try {
   if(con != null) con.close();
   } catch(SQLException e) {
4. e.printStackTrace();
5. }
   }
   }
   }

Statement를 이용한 실습

예제 1) t_emp 테이블 조회 (t_emp 테이블이 없을 경우 에러 발생)

1. CREATE TABLE t_emp(
   id NUMBER(5)
   , name VARCHAR2(25)
   , salary NUMBER(7, 2)
   , phone VARCHAR2(15)
   , dept_name VARCHAR2(25));
   

1. INSERT INTO t_emp VALUES(101, '조성희', 21300.50, '02-3345-4395', '교육사업부');
2. COMMIT;

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
3. public class SelectTest{
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott","tiger");
   stmt = conn.createStatement(); // Statement 객체를 Connection의 메소드를 이용해 생성
   ResultSet rs = stmt.executeQuery("select * from t_emp"); // 쿼리를 실행하고 결과값을 rs에 저장
   while(rs.next()) { // 결과값이 있는 동안 반복문을 실행
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double salary = rs.getDouble(3);
   String phone = rs.getString(4);
   String dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   rs.close();
   stmt.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
   e.printStackTrace();
4. }
   }
   }
   }

예제 2) t_emp 테이블에 데이터 삽입

1. import java.sql.*;
3. public class InsertTest {
   public static void main(String[] args) {
   Connection conn =null;
   Statement stmt = null;
   try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott","tiger");
   stmt=conn.createStatement();
   String query = "insert into t_emp values(200,'나세원',10000.00, '02-5678-1111','교육사업부')";
   int rowcount = stmt.executeUpdate(query); // 결과 값을 몇건을 실행했는지 숫자형으로 반환 한다
   System.out.println(rowcount +"개의 행 변경이 발생하였습니다.");
   stmt.close();
   conn.close();
   } catch (Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 3) execute 메소드를 이용한 다양한 SQL문장 실행 (SELECT, INSERT문)

1. import java.sql.*;
3. public class ExecuteTest {
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   if (args.length == 0) {
   System.out.println("select를 원하면 java ExecuteTest select을 입력하여 실행하세요");
   System.out.println("insert를 원하면 java ExecuteTest insert 300 나동걸 12345.67 032-123-4567 컨텐츠개발부를 입력하여 실행하세요");
   return;
   }
4. try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch(ClassNotFoundException e) {
5. e.printStackTrace();
6. }
7. try {
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   stmt = conn.createStatement();
   String sql = null;
   
   if(args[0].equals("select")) {
   sql = "select * from t_emp";
   } else {
   int id = Integer.parseInt(args[1]);
   String name = args[2];
   double salary = Double.parseDouble(args[3]);
   String phone = args[4];
   String dept = args[5];
   sql = "insert into t_emp values(" + id + ", '"
   + name + "' , "
   + salary + ", '"
   + phone + "', '"
   + dept + "')";
   }
   boolean result = stmt.execute(sql);
   if(result) {
   System.out.println("조회 결과는 다음과 같습니다.");
   ResultSet rs = stmt.getResultSet();
   
   while(rs.next()) {
   int id = rs.getInt(1);
   String name = rs.getString(2);
   double salary = rs.getDouble(3);
   String phone = rs.getString(4);
   String dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   } else {
   System.out.println("변경된 행의 수는 : " + stmt.getUpdateCount());
   }
   } catch(SQLException e) {
   System.out.println("Exception 발생" + e.getMessage() + " " + e.getErrorCode() + " " + e.getSQLState());
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
8. e.printStackTrace();
   }
   }
   }
   }

예제 4) executeBatch 메소드를 이용한 SQL문의 일괄처리

1. import java.sql.*;
3. public class BatchTest {
   public static void main(String[] args) {
   Connection conn =null;
   Statement stmt = null;
   try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott","tiger");
   stmt=conn.createStatement();
   stmt.addBatch("insert into t_emp values(400, '박연옥',29000.54,'032-534-0987','e-campus팀')"); // 배치문 등록
   stmt.addBatch("insert into t_emp values(401, '안소연',69000.58,'02-6225-0987','교육사업부팀')");
   stmt.addBatch("insert into t_emp values(402, '차유진',56000.23,'02-2244-0557','교육운영부')");
   stmt.addBatch("insert into t_emp values('최우식',29000.02,'02-455-9864','과정개발부')");
   int [] batchCount = stmt.executeBatch();
   for(int i = 0; i < batchCount.length; i++) {
   System.out.println(batchCount[i]);
   }
   stmt.close();
   conn.close();
   } catch(BatchUpdateException b) {
   System.err.println("SQLException :"+b.getMessage());
   System.err.println("SQLState :"+b.getSQLState());
   System.err.println("Massage :"+b.getMessage());
   System.err.println("벤더 :"+b.getErrorCode());
   System.err.println("Upate counts:");
   int [] updateCounts = b.getUpdateCounts();
   for(int i = 0; i < updateCounts.length; i++){
   System.err.println(updateCounts[i]+" ");
   }
   } catch(SQLException ex) {
   System.err.println("SQLException :"+ex.getMessage());
   System.err.println("SQLState :"+ex.getSQLState());
   System.err.println("Massage :"+ex.getMessage());
   System.err.println("벤더 :"+ex.getErrorCode());
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

PreparedStatement를 이용한 실습

예제 1) t_emp 테이블의 특정 레코드 조회

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
3. public class PreparedSelectTest {
   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott","tiger");
   pstmt = conn.prepareStatement("select * from t_emp where name = ?"); // 'name'은 바뀔수 있는 값 '?'를 지정한다
   pstmt.setString(1,args[0]); // '?'에 값을 매개형 인자 값을 넣는다
   ResultSet rs = pstmt.executeQuery(); // 'pstmt'를 수행한다 결과가 있다면 'rs'로 결과를 받는다
   while(rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double salary = rs.getDouble(3);
   String phone = rs.getString(4);
   String dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   rs.close();
   pstmt.close();
   conn.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(pstmt != null) pstmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 2) t_emp 테이블에 데이터 삽입

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
3. public class PreparedInsertTest {
   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott", "tiger");
   pstmt = conn.prepareStatement("insert into t_emp values(?, ?, ?, ?, ?)");
   pstmt.setInt(1, Integer.parseInt(args[0]));
   pstmt.setString(2, args[1]);
   pstmt.setDouble(3, Double.parseDouble(args[2]));
   pstmt.setString(4, args[3]);
   pstmt.setString(5, args[4]);
   int j = pstmt.executeUpdate();
   System.out.println(j + " rows affected ");
   pstmt.close();
   conn.close();
   } catch (SQLException e) {
   e.printStackTrace();
   } catch(ClassNotFoundException e) {
   e.printStackTrace();
   } finally {
   try {
   if( stmt != null) pstmt.close();
   if( conn != null) conn.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 3) file과 같은 긴 길이의 데이터를 삽입하는 SQL 문장 실행

1. CREATE TABLE files(
   name VARCHAR2(40)
   , contents VARCHAR2(4000));

1. import java.io.BufferedReader;
   import java.io.File;
   import java.io.FileInputStream;
   import java.io.InputStreamReader;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.Statement;
3. public class PreparedFileTest {
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null; // 딱히 사용하지 않음
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch (ClassNotFoundException e) {
4. e.printStackTrace();
5. }
   
   try {
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott","tiger");
   PreparedStatement pstmt = conn.prepareStatement("insert into files values(?, ?)");
   File file = new File("PreparedFileTest.java"); // 파일의 경로를 수정해 줄것!
   int size = (int) file.length(); // 딱히 사용하지 않음
   FileInputStream fin = new FileInputStream(file);
   pstmt.setString(1, file.getName());
   BufferedReader br = new BufferedReader(new InputStreamReader(fin)); // InputStream으로 파일을 읽어서 InpuStreamReader를 사용
6. // FileReader를 사용하는 것도 괜찮을 지도...
   String s;
   String s1="";
   while((s = br.readLine())!= null) {
   // System.out.println(s1);
   s1 += s + "\n"; // StringBuffer를 사용하는 것도 괜찮을 지도...
   }
   System.out.println("after while....." + s1);
   pstmt.setString(2, s1);
   pstmt.executeUpdate();
   } catch(Exception e) {
7. e.printStackTrace();
   }
   }
   }

예제 4) PreparedStatement를 이용한 여러 종류의 SQL문 처리

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
3. public class PrepareExecute {
   public static void main(String args[]) {
   int id = 0;
   double salary = 0;
   String name = null;
   String phone = null;
   String dept_name = null;
   Connection conn = null;
   PreparedStatement pstmt = null;
   if(args.length == 0) {
   System.out.println("select를 원하면 java PreparedExecute select 을 입력하여 실행하세요.");
   System.out.println("insert를 원하면 jaca PreparedExecute 701 차경호 10000.50 02-3455-4666 컨텐츠개발부 를 입력하여 실행하세요.");
   return;
   }
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   String sql = null;
   if(args[0].equals("select")) {
   sql = "select * from t_emp";
   }
   else {
   id = Integer.parseInt(args[0]);
   name = args[1];
   salary = Double.parseDouble(args[2]);
   phone = args[3];
   dept_name = args[4];
   sql = "insert into t_emp values(?, ?, ?, ?, ?)";
   }
   pstmt = conn.prepareStatement(sql);
   if(!args[0].equals("select")) {
   pstmt.setInt(1, id);
   pstmt.setString(2, name);
   pstmt.setDouble(3, salary);
   pstmt.setString(4, phone);
   pstmt.setString(5, dept_name);
   }
   boolean result = pstmt.execute();
   if(result) {
   System.out.println("조회 결과는 다음과 같습니다.");
   ResultSet rs = pstmt.getResultSet();
   while(rs.next()) { // ResultSet의 각 레코트행 이동
   id = rs.getInt(1);
   name = rs.getString(2);
   salary = rs.getDouble(3);
   phone = rs.getString(4);
   dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   } else {
   System.out.println("변경된 행의 수는 : " + pstmt.getUpdateCount());
   }
   } catch(ClassNotFoundException e) {
   e.printStackTrace();
   } catch(Exception e) {
   {e.printStackTrace();}
   }
   }
   }

예제 5) PreparedStatement를 이용한 SQL문의 일괄 처리

1. import java.sql.Connection;
   import java.sql.BatchUpdateException;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
3. class PreparedBatch {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement pstmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("드라이버로딩성공");
   } catch(ClassNotFoundException e) {}
   String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   try {
   con = DriverManager.getConnection(url, "scott", "tiger");
   pstmt = con.prepareStatement("update t_emp set dept_name = ? where name = ?");
   pstmt.setString(1, "인재개발부");
   pstmt.setString(2, "조성희");
   pstmt.addBatch();
   pstmt.setString(1, "it컨텐츠개발부");
   pstmt.setString(2, "나세원");
   pstmt.addBatch();
   int [] counts = pstmt.executeBatch();
   pstmt.close();
   con.close();
   } catch(BatchUpdateException b) {
   System.err.println("SQLException" + b.getMessage());
   System.err.println("SQLState" + b.getSQLState());
   System.err.println("Message" + b.getSQLState());
   System.err.println("벤더 "+ b.getErrorCode());
   System.err.println("Update counts");
   int [] updateCounts = b.getUpdateCounts();
   for(int i = 0; i < updateCounts.length; i++) {
   System.err.println(updateCounts[i] + "");
   }
   } catch(SQLException e) {
   e.printStackTrace();
   } finally {
   try {
   if(pstmt != null) pstmt.close();
   if(con!= null) con.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }
   

ResultSet을 이용한 실습

예제 1) t_emp 테이블의 조회

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
3. public class SelectTest{
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora9","scott","tiger");
   stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select * from t_emp");
   while(rs.next()) {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double salary = rs.getDouble(3); // 인덱스로 알아보기 힘드니 칼럼명을 적어주자!
   String phone = rs.getString(4);
   String dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   rs.close();
   stmt.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(conn != null) conn.close();
   } catch (SQLException e){
   e.printStackTrace();
   }
   }
   }
   }

예제 2) t_emp 테이블의 특정 레코드 조회 (차유진, 안소연)

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
3. public class PrepareSelectTest {
   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9","scott", "tiger");
   pstmt = conn.prepareStatement("select * from t_emp where name = ?");
   pstmt.setString(1, args[0]);
   ResultSet rs = pstmt.executeQuery();
   while( rs.next())
   {
   int id = rs.getInt("id");
   String name = rs.getString("name");
   double salary = rs.getDouble(3);
   String phone = rs.getString(4);
   String dept_name = rs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t" + phone + "\t" + dept_name);
   }
   rs.close();
   pstmt.close();
   conn.close();
   
   } catch (Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(pstmt != null) pstmt.close();
   if(conn != null) conn.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 3) ROWNUM을 이용하여 조회한 데이터를 Vector에 저장하여 조회시 활용

t_emp 테이블의 id와 name의 값을 조회순으로 1번째 데이터부터 5개만 조회한다

1. SELECT ROWNUM
   , id
   , name
   FROM t_emp
   WHERE ROWNUM >= 1
   AND ROWNUM <=5;

t_emp 테이블의 id와 name의 값을 조회순으로 6번째 데이터부터 5개만 조회한다면 다음과 같은 SQL문을 작성

(ROWNUM 함수는 각 레코드행 조회시에 해당 레코드 번호를 생성하므로 (>, >=) 연산자와 함께 사용될 수 없다)

1. SELECT ROWNUM
   , id
   , name
   FROM t_emp
   WHERE ROWNUM BETWEEN 6 AND 10;

위 SQL문은 다음과 같이 SUBQUERY를 이용하여 SELECT 한 데이터를 대상으로 또 한번 SELECT 하는 형태로 수정될 수 있다

1. SELECT id
   , name
   FROM (SELECT ROWNUM R
   , id
   , name
   FROM t_emp)
   WHERE R BETWEEN 6 AND 10;

실행시 인자 값 (6 10)

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.Vector;
3. public class SelectVextorTest {
   public static void main(String[] args) {
   Connection con = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   System.out.println("연결 성공");
4. String sql = "select id" +
   " , name" +
   " from (select rownum r" +
   " , id" +
   " , name" +
   " from (select *" +
   " from t_emp" +
   " order by id desc))" +
   " where r >= ? and r <= ?";
5. PreparedStatement st = con.prepareStatement(sql);
   st.setInt(1, Integer.parseInt(args[0]));
   st.setInt(2, Integer.parseInt(args[1]));
6. ResultSet rs = st.executeQuery();
7. Vector table = new Vector();
8. while (rs.next()) {
   Vector record = new Vector(2);
   int id = rs.getInt(1);
   String name = rs.getString(2);
9. record.addElement(new Integer(id));
   record.addElement(name);
   table.addElement(record);
   }
   con.close();
   System.out.println("연결 해제 성공 !!!!");
   
   System.out.println("조회된 데이터는 다음과 같습니다");
   for (int i = 0; i < table.size(); i++) {
   System.out.println(table.elementAt(i));
   }
   } catch (ClassNotFoundException e) {
   System.out.println("클래스 패스 또는 드라이버명을 확인 하세요");
   } catch (SQLException e) {
   System.out.println("연결정보를 확인하세요");
   e.printStackTrace();
   } finally {
   try {
   con.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 4) ScrollableResultSet을 통한 데이터베이스의 데이터 갱신

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
3. public class UpdatableUpdate {
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
4. try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
   "jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
   ResultSet.CONCUR_UPDATABLE);
   ResultSet srs = stmt
   .executeQuery("select id, name, salary, phone, dept_name from t_emp");
5. while (srs.next()) {
   int id = srs.getInt("id");
   String name = srs.getString(2);
   double salary = srs.getDouble(3);
   String phone = srs.getString(4);
   String dept_name = srs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t"
   + phone + "\t" + dept_name);
   }
   srs.last();
   
   srs.updateString(2, "변경된 이름"); // 마지막행의 이름을 변경함
   srs.updateString(5, "변경된 부서"); // 마지막행의 부서를 변경함
   srs.updateRow();
6. System.out.println("\n갱신된 후의 테이블 내용");
   srs.beforeFirst();
7. while (srs.next()) {
   int id = srs.getInt(1);
   String name = srs.getString(2);
   double salary = srs.getDouble(3);
   String phone = srs.getString(4);
   String dept_name = srs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t"
   + phone + "\t" + dept_name);
   }
   conn.close();
   } catch (Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if (stmt != null)
   stmt.close();
   if (conn != null)
   conn.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }
   

예제 5) ScrollableResultSet을 통한 데이터베이스의 데이터 삽입

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
3. public class UpdatableInsert {
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
4. try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection(
   "jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
5. stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
   ResultSet.CONCUR_UPDATABLE);
   ResultSet srs = stmt
   .executeQuery("select id, name, salary, phone, dept_name from t_emp");
   while (srs.next()) {
   int id = srs.getInt(1);
   String name = srs.getString(2);
   double salary = srs.getDouble(3);
   String phone = srs.getString(4);
   String dept_name = srs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t"
   + phone + "\t" + dept_name);
   }
   srs.moveToInsertRow(); // ResultSet에 공간을 확보해둔다
   srs.updateInt(1, 503); // 만든 공간에 첫번째에 값을 쓴다
   srs.updateString(2, "심청이");
   srs.updateDouble(3, 10000);
   srs.updateString(4, "o42-3456-7654");
   srs.updateString(5, "총무부");
   srs.insertRow();
   srs.moveToCurrentRow(); // 생략 가능 갱신 후의 row를 저장해둔다
   System.out.println("\n\n갱신된 후의 테이블내용");
   srs.beforeFirst(); // 다시 처음으로 간다
   while (srs.next()) { // SENSITIVE가 적용이 안되서 결과가 바로 출력에 적용은 안된다
   int id = srs.getInt(1);
   String name = srs.getString(2);
   double salary = srs.getDouble(3);
   String phone = srs.getString(4);
   String dept_name = srs.getString(5);
   System.out.println(id + "\t" + name + "\t" + salary + "\t"
   + phone + "\t" + dept_name);
   }
   conn.close();
   } catch (Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if (stmt != null)
   stmt.close();
   if (conn != null)
   conn.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 6) PreparedStatement의 UpdatableScorllableResutSet을 통한 데이터베이스의 데이터 갱신

실행할 인자 값(600)

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
3. public class PreparedUpdatable {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement pstmt = null;
4. try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch (ClassNotFoundException e) {
   e.printStackTrace();
   }
   String url = "jdbc:oracle:thin:@127.0.01:1521:ora9";
   try {
   con = DriverManager.getConnection(url, "scott", "tiger");
   pstmt = con
   .prepareStatement(
   "select id, name, salary, phone, dept_name from t_emp where id >= ?",
   ResultSet.TYPE_SCROLL_INSENSITIVE,
   ResultSet.CONCUR_UPDATABLE);
   pstmt.setInt(1, Integer.parseInt(args[0])); // 몇번째 부터 출력할지 정한다
   ResultSet srs = pstmt.executeQuery();
   srs.moveToInsertRow();
   srs.updateInt("id", 703);
   srs.updateString("name", "안명훈");
   srs.updateDouble(3, 20000.70);
   srs.updateString("phone", "02-555-7294");
   srs.updateString("dept_name", "e-campus부");
   srs.insertRow();// rs.updateRow()와rs.deleteRow()가능
   System.out.println("삽입된 후의 테이블 내용(최근데이터 부터)");
   srs.afterLast();
   while (srs.previous()) {
   int id = srs.getInt("id");
   String name = srs.getString("name");
   System.out.println(id + "\t" + name);
   }
   srs.last();
   srs.deleteRow(); // 삭제 한다
   System.out.println("최근의 입력된 하나의 데이터 삭제된 후의 테이블 내용");
   srs.afterLast();
   while (srs.previous()) { // ResultSet에 반영은 되지 않음 실제 DB에는 반영되어있음
   int id = srs.getInt("id");
   String name = srs.getString("name");
   System.out.println(id + "\t" + name);
   }
   pstmt.close();
   con.close();
   } catch (Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if (pstmt != null)
   pstmt.close();
   if (con != null)
   con.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

DatabaseMetaData를 이용한 실습

예제 1) 데이터베이스가 일괄처리를 지원하는지의 여부를 테스트하는 예제

1. import java.sql.BatchUpdateException;
   import java.sql.Connection;
   import java.sql.DatabaseMetaData;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
2. 
   public class MetaBatchTest {
   public static void main(String[] args) {
   Connection con = null;
   Statement stmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   System.out.println("드라이버로딩 성공");
   } catch(ClassNotFoundException e) {
   e.printStackTrace();
   }
   String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   try {
   con = DriverManager.getConnection(url, "scott", "tiger");
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   DatabaseMetaData dbmd = con.getMetaData();
   if(dbmd.supportsBatchUpdates()) { // Batch가 지원하는지 여부를 확인 한다 보통은 다 지원한다
   stmt.addBatch("INSERT INTO t_emp VALUES(801, '김정현', 87500.50, '02-452-6842', '프로젝트사업부')");
   stmt.addBatch("INSERT INTO t_emp VALUES(802, '이희만', 84500.45, '031-552-6654', '총무부')");
   stmt.addBatch("INSERT INTO t_emp VALUES(803, '김현숙', 56500.60, '02-465-6554', '총무부')");
   stmt.addBatch("UPDATE t_emp SET dept_name = '경리부' WHERE dept_name = '총무부'");
   System.out.println("batch 작업 수행 이 후 ");
   int [] batchCount = stmt.executeBatch();
   for(int i = 0; i < batchCount.length; i++) {
   System.out.println(batchCount[i]);
   }
   } else {
   System.out.println("배치지원안됨");
   int rowCount = 0;
   rowCount += stmt.executeUpdate("INSERT INTO t_emp VALUES(801, '김정현', 87500.50, '02-452-6842', '프로젝트사업부')");
   rowCount += stmt.executeUpdate("INSERT INTO t_emp VALUES(802, '이희만', 84500.45, '031-552-6654', '총무부')");
   rowCount += stmt.executeUpdate("INSERT INTO t_emp VALUES(803, '김현숙', 56500.60, '02-465-6554', '총무부')");
   rowCount += stmt.executeUpdate("UPDATE t_emp SET dept_name = '경리부' WHERE dept_name = '총무부'");
   System.out.println(rowCount + "개의 행이 변화되었습니다.");
   }
   stmt.close();
   con.close();
   } catch(BatchUpdateException e) {
   int [] counts = e.getUpdateCounts();
   for(int i = 0; i < counts.length; i++) {
   System.out.println(i + "batch : " + counts[i] + " rows update");
   }
   e.printStackTrace();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(con != null) con.close();
   if(stmt != null) stmt.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 2) ResultSet의 Type 지원여부를 알아보는 예제

1. import java.sql.Connection;
   import java.sql.DatabaseMetaData;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
2. 
   public class MetaResultTypeTest {
   public static void main(String[] args) {
   Connection con = null;
   Statement stmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch(ClassNotFoundException e) {
   e.printStackTrace();
   }
   String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   try {
   con = DriverManager.getConnection(url, "scott", "tiger");
   DatabaseMetaData dbmd = con.getMetaData();
   if(dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {
   if(dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
   System.out.println("지원함");
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   ResultSet srs = stmt.executeQuery("SELECT id" +
   " , name" +
   " , salary" +
   " , phone" +
   " , dept_name" +
   " FROM t_emp");
   srs.last();
   srs.updateString(2, "변경후의 이름");
   srs.updateDouble(3, 20000);
   srs.updateString(4, "042-3456-7654");
   srs.updateString(5, "총무부");
   srs.updateRow();
   } else {
   System.out.println("TYPE_SCROLL_SENSITIVE 타입은 지원안함");
   }
   } else {
   System.out.println("ResultSet.TYPE_SCROLL_SENSITIVE," +
   " ResultSet.CONCUR_UPDATABLE 타입은 지원안하므로" +
   " ResultSet 갱신할 수 없음 ");
   }
   stmt.close();
   con.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(stmt != null) stmt.close();
   if(con != null) con.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   }

예제 3) 데이터 베이스 제품정보와 JDBC 드라이버 정보를 알아본는 예제

1. import java.sql.Connection;
   import java.sql.DatabaseMetaData;
   import java.sql.DriverManager;
   import java.sql.SQLException;
2. 
   public class MetaInformation {
   public MetaInformation() {
   Connection con = null;
   try {
   String driverName = "oracle.jdbc.driver.OracleDriver";
   Class.forName(driverName);
   String dbURL = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   con = DriverManager.getConnection(dbURL, "scott", "tiger");
   DatabaseMetaData dbmd = con.getMetaData();
   System.out.println(dbmd.getDriverName());
   System.out.println(dbmd.getDriverVersion());
   System.out.println(dbmd.getUserName());
   System.out.println(dbmd.getDatabaseProductName());
   System.out.println(dbmd.getURL());
   con.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(con != null) con.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   
   public static void main(String[] args) {
   MetaInformation me = new MetaInformation();
   }
   }

ResultSeetMetaData를 이용한 실습

예제 1) 테이블 필드 정보를 출력하는 예제

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.ResultSetMetaData;
   import java.sql.SQLException;
   import java.sql.Statement;
2. 
   public class ResultMetaDynamicColumn {
   public ResultMetaDynamicColumn(String tname) {
   Connection connection = null;
   Statement sqlStatement = null;
   try {
   String driverName = "oracle.jdbc.driver.OracleDriver";
   Class.forName(driverName);
   String dbURL = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   connection = DriverManager.getConnection(dbURL, "scott", "tiger");
   String sql = "SELECT * FROM " + tname;
   sqlStatement = connection.createStatement();
   ResultSet records = sqlStatement.executeQuery(sql);
   ResultSetMetaData metaData = records.getMetaData();
   displayColumns(metaData);
   sqlStatement.close();
   connection.close();
   } catch(Exception e) {
   e.printStackTrace();
   } finally {
   try {
   if(sqlStatement != null) sqlStatement.close();
   if(connection != null) connection.close();
   } catch(SQLException e) {
   e.printStackTrace();
   }
   }
   }
   
   private void displayColumns(ResultSetMetaData metaData) throws SQLException {
   String s = "";
   int count = metaData.getColumnCount();
   for(int i = 1; i <= count; i++) {
   s = s + (metaData.getColumnName(i).length() >= 9 ? metaData.getColumnName(i) : metaData.getColumnName(i) + "\t") + "\t" +
   (metaData.getColumnTypeName(i).length() >= 8 ? metaData.getColumnTypeName(i) : metaData.getColumnTypeName(i) + "\t") + "\t" +
   metaData.getColumnType(i) + "\t";
   switch(metaData.isNullable(i)) {
   case 0:
   s += "null 여부 : null아님\n";
   break;
   case 1:
   s += "null 여부 : null임\n";
   break;
   case 2:
   s += "null 여부 : 알 수 없음\n";
   break;
   }
   }
   System.out.println("결과는 다음과 같습니다 \n" + s);
   }
   
   public static void main(String [] args) {
   new ResultMetaDynamicColumn(args[0]);
   }
   }

예제 2) ResultSetMetaData를 이용한 테이블 조회

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.ResultSetMetaData;
   import java.sql.SQLException;
   import java.sql.Statement;
2. 
   public class ResultMetaDynamicTable {
   public static void main(String [] args) {
   Connection con = null;
   Statement stmt = null;
   try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   stmt = con.createStatement();
   String query = "SELECT * FROM " + args[0];
   ResultSet rs = stmt.executeQuery(query);
   ResultMetaDynamicTable rmdt = new ResultMetaDynamicTable(); // 아래 메소드를 호출하기 위해 객체 생성함 별의미 없음
   rmdt.dispResultSet(rmdt, rs);
   stmt.close();
   con.close();
   } catch(Exception e) {
   e.printStackTrace();
   String errmsg = e.getMessage();
   System.out.println(errmsg);
   }
   }
   
   private void dispResultSet(ResultMetaDynamicTable rmdt, ResultSet rs) throws SQLException {
   int i;
   ResultSetMetaData rsmd = rs.getMetaData(); // 메타데이터를 가져옴
   int numCols = rsmd.getColumnCount(); // 테이블의 컬럼수 많큼 가져온다
   for(i = 1; i <= numCols; i++) {
   System.out.print(rsmd.getColumnLabel(i) + "\t||\t");
   }
   System.out.println();
   while(rs.next()) {
   for(i = 1; i <= numCols; i++) {
   rmdt.dispElement(rs, rsmd.getColumnType(i), i);
   } // for
   System.out.println();
   } // while
   } // method
   
   private void dispElement(ResultSet rs, int columnType, int i) throws SQLException { // columnType 사용안함
   String new_String = new String(rs.getString(i)); // 사용안함
   System.out.print(rs.getString(i) + "\t||\t");
   } // method
   } // class

CallableStatement를 이용한 실습

예제 1) 프로시저를 이용하여 a_emp 테이블의 salary를 변경하는 예제

테이블 생성 및 데이터를 삽입

1. CREATE TABLE a_emp (
   id NUMBER(5)
   , name VARCHAR2(25)
   , salary NUMBER(7,2)
   , title VARCHAR2(25) DEFAULT '사원'
   , in_date DATE DEFAULT SYSDATE
   , dept_name VARCHAR2(25)
   );
2. 
   INSERT INTO a_emp(id, name, salary, dept_name) VALUES(10,'이자바',15000,'교육부');
   INSERT INTO a_emp(id, name, salary, dept_name) VALUES(20,'김멀티',5000,'정보처리부');
   INSERT INTO a_emp(id, name, salary, dept_name) VALUES(30,'박삼성',18000,'총무부');
   INSERT INTO a_emp(id, name, salary, dept_name) VALUES(40,'나신용',7000,'인재개발부');
   INSERT INTO a_emp(id, name, salary, dept_name) VALUES(50,'김보증',7500,'자금부');
   COMMIT;

프로시저

1. CREATE OR REPLACE PROCEDURE sal_inc
   (in1 IN a_emp.name%TYPE,
   in2 IN a_emp.salary%TYPE,
   out1 OUT a_emp.salary%TYPE)
   IS
   new_name a_emp.name%TYPE; -- 변수 선언
   new_sal a_emp.salary%TYPE;
   BEGIN
   SELECT DISTINCT name, salary -- name과 salary를 가져옴
   INTO new_name, new_sal -- 변수에 가져온 값을 넣어줌
   FROM a_emp
   WHERE name = in1; -- 첫번째 인자에 넣어준 in1을 호출함
   
   new_sal := new_sal + in2; -- 조회해서 가져온 sal에 두번째 인자값을 더함
   
   UPDATE a_emp SET salary = new_sal
   WHERE name = in1;
   
   COMMIT;
   
   out1 := new_sal;
   END;
   /

프로그램 실행

show errors -- 에러확인

var aaa number; -- 출력값은 number 타입이므로 aaa 변수를 하나 선언한다

exec sal_inc('이자바',10000,:aaa); -- 프로시저 실행

print aaa; -- 결과 확인

1. import java.sql.CallableStatement;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   import java.sql.Types;
2. 
   public class CallableSalaryInc {
   public static void main(String[] args) throws Exception {
   Connection con = null;
   Statement stmt = null;
   String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora9";
   String user = "scott";
   String psw = "tiger";
   
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection(url, user, psw);
   stmt = con.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT name, salary FROM a_emp");
   CallableStatement cstmt = con.prepareCall("{call sal_inc(?, ?, ?)}");
   cstmt.registerOutParameter(3, Types.DOUBLE);
   while(rs.next()) {
   cstmt.setString(1, rs.getString("name"));
   cstmt.setDouble(2, 1000);
   cstmt.execute();
   System.out.println("실행 ok : " + rs.getString(1) + "\t" + cstmt.getDouble(3));
   }
   stmt.close();
   cstmt.close();
   con.close();
   }
   }

트랜잭션 처리 실습

예제 1) 트랜잭션 자동 처리 예제

1. CREATE TABLE TXTEST1(
   seq VARCHAR2(100) NOT NULL
   , subject VARCHAR2(255)
   , contents VARCHAR2(500)
   );
2. CREATE TABLE TXTEST2(
   seq VARCHAR2(100) NOT NULL
   , subject VARCHAR2(255)
   , contents VARCHAR2(500)
   );

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
2. 
   public class TxTest {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   String seq = null;
   String subject = null;
   String contents = null;
   
   try {
   if(args[0] != null && args[1] != null && args[2] != null) {
   seq = args[0];
   subject = args[1];
   contents = args[2];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java TxTest edu01 test-subjest test-content");
   System.exit(0);
   }
   
   try {
   String query1 = "INSERT INTO txtest1 (seq, subject, contents)" +
   " VALUES (?, ?, ?)";
   String query2 = "INSERT INTO txtest2 (seq, object, contents)" + // 열명이 부적합합니라는 오류를 보기위해 틀리게 함
   " VALUES (?, ?, ?)";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   ps.setString(2, subject);
   ps.setString(3, contents);
   ps.executeUpdate();
   
   ps = con.prepareStatement(query2);
   ps.setString(1, seq);
   ps.setString(2, subject);
   ps.setString(3, contents);
   ps.executeUpdate();
   
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Exception");
   System.out.println(e.toString());
   try {
   con.rollback();
   } catch(Exception se) {
   se.printStackTrace();
   }
   }
   }
   }

예제 2) 트랜잭션 수동 처리 예제

1. import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
2. 
   public class TxTest2 {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   String seq = null;
   String subject = null;
   String contents = null;
   
   try {
   if(args[0] != null && args[1] != null && args[2] != null) {
   seq = args[0];
   subject = args[1];
   contents = args[2];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java TxTest edu01 testsubjest test-content");
   System.exit(0);
   }
   
   try {
   String query1 = "INSERT INTO txtest1 (seq, subject, contents)" +
   " VALUES (?, ?, ?)";
   String query2 = "INSERT INTO txtest2 (seq, object, contents)" +
   " VALUES (?, ?, ?)";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   
   con.setAutoCommit(false);
   
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   ps.setString(2, subject);
   ps.setString(3, contents);
   ps.executeUpdate();
   
   ps = con.prepareStatement(query2);
   ps.setString(1, seq);
   ps.setString(2, subject);
   ps.setString(3, contents);
   ps.executeUpdate();
   
   con.commit(); // 커밋이 이부분에 있으므로 데이터가 둘다 입력되지 않는다.
   
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Exception");
   System.out.println(e.toString());
   try {
   con.rollback(); // 확인 사살용 롤백 실제 커밋전에 에러가 났으므로 이부분에서 해줄게 없다.
   } catch(Exception se) {
   se.printStackTrace();
   }
   }
   }
   }

BLOB와 CLOB를 이용한 실습

예제1) CLOB 데이터 삽입 예제

1. CREATE TABLE CLOBTEST(
   seq VARCHAR2(100) NOT NULL
   , clobdata CLOB
   );

1. import java.io.Writer;
   import java.sql.Clob;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
2. 
   public class ClobTest {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   String seq = null;
   String clobData = null;
   
   try {
   if(args[0] != null && args[1] != null) {
   seq = args[0];
   clobData = args[1];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java ClobTest edu01 test-content");
   System.exit(0);
   }
   
   try {
   String query1 = "INSERT INTO CLOBTEST (seq, clobdata)" +
   " VALUES (?, EMPTY_CLOB())";
   String query2 = "SELECT clobdata" +
   " FROM CLOBTEST" +
   " WHERE seq = ? FOR UPDATE"; // 테이블을 락을 걸어줌
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   con.setAutoCommit(false);
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   ps.executeUpdate();
   ps = con.prepareStatement(query2);
   ps.setString(1, seq);
   rs = ps.executeQuery();
   if(rs.next()) {
   Clob tmpClob = rs.getClob(1);
   if(tmpClob != null) { // 데이타를 입력하기 위한 부분
   Writer writer = ((oracle.sql.CLOB)tmpClob).getCharacterOutputStream(); // writer객체를 통해 연결되있는 CLOB 데이터를 쓴다
   char [] bss = clobData.toCharArray(); // 문자열을 캐릭터 배열에 담아줌
   writer.write(bss); // 담은 문자를 출력
   writer.flush(); // 메모리에 있는걸 써줌
   writer.close();
   }
   }
   
   con.commit();
   System.out.println("clob insert success!");
   rs.close();
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Exception");
   System.out.println(e.toString());
   }
   }
   }

예제 2) CLOB 데이터 조회 예제

1. import java.sql.Clob;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
2. 
   public class ClobTest2 {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   
   String seq = null;
   
   try {
   if(args[0] != null) {
   seq = args[0];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java ClobTest2 edu01");
   System.exit(0);
   }
   
   try {
   String query1 = "SELECT seq, clobdata" +
   " FROM clobtest" +
   " WHERE seq = ?";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   rs = ps.executeQuery();
   if(rs.next()) {
   seq = rs.getString(1);
   Clob tmpClob = rs.getClob(2);
   
   long start = 1;
   int bufferSize = 1024*40;
   StringBuffer total = new StringBuffer();
   String tstr = "";
   
   for(;;) {
   tstr = tmpClob.getSubString(start, bufferSize);
   if((tstr != null) && !tstr.equals("")) {
   total.append(tstr);
   start += bufferSize;
   } else {
   break;
   }
   }
   System.out.println("seq : " + seq);
   System.out.println("clobData : " + total.toString());
   }
   rs.close();
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Excepion");
   System.out.println(e.toString());
   }
   }
   }

예제 3) BLOB 데이터 삽입 예제

1. CREATE TABLE BLOBTEST(
   seq VARCHAR2(100) NOT NULL
   , blobdata BLOB
   );

1. import java.io.File;
   import java.io.FileInputStream;
   import java.io.FileNotFoundException;
   import java.io.OutputStream;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
2. import oracle.jdbc.OracleResultSet;
4. public class BlobTest {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   OracleResultSet rs = null;
   String seq = null;
   try {
   if(args[0] != null) {
   seq = args[0];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java BlobTest edu01");
   System.exit(0);
   }
   
   try {
   String query1 = "INSERT INTO blobtest(seq, blobdata)" +
   " VALUES (?, EMPTY_BLOB())";
   String query2 = "SELECT blobdata FROM blobtest WHERE seq = ? FOR UPDATE";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   con.setAutoCommit(false);
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   ps.executeUpdate();
   ps = con.prepareStatement(query2);
   ps.setString(1, seq);
   rs = (OracleResultSet) ps.executeQuery();
   if(rs.next()) {
   oracle.sql.BLOB tmpBlob = ((OracleResultSet)rs).getBLOB(1);
   
   try{
   File file = new File("BlobTest.java");
   long fileLength = (long) file.length();
   FileInputStream instream = new FileInputStream(file);
   OutputStream outstream = tmpBlob.getBinaryOutputStream();
   
   int size = tmpBlob.getBufferSize();
   byte [] buffer = new byte[size];
   int length = -1;
   while((length = instream.read(buffer)) != -1) {
   outstream.write(buffer, 0, length);
   }
   instream.close();
   outstream.close();
   System.out.println("file insert success!");
   } catch (FileNotFoundException fe) {
   System.err.println("thrown : java.io.FileNotFoundException \n" );
   fe.printStackTrace();
   System.err.println(fe.getMessage());
   }
   }
   con.commit();
   rs.close();
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Exception");
   System.out.println(e.toString());
   }
   }
   }

예제 4) BLOB 데이터 조회 예제

1. import java.io.FileOutputStream;
   import java.io.InputStream;
   import java.sql.Blob;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
2. import oracle.jdbc.OracleResultSet;
3. 
   public class BlobTest2 {
   public static void main(String[] args) {
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   String seq = null;
   try {
   if(args[0] != null) {
   seq = args[0];
   }
   } catch(Exception e) {
   System.out.println("사용방법 : java BlobTest2 edu01");
   System.exit(0);
   }
   
   try {
   String query1 = "SELECT blobdata" +
   " FROM blobtest" +
   " WHERE seq = ?";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora9", "scott", "tiger");
   ps = con.prepareStatement(query1);
   ps.setString(1, seq);
   rs = ps.executeQuery();
   if(rs.next()) {
   Blob b = ((OracleResultSet)rs).getBLOB(1);
   InputStream binstr = b.getBinaryStream();
   FileOutputStream foStream = new FileOutputStream("BlobTest.java.bak");
   byte abyte0 [] = new byte[4096];
   int i;
   while((i = binstr.read(abyte0)) != -1) {
   foStream.write(abyte0, 0, i);
   }
   binstr.close();
   System.out.println("file write success!");
   }
   rs.close();
   ps.close();
   con.close();
   } catch(Exception e) {
   System.out.println("Exception");
   System.out.println(e.toString());
   }
   }
   }
   

끝.