Recent Posts

Thursday, 15 November 2018

Working with Large Objects - CLOB

Character Large Objects (CLOB)
☀ A CLOB is a collection of Character data stored as a single entity in the database.
☀ CLOB can be used to store large text documents(may plain text or xml documents)
☀ CLOB Type can store maximum of 4GB data.

Steps to insert CLOB type file in the database
     All steps are exactly same as BLOB, except the following differences
1. Instead of FileInputStream, we have to take FileReader.

2. Instead of setBinaryStream() method we have to use setCharacterStream() method.
public void setCharacterStream(int index,Reader r) throws SQLException
public void setCharacterStream(int index,Reader r,int length) throws SQLException
public void setCharacterStream(int index,Reader r,long length) throws SQLException

Program
import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class ClobInsertTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "insert into students values(?,?)"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      pst.setString(1,"Ashok Kumar"); 
      File f = new File("ashok_resume.doc");
      FileReader fr = new FileReader(f);
      ps.setCharacterStream(2,fr);
      System.out.println("file is inserting from :"+f.getAbsolutePath());
      int rowCount = pst.executeUpdate(); 
      if(rowCount == 0) 
         System.out.println("Record Not inserted"); 
      else
         System.out.println("Record inserted"); 
      con.close();
   }
}
Retrieving CLOB Type from Database
     All steps are exactly same as BLOB, except the following differences.
1. Instead of using FileOutputStream,we have to use FileWriter

2.Instead of using getBinaryStream() method we have to use getCharacterStream() method

Program
import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class ClobRetriveTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "select * from students"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      ResultSet rs = pst.executeQuery(); 
      FileOutputStream os = new FileOutputStream("ashok_resume_db.jpeg");
      while(rs.next()) { 
         String name = rs.getString(1); 
         Reader r = rs.getCharacterStream(2);
         int i=r.read();
         while(i != -1)
            fw.write(i);
            i = r.read();
         }
         fw.flush(); 
         System.out.println("Retrieved Successfully file :ashok_resume_db.txt");
      }
      con.close();
   }
}

Next Tutorial : Connection Pooling

Previous Tutorial : Working with Large Objects - BLOB


No comments:

Post a Comment