How To Handle CLOBs Easily in JDBC?

How To Handle CLOBs Easily in JDBC?

Prior to Oracle JDBC 10g, to manipulate the CLOB data in JDBC, Oracle extension class oracle.sql.CLOB was used. But now, Oracle JDBC 10g has a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling of large 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 PreparedStatement and ResultSet objects respectively. By default, 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 Connection property - SetBigStringTryClob can 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 be used 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 may truncate the data or cause errors.

In summary, PreparedStatement.setString() comes handy for processing the CLOB data, by just setting the Connection property 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 Connection property 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 Connection property - SetBigStringTryClob is 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()); }