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.

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

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

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

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

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