import java.io.*;
import java.sql.*;
import oracle.sql.CLOB;

/**
 * This class illustrates how to insert, update and access LOB columns in 
 * Oracle database. We will use streams to access LOB data, using streams sheilds 
 * us from out of memory errors and its the most efficient way of accessing LOB.
 * 
 */
public class LOBSample  {

  private Connection conn = null;

  // Alter JDBC URL to you database parameters
  // jdbc:oracle:thin:@dbhostname:port:dbSID
  private final String URL = "jdbc:oracle:thin:@localhost:1521:ias904";
  
  // CLOB data will be inserted,updated using this id
  private final int id = 1;

  public LOBSample( ) throws SQLException {

    // Register Oracle Driver
    DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
    // Get Connection
    conn = DriverManager.getConnection( URL, "scott", "tiger" );    
    // Set AutoCommit to false, LOB operations are faster 
    conn.setAutoCommit( false );
  }

  public static void main( String[] args ) throws SQLException {
    LOBSample sample = new LOBSample( );
    try {
      sample.createCLOBTable( );
      sample.insertCLOB( );
      sample.viewCLOB( );
      sample.updateCLOB( );
      sample.viewCLOB( );
    } catch( Exception ex ) {
      System.out.println(ex);
    } finally {
      sample.cleanup( );    
    } 
  }

  public void createCLOBTable() {
    Statement stmt = null;
    try {

      String createtablesql = new StringBuffer( " CREATE TABLE LOBTable ( ")
                                       .append( "  ID     NUMBER(3), ")
                                       .append( "  LOBCOL CLOB " )
                                       .append( " ) ").toString( );

      stmt = conn.createStatement( );

      stmt.execute( createtablesql );         
      System.out.println("Created Table LOBTable ");
      conn.commit( );
      
    } catch( SQLException sqlEx ) {
      // Ignore error, table might be there
      // System.out.println(sqlEx);
    } finally {
      try {
        if( stmt != null ) stmt.close( );
      } catch( SQLException sEx ) {
        System.out.println(" Error closing stmt : " + sEx.toString( ) );
      }
    }
  }

  public void insertCLOB() {
  
    PreparedStatement pstmt = null;

    ResultSet rset = null;
    try {

      String insertlocatorsql = 
           new StringBuffer( " INSERT INTO LOBTable  ")
                    .append( "  VALUES( ? , empty_clob() ) ")
                    .toString( );

      String insertclobsql = 
           new StringBuffer( " SELECT LOBCol   ")
                    .append( "  FROM LOBTable  ")
                    .append( "    WHERE ID = ? FOR UPDATE ")
                    .toString( );

      // Insert CLOB locator
      pstmt = conn.prepareStatement( insertlocatorsql );
      
      pstmt.setInt( 1, id );
      
      pstmt.executeUpdate( );                                 
      
      pstmt.close( );
      
      System.out.println("Inserted LOB Locator ");      

      // Get reference CLOB locator and insert actual data
      pstmt = conn.prepareStatement( insertclobsql );
      
      pstmt.setInt( 1, id );
      
      rset = pstmt.executeQuery( );                                 
      
      if( rset.next( ) ) {

        // Get CLOB locator
        CLOB clob = ( CLOB ) rset.getClob( 1 );
       
        String stringdata = this.getLongString( 5000, 'A' );

        // Write data into CLOB
        populateCLOB( clob, stringdata );
        
        conn.commit( );   

        System.out.println("Inserted LOB Data ");

      } else {
        System.out.println(" no rows ");
      }
    } catch( Exception ex ) {
    
      System.out.println( "Error inserting clob data" + ex.toString( ) );
      
    } finally {
    
      try {
        if( rset != null ) rset.close( );
        if( pstmt != null ) pstmt.close( );
      } catch( SQLException sEx ) {
        System.out.println(" Error closing rset/pstmt: " + sEx.toString( ) );
      }
      
    }
    
  }


  public void populateCLOB( CLOB clob, String data ) throws IOException, SQLException {

    System.out.println("");
    System.out.println("Writing into CLOB, data of length : " + data.length( ) );
    System.out.println("");

    StringReader reader = new StringReader( data );

    Writer clobWriter = clob.getCharacterOutputStream( );
    
    // Get optimal buffer size to read/write data  
    char[] buffer = new char[ clob.getBufferSize( ) ];
    int read = 0;
    int bufflen = buffer.length;

    // Read from String and write to CLOB
    while( (read = reader.read(buffer,0,bufflen)) > 0 ) {
      clobWriter.write( buffer, 0, read );
    }
    
    clobWriter.close( );
    reader.close( );

  }

  private void viewCLOB( ) {

    PreparedStatement pstmt = null;

    ResultSet rset = null;
    try {

      String selectclobsql = 
           new StringBuffer( " SELECT LOBCol   ")
                    .append( "  FROM LOBTable  ")
                    .append( "    WHERE ID = ? ")
                    .toString( );

      pstmt = conn.prepareStatement( selectclobsql );

      pstmt.setInt( 1, id );

      rset = pstmt.executeQuery( ); 

      if( rset.next( ) ) {
        CLOB clob = ( CLOB ) rset.getClob( 1 );

        Reader clobReader = clob.getCharacterStream( );

        // Get optimal size to read/write data and initialze buffer
        char[] buffer = new char[ clob.getBufferSize( ) ];
        int read = 0;
        int bufflen = buffer.length;
        
        System.out.println("");
        System.out.println("Displaying contents of LOB ");
        System.out.println("");
  
        // Read from CLOB and write to standard output
        while( (read = clobReader.read(buffer,0,bufflen)) > 0 ) {
          System.out.print( new String( buffer, 0, read ) );
        }
          
        clobReader.close( );

      } else {
        System.out.println(" no rows ");
      }
                  
    } catch( Exception ex ) {
    
      System.out.println( "Error selecting clob data" + ex.toString( ) );
      
    } finally {
    
      try {
        if( rset != null ) rset.close( );
        if( pstmt != null ) pstmt.close( );
      } catch( SQLException sEx ) {
        System.out.println(" Error closing rset/pstmt: " + sEx.toString( ) );
      }
      
    }

  }

  public void updateCLOB( ) {

    PreparedStatement pstmt = null;

    ResultSet rset = null;
    try {

      String updateclobsql = 
           new StringBuffer( " SELECT LOBCol   ")
                    .append( "  FROM LOBTable  ")
                    .append( "    WHERE ID = ? FOR UPDATE ")
                    .toString( );

      pstmt = conn.prepareStatement( updateclobsql );
      pstmt.setInt( 1, id );
      rset = pstmt.executeQuery( );                                 
      if( rset.next( ) ) {
        // Get CLOB locator
        CLOB clob = ( CLOB ) rset.getClob( 1 );
        String stringdata = this.getLongString( 5000, 'B' );
        // Write to CLOB
        populateCLOB( clob, stringdata );
        
        conn.commit( );                
        
        System.out.println("Updated LOB Data ");
        
      } else {
        System.out.println(" no rows ");
      }
            
    } catch( Exception ex ) {
    
      System.out.println( "Error updating clob data" + ex.toString( ) );
      
    } finally {
    
      try {
        if( rset != null ) rset.close( );
        if( pstmt != null ) pstmt.close( );
      } catch( SQLException sEx ) {
        System.out.println(" Error closing rset/pstmt: " + sEx.toString( ) );
      }
      
    }
    
  }

  public String getLongString( long len, char c ) {
    StringBuffer sb = new StringBuffer( );
    for( long i=0; i < len; i++ )
      sb.append( c );
    // System.out.println( " Returning String data of length : " + sb.length( ) );  

    return sb.toString();
  }

  public void cleanup( ) {
    try {
      if( conn != null ) conn.close();
      System.out.println("");
      System.out.println("Connection closed ");
      System.out.println("");
    } catch( SQLException sqlEx ) {
      System.out.println( "Error closing connection " + sqlEx.toString( ) );
    }
  }
  
}