How to store data in CLOB field in db2 using java code

CLOB is Character Large Objects which is used to store large volume of character data. The default size for CLOB is 1 mb. It can contain up to 2 gigabytes of character data. The data is stored in the database as text and is processed the same way as a CHAR column. JDBC 2.0 provides functions for dealing directly with CLOBs.

Data is stored in CLOB field by setClob or setString method of the PreparedStatement interface and the data is retrieved by the getClob or getString method of ResultSet interface. When reading data,  a block of rows are fetched from the database.  The block of CLOBs are not moved to the ResultSet instead only pointers (LOB locators (4 byte integers)) are moved into the ResultSet.

Now we are going to see how to write CLOB data in a table using code.  For that excise, we have to create a table with CLOB field. In our example , news table is created which is having the fields of News_Id, 
Subject, 
Event_date, 
Involved, 
Location,
News_in_Detail .

 The field News_in_Detail is a Clob field. Clob object can be created using the following ststement

               Clob clob = rset.getClob(columnno);   where rset is the result set of a table containing CLOB field.

In the below example code, 2 records are inserted. One record using pstmt.setString(..) and record another using clob object.

 

package com.javaonline;

import java.sql.*;
import java.lang.StringBuffer;
public class DB2CLOB {
   public static void main(String[] args) 
   throws SQLException 
   {
       try {
        
         // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        
      } catch (Exception e) {
        System.out.println(e);
        System.exit(1); 
      }
       
      Connection con = DriverManager.getConnection("jdbc:db2:test");    
      Statement stmt=null;

   try {     
         stmt = con.createStatement();
       //table creation (One time only)
         stmt.executeUpdate("CREATE TABLE News (News_Id varchar (10) , Subject varchar (50) , Event_date Date,  Involved varchar (100) ,Location varchar(25),  News_in_Detail  CLOB)");
   } catch (Exception e) {
      System.out.println("Tables already created , skipping create table statement");
      }
   
      String newsid, subject, eventdate, involved, location , newsindetail;
      StringBuffer buffer=new StringBuffer(5000);
       int success=0;
     
       PreparedStatement pstmt = con.prepareStatement("INSERT INTO News VALUES(?,?,?,?,?,?)");
       
//Ist record to be inserted
       newsid="3000";
       subject="Sports Cricket";
       eventdate= "2011-05-20";
       involved="England , WI";
       location="London";
       buffer.append("Date : 20/05/2011, In the first match of the tri series, England won the toss and elected to bat and put score of 250 in 50 overs losing 8 wickets ...."  );
       newsindetail = buffer.toString();
       
        pstmt.setString(1, newsid);
        pstmt.setString(2, subject);
        pstmt.setString(3, eventdate);
        pstmt.setString(4, involved);
        pstmt.setString(5, location);
        pstmt.setString(6, newsindetail);
         success =    pstmt.executeUpdate();
         
        if(success>=1)  System.out.println("1 Row Inserted");
        
//IInd record to be inserted using clob object. 
        
        newsid="3001";
        subject="Bio-Data";
        eventdate= "2011-01-20";
        involved="Sachin";
        location="India";
        buffer= new StringBuffer("Sachin is one of best the batsman in India. He is having  wonderful records in Test Matches  as well as Oneday matches ...."  );
        newsindetail = buffer.toString();     
      
        ResultSet rset = stmt.executeQuery("SELECT * FROM News");
        rset.next();
        Clob clob = rset.getClob(6);  // clob object is created
        System.out.println("News= "+clob.getSubString(1,(int)clob.length()-1)); //printing clob value
        //clob.truncate((long) 15000);  // new length can be assigned
        int nocw= clob.setString((long)18,newsindetail); // New string is stored
        
        pstmt.setString(1, newsid);
        pstmt.setString(2, subject);
        pstmt.setString(3, eventdate);
        pstmt.setString(4, involved);
        pstmt.setString(5, location);
        pstmt.setClob(6, clob);  // using setClob 
          success =    pstmt.executeUpdate();
          
        if(success>=1)  System.out.println("1 Row Inserted"); 
      con.close(); // Connection closed 
   }
}