JDBC API for LOBs

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.

See Also:

Working with LOBs and BFILEs

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.

prop.setProperty("oracle.jdbc.defaultLobPrefetchSize","64000");

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

((OracleStatement)stmt).setLobPrefetchSize(100000);

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*/
      32000);

Note:

About Prefetching LOB Data

Table 8-3 JDBC methods for LOBs

Category	Function / Procedure	Description
Miscellaneous	`empty_lob()`	Creates an empty LOB
Miscellaneous	`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);
  try(
      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");
    rs.next();
    c1 = rs.getCLOB(1);
    OracleClob c11 = (OracleClob)c1;
    rs.close();
 
    /*------------------------------------------------------------------*/
    /*---------------------- Sanity Checking ---------------------------*/
    /*------------------------------------------------------------------*/
    if (c11.isSecureFile())
      System.out.println("C1 is a Securefile LOB");
    else
      System.out.println("C1 is a Basicfile LOB");
 
 
    /*------------------------------------------------------------------*/
    /*----------------------- Open/Close -------------------------------*/
    /*------------------------------------------------------------------*/
 
    /*----------------------- Opening a CLOB ---------------------------*/
    c11.open(LargeObjectAccessMode.MODE_READONLY);
 
    /*-------------- Determining Whether a CLOB Is Open ----------------*/
    if (c11.isOpen())
      System.out.println("C11 is open!");
    else
      System.out.println("C11 is not open");
 
    /*----------------------- Closing a CLOB ---------------------------*/
    c11.close();
 
    /*-------------------------------------------------------------------*/
    /*-------------------- 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();
    in.read(readBuffer,0,5);
    in.close();
    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");
    rs.next();
    c2 = rs.getClob(1);
    OracleClob c22 = (OracleClob)c2;
 
    /*-------------------- Write to a CLOB ----------------------------*/
    c22.open(LargeObjectAccessMode.MODE_READWRITE);
    c2.setString(3,"modified");
    String msubs = c2.getSubString(1, 15);
    System.out.println("Modified LOB substring: " + msubs);
 
    /*-------------------- Truncate a CLOB ----------------------------*/
    c2.truncate(20);
    len = c2.length();
    System.out.println("Truncated LOB len = " + len);
    c22.close();
 
  }
}

Parent topic: Locator Interface for LOBs