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字段进行读写了.

您可能感兴趣的文章: