8.3 JDBC API for LOBs

JDBC supports standard Java interfaces java.sql.Clob and java.sql.Blob for CLOBs and BLOBs respectively.

In JDBC, you do not deal with locators but instead use methods and properties in the Java APIs to perform operations on LOBs.

When BLOB and CLOB objects are retrieved as a part of an ResultSet, these objects represent LOB locators of the currently selected row. If the current row changes due to a move operation, for example, rset.next(), then the retrieved locator still refers to the original LOB row. You must call getBLOB(), getCLOB(), or getBFILE() on the ResultSet each time a move operation is made depending on whether the instance is a BLOB, CLOB or BFILE.

Prefetching of LOB Data

When using the JDBC client, the number of server round trips can be reduced by prefetching part of the data and metadata (length and chunk size) along with the LOB locator during the fetch. This applies to persistent LOBs, temporary LOBs, and BFILEs. For small to medium sized LOBs, Oracle recommends setting the prefetch length such that about majority of your LOBs are smaller than the prefetch size.

LOB prefetch size can be set at the session level, and can be overwritten at the statement or the column level. The prefetch size values can be:
  • -1 to disable prefetching
  • 0 to enable prefetching for metadata only
  • any value greater than 0 which represents the number of bytes for BLOBs and characters for CLOBs, to be prefetched along with the locator during fetch operations.
Use prop.setProperty to set the prefetch size for the session. The default session prefetch size is 32k for the JDBC Thin Driver.

You can overwrite the session level default prefetch size at the statement level as follows:


You can use the following code snippet to fetch the prefetch size of a statement:

int pf = ((OracleStatement)stmt).getLobPrefetchSize() ;

You can overwrite the session level default prefetch size at the column level as follows:

((OracleStatement)stmt).defineColumnType(1, OracleTypes.CLOB, /*lobPrefetchSize*/

Table 8-3 JDBC methods for LOBs

Category Function / Procedure Description
Miscellaneous empty_lob() Creates an empty LOB
isSecureFile() Finds out if the BLOB or CLOB locator is a SecureFile
Open/Close open() Open a LOB
isOpen() Check if a LOB is open
close() Close the LOB
Read Operations length() Get the length of the LOB
getChunkSize() Get the optimum read/write size
getBytes() Read data from the BLOB starting at the specified offset
getBinaryStream() Streams the BLOB as a binary stream
getChars() Read data from the CLOB starting at the specified offset
getCharacterStream() Streams the CLOB as a character stream
getAsciiStream() Streams the CLOB as an ASCII stream
getSubString() Return part of the LOB value starting at the specified offset
position() Return the matching position of a pattern in a LOB
Modify Operations setBytes() Write data to the BLOB at a specified offset
setBinaryStream() Sets a binary stream that can be used to write to the BLOB value
setString() Write data to the CLOB at a specified offset
setCharacterStream() Sets a character stream that can be used to write to the CLOB value
setAsciiStream() Sets an ASCII stream that can be used to write to the CLOB value
truncate() Trim the LOB value to the specified shorter length
Operations involving multiple locators dst = src Assign LOB locator src to LOB locator dst

Example 8-3 JDBC API for LOBs

static void jdbc_lob_apis() throws Exception {
  System.out.println("Persistent LOBs Test in JDBC "+ TYPE);
      Connection con = getConnection();
      Statement       stmt = con.createStatement();
    ResultSet   rs      = null;
    Clob  c1      = null;
    Clob  c2      = null;
    Reader      in      = null;
    long        pos     = 0;
    long        len     = 0;
    rs = stmt.executeQuery("select ad_sourcetext from print_media where product_id = 1");
    c1 = rs.getCLOB(1);
    OracleClob c11 = (OracleClob)c1;
    /*---------------------- Sanity Checking ---------------------------*/
    if (c11.isSecureFile())
      System.out.println("C1 is a Securefile LOB");
      System.out.println("C1 is a Basicfile LOB");
    /*----------------------- Open/Close -------------------------------*/
    /*----------------------- Opening a CLOB ---------------------------*/
    /*-------------- Determining Whether a CLOB Is Open ----------------*/
    if (c11.isOpen())
      System.out.println("C11 is open!");
      System.out.println("C11 is not open");
    /*----------------------- Closing a CLOB ---------------------------*/
    /*-------------------- Reading from a LOB ---------------------------*/
    /*------------------------ Get CLOB Length -------------------------*/
    len = c1.length();
    System.out.println("CLOB length = " + len);
    /*------------------------ Reading CLOB Data -----------------------*/
    char[] readBuffer = new char[6];
    in = c1.getCharacterStream();
    String lobContent = new String(readBuffer);
    System.out.println("Buffer with LOB contents: " + lobContent);
    /*----------------------- Substr of a CLOB -------------------------*/
    String subs = c1.getSubString(2, 5);
    System.out.println("LOB substring: " + subs);
    /*----------------------- Search for a pattern  --------------------*/
    pos = c1.position("aaa", 1);
    System.out.println("Pattern matched at position = " + pos);
    /*-------------------- Modifying a LOB -----------------------------*/
    rs = stmt.executeQuery("select ad_sourcetext from print_media where product_id = 1 for update");
    c2 = rs.getClob(1);
    OracleClob c22 = (OracleClob)c2;
    /*-------------------- Write to a CLOB ----------------------------*/
    String msubs = c2.getSubString(1, 15);
    System.out.println("Modified LOB substring: " + msubs);
    /*-------------------- Truncate a CLOB ----------------------------*/
    len = c2.length();
    System.out.println("Truncated LOB len = " + len);