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

Leave a Reply

what does cbd cbd doobies where to buy cbd oil in illinois cbd oil in wisconsin brendan schaub cbd who invented cbd oil will cbd oil show on a drug test hemp oil the same as cbd cbd for addiction best cbd online is hemp oil and cbd the same thing hemp cbd oil legal who has the best cbd oil weaning off suboxone with cbd oil cbd healing salve rent apartment auckland cbd cbd cooking oil lunch sydney cbd cbd store dothan al what does thc and cbd stand for sunmed cbd water soluble best cbd for rls hemp oil extract vs cbd oil best cbd oil to buy on amazon cbd and muscle growth cbd high potency cbd store bridgeville cbd for memory loss why cbd in deodorant cbd american shaman grand island ne organic non gmo cbd oil cbd oil for vision how to know which cbd oil to use cbd oil for back pain reddit water soluble vs oil cbd pacific cbd co reviews diarrhea from cbd oil green mountain cbd coupon

male enlargement pills side effects male enhancement pills near 45225 do any penis pills work pill cialis rail male enhancement formula growing pills foods to help male enhancement does mint mojito male enhancer work natural method for penis enlargement are sex pills waste of money l citrulline supplement strongest male enhancer at cvs extenze blue pill side effects bonza sex pills drug movies 2020 male enhancement nudity nite rider pills amazon zma male enhancement purple stars pills as seen on tv sex pills epic male enhancer legal marine corps male enhancement pill newest penis enlargement techneque male enhancement commercial bob penis enlargement mailing list trial offers erectile dysfunction drugs how to increase penis size no pills potency supplements gas station rhino pills order erectile dysfunction pills men sex enhancement pills dietary supplements best rated sex pills on gastations sildenafil drug interactions canadian drug store viagra enhance mind iq pills easy drugs to make what is viagra tablets used for

tuna diets weight loss lactose free diet weight loss kylie jenner weight loss diet fast food diet weight loss youtube seth rogen weight loss diet premier protein shake diet weight loss extra strength weight loss pills alli weight loss pills cvs best pills to lose weight fast at gnc do caffeine pills help lose weight keto diet fish recipes red snapper miracle weight loss supplement what diet to lose weight fast how to diet and lose weight fast can a vegan diet help you lose weight mechanism of ketogenic diet weight loss best juice diets for weight loss what is quick weight loss center diet best vegetarian diet for weight loss india vegetarian diet weight loss chart jillian weight loss supplements recipes for keto diet dinners clearfield doctor prescribed weight loss pills supplement stacking for weight loss nfl player weight loss pills align weight loss pills revoluthin weight loss pills reviews weight loss muscle gain supplements sapphire weight loss pills best thyroid medicine weight loss the best selling weight loss pills noodle diet for weight loss midsection weight loss diet hydroxy weight loss pills 7 day vegetarian diet plan for weight loss weight loss pills target belly fat keto pills lose weight at walmart weight loss fiber supplements