DB Tips

오라클 10g에서는 standard API로 clob를 사용할 수 있도록 수정되었다고 합니다.

http://otn.oracle.com/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html?_template=/otn/content/print

참고는 위의 오라클에서 가능합니다.

간단하게 보자면

첫번째 setString()함수로 기존의 다른 int나 String 타입을 preparedStatmemt에 추가하듯이 같은 방법으로 추가 하되 connectionString에 setBigStringTryClob=true를 추가해 줘야 한다고 합니다.

두번째 방법은 표준 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();
...........

오라클에서 퍼왔슴당.