Data Interface for LOBs in Java

8.3 Data Interface for LOBs in Java

This section discusses the usage of data interface for LOBs in Java.

You can read and write CLOB and BLOB data using the same streaming mechanism as for LONG and LONG RAW data.

For read operations, use the defineColumnType(nn, Types.LONGVARCHAR) method or the defineColumnType(nn, Types.LONGVARBINARY) method on the persistent or temporary LOBs returned by the SELECT statement. This produces a direct stream on the data that is similar to VARCHAR2 or RAW column.

Note:

If you use VARCHAR or RAW as the defineColumnType, then the selected value will be truncated to size 32k.
Standard JDBC methods such as getString or getBytes on ResultSet and CallableStatement are not part of the Data Interface as they use the LOB locator underneath.

To insert character data into a LOB column in a PreparedStatement, you may use setBinaryStream(), setCharacterStream(), or setAsciiStream() for a parameter which is a BLOB or CLOB. These methods use the stream interface to create a LOB in the database from the data in the stream. If the length of the data is known, for better performance, use the versions of setBinaryStream() or setCharacterStream functions which accept the length parameter. The data interface also supports standard JDBC methods such as setString or setBytes on PreparedStatement to write LOB data. It is easier to code, and in many cases faster, to use these APIs for LOB access. All these techniques reduce database round trips and result in improved performance in many cases.

The following code snippets work with all JDBC drivers:

Bind:

This is for the non-streaming mode:

...
String sql = "insert into print_media (product_id, ad_id, ad_final_text)" +
             " values (:1, :2, :3)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt( 1, 2 );
    pstmt.setInt( 2, 20);
    pstmt.setString( 3, "Java string" );
    int rows = pstmt.executeUpdate();
...

Note:

Oracle supports the non-streaming mode for strings of size up to 2 GB, but your machine's memory may be a limiting factor.

For the streaming mode, the same code as the preceding works, except that the setString() statement is replaced by one of the following:

pstmt.setCharacterStream( 3, new LabeledReader(), 1000000 );
pstmt.setAsciiStream( 3, new LabeledAsciiInputStream(), 1000000 );

Note:

You can use the streaming interface to insert Gigabyte sized character and binary data into a LOB column.

Here, LabeledReader() and LabeledAsciiInputStream() produce character and ASCII streams respectively. If ad_finaltext were a BLOB column instead of a CLOB, then the preceding example works if the bind is of type RAW:

pstmt.setBytes( 3, <some byte[] array> );
pstmt.setBinaryStream( 3, new LabeledInputStream(), 1000000 );

Here, LabeledInputStream() produces a binary stream.

Define:

For non-streaming mode:

OracleStatement stmt = (OracleStatement)(conn.createStatement());
  stmt.defineColumnType( 1, Types.VARCHAR );
  ResultSet rst = stmt.executeQuery("select ad_finaltext from print_media" );
  while( rst.next() )
     {
       String s = rst.getString( 1 );
       System.out.println( s );
     }

Note:

If the LOB size is greater than 32767 bytes, the data is truncated and no error is thrown.

For streaming mode:

OracleStatement stmt = (OracleStatement)(conn.createStatement());
  stmt.defineColumnType( 1, Types.LONGVARCHAR );
  ResultSet rst = stmt.executeQuery("select ad_finaltext from print_media" );
  while(rs.next()) {  
    Reader reader = rs.getCharacterStream( 1 );  
    int data = 0;  
    data = reader.read();   
    while( -1 != data ){    
      System.out.print( (char)(data) );    
      data = reader.read();  
      }  
    reader.close();
    }

Note:

Specifying the datatype as LONGVARCHAR lets you select the entire LOB. If the define type is set as VARCHAR instead of LONGVARCHAR, the data will be truncated at 32k.

If ad_finaltext were a BLOB column instead of a CLOB, then the preceding examples work if the define is of type LONGVARBINARY:

...
OracleStatement stmt = (OracleStatement)conn.createStatement();

stmt.defineColumnType( 1, Types.INTEGER );
stmt.defineColumnType( 2, Types.LONGVARBINARY );
 
ResultSet rset = stmt.executeQuery("SELECT ID, LOBCOL FROM LOBTAB");
	while(rset.next())
	{
     		/* using getBytes() */
     		/*
     		byte[] b = rset.getBytes("LOBCOL");
		System.out.println("ID: " + rset.getInt("ID") + "  length: " + b.length);
     		*/
 
	        /* using getBinaryStream() */
        	InputStream byte_stream = rset.getBinaryStream("LOBCOL");
        	byte [] b = new byte [100000];
	        int b_len = byte_stream.read(b);
        	System.out.println("ID: " + rset.getInt("ID") + "  length: " + b_len);
 
	        byte_stream.close();
    	}
...