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