在JDBC中Oracle中CLOB的使用

ethvkvuang

Prior to Oracle JDBC 10g
, to manipulate the CLOB      data in JDBC, Oracle extension class oracle.sql.CLOBwas used. But now, Oracle JDBC 10ghas a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling oflarge      data using some of the available standard APIs
, instead of using the      Oracle extension classes. These could be thought as shortcuts for inserting      and retrieving CLOB data from the database.
The enhanced standard APIs for manipulating CLOBs are
the      setString()and getString()of the PreparedStatementand ResultSetobjects respectively. Bydefault, the method preparedStatement.setString()will allow processing of the strings up to 32765 bytes. In order to insert      data greater than 32765 bytes, a newly introduced Connectionproperty - SetBigStringTryClobcan be set. This      forces the preparedStatement.setString()to      use another newly introduced method, OraclePreparedStatement.setStringForClob()instead. This is actually done internally, transparent to the user.
Nevertheless, the newly introduced method OraclePreparedStatement.setStringForClob()alone can also be used instead of the standard APIs.     This method   makes
the check on the data size internally again.
ResultSet.getString()can still beused  to read      the CLOB column. For the  getString()and the      setString()
operations, the size limit for the      string to be read or inserted is the one imposed by Java,that is, a positive      int; the smallest being 0 or 1 byte.
Note: Do not use the setString() to bind large data to VARCHAR and LONG database columns, since it maytruncate      the data or cause errors.

In summary, PreparedStatement.setString()comes handy for processing the CLOB data, by just setting the Connectionproperty SetBigStringTryClob
. However, handling      very large amounts of data this way may not be a
wise;  streaming the data      is a better alternative.
Following is the code snippet to set the Connectionproperty to process large data using the standard APIs. Refer the full source        code in the file: ClobManipulationIn10g.java

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 );

The code snippet to create an Oracle database table with        a CLOB column in it.

// SQL statement CREATE TABLE clob_tab (clob_col CLOB);

　　
Once the Connectionproperty - SetBigStringTryClobis set, use the standard preparedStatement.setString()method for binding data more than 32765 bytes.

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();

　　
Instead of the standard APIs, Oracle extension APIs      can be used. OraclePreparedStatement.setStringForClob()can be used for binding data greater than 32765 bytes.

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(); ...........

　　
Following is the code snippet that demonstrates the
use        of the standard ResultSet.getString()method,        enhanced now to read more than 32765 bytes:

.....
// 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());     
}