How to write CLOB fields

Below code shows the way to write CLOB fields to the database:

import java.io.ByteArrayInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author http://java-sample-program.blogspot.in/  
 * Write CLOB to database
 */
public class ClobTest {

 public static void main(String[] args) throws Exception{
 Connection con = null;
 CallableStatement stmt = null;
 
 try {
 
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
    "username","password");
  stmt = con.prepareCall("{call CLOBTEST.saveClob(?)}");
  String xmlString = "1234567890";
  
  stmt.setAsciiStream(1,new ByteArrayInputStream(xmlString.getBytes()),
     xmlString.getBytes().length);
  
  stmt.execute();
  System.out.println("executed successfully");
  
 }catch(Exception e){
  e.printStackTrace();
 } finally {
  try{
   if(stmt != null){ 
    stmt.close();
   }
   if(con!= null){
    con.close();     
   }
  }catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 }

}

CLOB to String Conversion

Below code shows the two ways to convert database CLOB to String code.

1) Convert CLOB to String using BufferedReader:

import java.io.BufferedReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author http://java-sample-program.blogspot.in/  
 * Convert CLOB to String using BufferedReader
 */
public class ClobTest {

 public static void main(String[] args) throws Exception{
 Connection con = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 
 try {
 
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
    "username","password");
  pstmt = con.prepareStatement("SELECT clobCol FROM TableName");
  rs = pstmt.executeQuery();
  
  while(rs.next()){   
   
   StringBuffer strOut = new StringBuffer();
   String aux;
   try {
    BufferedReader br = new BufferedReader(rs.getClob("clobCol").
      getCharacterStream());
    while ((aux=br.readLine())!=null) {
     strOut.append(aux);
     strOut.append(System.getProperty("line.separator"));
    }
   }catch (Exception e) {
    e.printStackTrace();
   }
   String clobStr = strOut.toString();
   System.out.println(clobStr);
  } 
  
 }catch(Exception e){
  e.printStackTrace();
 } finally {
  try{
   if(pstmt != null){ 
    pstmt.close();
   }
   if(rs!= null){ 
    rs.close();
   }     
   if(con!= null){
    con.close();     
   }
  }catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 }
 
}

Use the above method if a very large Clob can be returned by the database.

2) Convert CLOB to String using getSubString() method:

import java.io.BufferedReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author http://java-sample-program.blogspot.in/  
 * Convert CLOB to String using getSubString() method
 */
public class ClobTest {

 public static void main(String[] args) throws Exception{
 Connection con = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 
 try {
 
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
    "username","password");
  pstmt = con.prepareStatement("SELECT clobCol FROM TableName");
  rs = pstmt.executeQuery();
  
  while(rs.next()){   
   Clob clob = rs.getClob("clobCol");
   if (clob != null) {  
    String clobStr = clob.getSubString(1, (int) clob.length());
    System.out.println(clobStr);
   }
  } 
  
 }catch(Exception e){
  e.printStackTrace();
 } finally {
  try{
   if(pstmt != null){ 
    pstmt.close();
   }
   if(rs!= null){ 
    rs.close();
   }     
   if(con!= null){
    con.close();     
   }
  }catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 }
 
}