Handling CLOBs

晓楠833

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