CLOB

CLOB

In this tutorial, we are going to discuss about CLOB (Character Large Object). A CLOB (Character Large Object) is a data type used to store large amounts of character data in a database, typically text data such as documents, articles, or large textual content. CLOBs are used in database systems to handle data that exceeds the size limits of traditional VARCHAR or TEXT data types.

  • 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.
CLOB 1
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

E.g

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
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();
   }
}

Here’s an overview of CLOBs:

1. Characteristics

  • Large Capacity: CLOBs can store a vast amount of character data, ranging from megabytes to gigabytes, depending on the database system and configuration.
  • Textual Data: CLOBs are designed to store textual data, such as paragraphs, documents, or other large strings of characters.
  • Binary Safe: CLOBs are binary-safe, meaning they can store any character data, including special characters, Unicode characters, and binary data, without data loss.

2. Usage

  • Text Storage: CLOBs are commonly used to store large text documents, articles, blog posts, or any other textual content that exceeds the size limits of traditional text data types.
  • BLOB Equivalence: In many database systems, CLOBs are complemented by BLOBs (Binary Large Objects), which are used to store binary data such as images, videos, or other binary content.

3. Operations

  • Insertion and Retrieval: CLOBs can be inserted into and retrieved from the database using SQL statements or database APIs. When inserting CLOB data, the text content is typically provided as a parameter to the INSERT statement.
  • Manipulation: Database systems provide functions and operations for manipulating CLOB data, such as substring extraction, concatenation, search, and replace operations.
  • Streaming: Some database APIs support streaming mechanisms for reading and writing large CLOB data in chunks, which can be more efficient for handling extremely large CLOBs.

4. Considerations

  • Storage Requirements: Storing large amounts of textual data as CLOBs can consume significant database storage space, so it’s essential to consider storage requirements and optimize data storage where possible.
  • Performance: Operations involving CLOBs, especially large ones, may have performance implications due to the size of the data being processed. Careful indexing and query optimization may be necessary for efficient retrieval and manipulation of CLOB data.

In summary, CLOBs are a valuable data type in relational databases for storing large amounts of character data efficiently. They provide a means to handle large textual content in database applications while ensuring data integrity and consistency. However, it’s essential to consider storage requirements, performance implications, and database-specific considerations when working with CLOB data.

That’s all about the CLOB in JDBC. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

CLOB
Scroll to top