[예제]오라클 10g에서 CLOB 다루기

오라클자바커뮤니티에서 설립한  개발자실무교육6년차 오엔제이프로그래밍 실무교육센터

(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링, Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#, ASP.Net)   www.onjprogramming.co.kr 

- 방법1
setString()함수이용
connectionString에 setBigStringTryClob=true로 설정해야 함

- 방법2
preparedStatment --> OraclePreparedstatment로 변경
setStringForClob() 이용
 
==============================================

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.util.Properties;
.......... 

// Load the database details into the variables.
String url      = "jdbc:oracle:thin:@localhost:1521:orcl";
String user    = "scott";
String password = "tiger";

// Create the properties object that holds all database details
Properties props = new Properties();
props.put("user", user );
props.put("password", password);
props.put("SetBigStringTryClob", "true");

// Load the Oracle JDBC driver class.
DriverManager.registerDriver(new OracleDriver());   

// Get the database connection
Connection conn = DriverManager.getConnection( this.url, this.props );
첫번재 방법
PreparedStatement pstmt = conn.prepareStatement(
                                  "INSERT INTO clob_tab VALUES(?)");

// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// The string data is automatically transformed into a CLOB and
// inserted into the database column.
// Make sure that the Connection property - 'SetBigStringTryClob' is
// set to true for the insert to happen.
pstmt.setString(1, str);
pstmt.executeUpdate();
 
두번재 방법
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
..........

// Create SQL query to insert CLOB data and other columns in the database.
String sql = "INSERT INTO clob_tab VALUES(?)";
     
// Read a big file(larger than 32765 bytes).
// Note: method readFile() not listed here.
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// Create the OraclePreparedStatement object
opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);

// Use the new method to insert the CLOB data (for data greater or lesser than 32 KB)
opstmt.setStringForClob(1,str);

// Execute the OraclePreparedStatement
opstmt.executeUpdate();
...........