How to Read data from BLOB and CLOB type columns from a table using JDBC?


Clob datatype

CLOB stands for Character Large Object. in general, an SQL Clob is a built-in datatype which is used to store large amount of textual data. Using this datatype, you can store data up to 2,147,483,647 characters. MYSQL database provides support Clob datatype TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).

Reading Data from a column of datatype Clob

You can read CLOB value (character stream data) from a table using getCharacterStream() or getClob() methods of the ResultSet interface.

These methods accept an integer value representing the index of the required column (or, a String value representing its name) and, reads CLOB data from it.

ResultSet rs = stmt.executeQuery("SELECT * from articles_data");
rs.next();
Clob clob = rs.getClob("File");

Blob datatype

A BLOB is a binary large object that can hold a variable amount of data with a maximum length of 65535 characters.

These are used to store large amounts of binary data, such as images or other types of files. MySQL supports BLOB datatype using TINYBLOB, BLOB, MEDIUMBLOB and, LONGBLOB.

The java.sql.Blob interface of the JDBC API represents the BLOB datatype. Since the Blob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL BLOB (not the data).

Reading Data from a column of Blob datatype

You can read BLOB value (binary data) from a table using the getBinaryStream() or, getBlob() methods of the ResultSet interface.

These methods accept an integer value representing the index of the required column (or, a String value representing its name) and, reads CLOB data from it.

ResultSet rs = stmt.executeQuery("SELECT * from articles_data");
rs.next();
Blob blob = rs.getBlob("image");

Let us create a table with name articles_data in MySQL using the CREATE statement as shown below −

CREATE TABLE articles_data (
   Name VARCHAR(255),
   Article LONGTEXT,
   Logo Blob);

Following JDBC program inserts a records into the articles_data table, retrieves it back and, stores the contents of the columns: article and logo as file and image respectively and, specifies the paths.

Example

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class ReadingDataFromBlobandClob {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampledatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Inserting values
      String query = "INSERT INTO articles_data(Name, Article, Logo) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      FileReader fileReader = new FileReader("E:\\images\\javafx_contents.txt");
      pstmt.setClob(2, fileReader);
      InputStream inputStream = new FileInputStream("E:\\images\\javafx_logo.jpg");
      pstmt.setBlob(3, inputStream);
      pstmt.execute();
      System.out.println("Record inserted......");
      //Retrieving the results
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * from articles_data");
      while(rs.next()) {
         String name = rs.getString("Name");
         Clob clob = rs.getClob("Article");
         Blob blob = rs.getBlob("Logo");
         System.out.println("Name: "+name);
         System.out.println("Clob value: "+clob);
         System.out.println("Blob value: "+blob);
         System.out.println("");
         System.out.print("Clob data is stored at: ");
         //Storing clob to a file
         int i, j =0;
         Reader r = clob.getCharacterStream();
         String filePath = "E:\\output\\"+name+"_article_content.txt";
         FileWriter writer = new FileWriter(filePath);
         while ((i=r.read())!=-1) {
               writer.write(i);
         }
         writer.close();
         System.out.println(filePath);
         j++;
         System.out.print("Blob data is stored at: ");
         InputStream is = blob.getBinaryStream();
         byte byteArray[] = new byte[is.available()];
         is.read(byteArray);
         filePath = "E:\\output\\"+name+"_article_logo.jpg";
         FileOutputStream outPutStream = new FileOutputStream(filePath);
         outPutStream.write(byteArray);
         System.out.println(filePath);
      }
   }
}

Output

Connection established......
Record inserted......
Name: JavaFX
Clob value: com.mysql.jdbc.Clob@18ef96
Blob value: com.mysql.jdbc.Blob@6956de9
Clob data is stored at: E:\output\JavaFX_article_content.txt
Blob data is stored at: E:\output\JavaFX_article_logo.jpg

If you verify the contents of the files at specified paths you can find as shown below −


raja
Published on 04-Jul-2019 15:58:25
Advertisements