Java操作Oracle10g的CLOB类型字段

Oracle 10g对CLOB类型的操作进行了相当程度的简化, 一般情况下(不超过32,765字节), 使用新版的ojdbc14.jar驱动, 就可以在Java代码

Oracle 10g对CLOB类型的操作进行了相当程度的简化, 一般情况下(不超过32,765字节), 使用新版的ojdbc14.jar驱动, 就可以在Java代码中和VARCHAR2类型一样用getString和setString进行读写操作, 这给开发带来了很大的便利. 不过当超过32,765字节又该如何处理呢? 这就需要用到Oracle extension APIs了, 在Oracle网站上有一篇相关的技术文档做了说明.

程序初始化JDBC Driver时设置:

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 ); 程序写操作CLOB类型字段: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(); 读操作没有什么区别:.....
// Create a PreparedStatement object
PreparedStatement pstmt = null;

// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;
.......


// Create SQL query statement to retrieve records having CLOB data from
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();

String clobVal = null;

// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
clobVal = rset.getString(1);
System.out.println("CLOB length: "+clobVal.length());
} 这样就可以简便的对大数据量的CLOB字段进行读写了.

，