CLOB

CLOB stands for character large object, it can store upto 2,147,483,647 characters(For Oracle). A CLOB is used to store unicode character-based data, such as large documents in any character set.

According to the Mysql, there are four text types TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT which can be taken as CLOB type and have the maximum lengths and storage requirements.

Type	Length
TINYTEXT	255 characters
TEXT	16,535 characters
MEDIUMTEXT	16,777,216 characters
LONGTEXT	4294967295 characters

How to create a CLOB Object

to create CLOB use Connection.createBlob

   Ex:

   Blob b1 = conn.createClob();

How to write CLOB data to Database

to write CLOB to DB use PreparedStatement, setClob method

   Ex:

   PreparedStatement ps = conn.prepareStatement("INSERT INTO employee values (?, ?, ?)");

   prepStmt.setClob(3, b1);

   prepStmt.execute();

How to read CLOB data from Database

to read CLOB from DB use ResultSet.getClob

   Ex:

   /* read and print the Blob data */

   Clob b = rs.getClob("resume");

   Reader in = b.getCharacterStream();

   int a;

   while((a = in.read())!= -1){

      System.out.print((char)a);

   }

/* Step 1: Import sql package */
import java.sql.*;
import java.io.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException, IOException{     
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        PreparedStatement prepStmt;
        Clob b1 = conn.createClob();
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        
        prepStmt = conn.prepareStatement("INSERT INTO employee values (?, ?, ?)");
        stmt.execute("CREATE TABLE employee (id int, name varchar(30), resume LONGTEXT, PRIMARY KEY(id))");
        
        prepStmt.setInt(1, 1);
        prepStmt.setString(2, "Krishna");
        
        /* Read the file specified in the location */
        System.out.println("Enter resume location");
        String location = br.readLine();
        
        /* Logic to read the file and set the blob value*/
        BufferedReader reader = new BufferedReader(new FileReader(location));
        String line;
        StringBuilder buf = new StringBuilder();
        while ((line = reader.readLine()) != null) {
           buf.append(line);
           System.out.println(line);
 }
        b1.setString(1, buf.toString());
        
        /* Set the Clob object */
        prepStmt.setClob(3, b1);
        prepStmt.execute();
        
        String query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        System.out.println("Selecting data");
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
            
            /* read and print the Blob data */
            Clob b = rs.getClob("resume");
            Reader in = b.getCharacterStream();
            int a;
            while((a = in.read())!= -1){
                System.out.print((char)a);
            }
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Sample Output

Loading/Registering driver
Connecting to database
Enter resume location
C:\Hari\resume.txt
A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.

There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.
Selecting data
1 Krishna
A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.Dropping table employee

lets say 'resume.txt' contains below data.

A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.

There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.

Prevoius                                                 Next                                                 Home