import java.io.*;
import java.sql.*;
import oracle.sql.CLOB;
public class LOBSample {
private Connection conn = null;
private final String URL = "jdbc:oracle:thin:@localhost:1521:ias904";
private final int id = 1;
public LOBSample( ) throws SQLException {
DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
conn = DriverManager.getConnection( URL, "scott", "tiger" );
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 ) {
} 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( );
pstmt = conn.prepareStatement( insertlocatorsql );
pstmt.setInt( 1, id );
pstmt.executeUpdate( );
pstmt.close( );
System.out.println("Inserted LOB Locator ");
pstmt = conn.prepareStatement( insertclobsql );
pstmt.setInt( 1, id );
rset = pstmt.executeQuery( );
if( rset.next( ) ) {
CLOB clob = ( CLOB ) rset.getClob( 1 );
String stringdata = this.getLongString( 5000, 'A' );
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( );
char[] buffer = new char[ clob.getBufferSize( ) ];
int read = 0;
int bufflen = buffer.length;
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( );
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("");
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( ) ) {
CLOB clob = ( CLOB ) rset.getClob( 1 );
String stringdata = this.getLongString( 5000, 'B' );
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 );
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( ) );
}
}
}