Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
5K+ Views
