Handling CLOBs - Made easy with Oracle JDBC 10g

Date: 17-Mar-2004

This document will help you understand: 

• How to easily handle the CLOB data at the JDBC layer using enhanced APIs available with Oracle JDBC 10g
• How to run the sample Java application demonstrating the easy handling of CLOB data in the JDBC code.

Table of Contents

Introduction
Prerequisites
Software Requirements
How to Handle CLOBs Easily in JDBC?
Running the Sample Java Application
Useful References

Introduction

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing independent providers to implement and extend the standard with their own.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance. For more information about Oracle JDBC, refer the Useful References section in this document.

What are CLOBs?

Basically, LOBs (Large Objects) are designed to support large unstructured data such as text, graphic images, still video clips, full motion video, and sound waveforms. A typical employee record may be a few hundred bytes, but even small amounts of multimedia data can be thousands of times larger. Oracle supports the following two types of LOBs:

• Those stored in the database either in-line in the table or in a separate segment or tablespace, such as BLOB(Binary LOB), CLOB (Character LOB) and, NCLOB (National Character LOB).
  As the name signifies, BLOB holds binary data while the CLOB holds textual data and the NCLOB holds, character data that corresponds to the national character set defined for the Oracle database.
  
• Those stored as operating system files, such as BFILEs.
  

Managing CLOBs and BLOBs in JDBC is not very simple when compared to managing small amount of data. The way the CLOB and the BLOB objects are created and managed is different than a VARCHAR(String) column. In fact, Oracle extension classes are provided to support these types objects in JDBC like oracle.sql.CLOB, oracle.sql.BLOB etc.

The new enhancements in Oracle JDBC 10g promises to simplify the insertion and retrieval techniques for the CLOBs using the standard APIs . This document enumerates how easily the CLOB data can be handled in JDBC, by harnessing the new features of the Oracle JDBC 10g driver.

Prerequisites

To work your way through this HowTo, it is necessary to have a basic understanding of the fundamental concepts of JDBC, and how to use the JDBC APIs to connect to the database for the basic CRUD(Create, Read, Update, and Delete)- the essential database operations. In addition, you need to know the basic Oracle database concepts.

Software Requirements

• Oracle Database 10g . You can download the database here .
• Oracle Database 10g JDBC Driver. This can be downloaded here .
• JDK1.4.x or later that can be downloaded here .

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

Running the Sample Java Application

• Copy the sample Class file ClobManipulationIn10g.java in a directory, say for example, clobs .
• From the clobs directory, set the environment variable CLASSPATH to include the following:
  • Include the JDBC driver class; use ojdbc14.jar with JDK 1.4.x / use classes12.jar/zip with JDK 1.3.x
  • Include the current directory(. ) in the CLASSPATH.
• Create the table 'clob_tab' in an Oracle database under a user say, scott. Refer the code snippet for more details.
• Edit the file ClobManipulationIn10g.java to configure your database details.
  
  
  • Change the following lines in the file in the main() method.

    // Load the database details into the variables.
    String dbUrl = "jdbc:oracle:thin:@<database host machine>:<port>:<SID> ";
    clobManipulationIn10g.user = "scott "; // Replace the username where the table 'clob_tab' was created.
    clobManipulationIn10g.password = "tiger "; // Replace the password of the username.
    
    Replace the values of:
    <database host machine> where the Oracle database is installed.
    <port> port where the database TNS listener listening. The default is 1521.
    <SID> the SID of the database.

• Save the file. Compile the Java file using:
  javac -d . ClobManipulationIn10g.java
• Run the class using the following command. Pass a file name with the complete path as the parameter value for <fileName>. For testing, use a file which is >32 KB of size.
  java oracle.otnsamples.jdbc.ClobManipulationIn10g "<fileName>"
  

  For example, java oracle.otnsamples.jdbc.ClobManipulationIn10g "d:\test\bigFile.txt"
  Depending on the size of the file(say for example, 40kb) that was read, the output will be like the following:

  CLOB length: 40000

 

orginal site:http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html