CLOB and BLOB data types in JDBC
In JDBC, CLOB (Character Large Object) and BLOB (Binary Large Object) are data types used to store large amounts of data in a database. They are designed to handle different types of large data:
1. CLOB (Character Large Object):
- Definition: CLOB is used to store large amounts of character data. It can hold text data such as large documents, HTML, XML files, etc.
- Storage: CLOB can store data in character format and is used when the data to be stored is textual.
- Size: Can store up to 2GB of character data (depends on the database system).
2. BLOB (Binary Large Object):
- Definition: BLOB is used to store large amounts of binary data. It can hold multimedia files such as images, videos, audio files, etc.
- Storage: BLOB can store data in binary format and is used when the data to be stored is binary CLOB and BLOB data types.
- Size: Can store up to 2GB of binary data (depends on the database system).
Let’s illustrate how to use CLOB and BLOB data types in JDBC with a Java example where we store and retrieve large text and binary data from a database.
Table of Contents
Database Table Creation
Database Table Creation
Before running the Java code, create a table in your database.
sql
CREATE TABLE files (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
file_type VARCHAR(50),
file_data LONGBLOB, -- For BLOB data
file_text LONGTEXT -- For CLOB data
);
Java Program Using CLOB and BLOB
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.FileReader;
import java.io.Writer;
import java.io.FileWriter;
public class ClobBlobExample {
public static void main(String[] args) {
// JDBC URL, username, and password of MySQL server
String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
String username = "root";
String password = "password";
// JDBC variables for opening and managing connection
Connection connection = null;
try {
// Load the JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection
connection = DriverManager.getConnection(jdbcURL, username, password);
System.out.println("Connected to the database!");
// Insert CLOB data
String insertClobSQL = "INSERT INTO files (name, file_type, file_text) VALUES (?, ?, ?)";
PreparedStatement insertClobStmt = connection.prepareStatement(insertClobSQL);
insertClobStmt.setString(1, "LargeTextFile");
insertClobStmt.setString(2, "text");
insertClobStmt.setClob(3, new FileReader("large_text_file.txt"));
insertClobStmt.executeUpdate();
insertClobStmt.close();
// Insert BLOB data
String insertBlobSQL = "INSERT INTO files (name, file_type, file_data) VALUES (?, ?, ?)";
PreparedStatement insertBlobStmt = connection.prepareStatement(insertBlobSQL);
insertBlobStmt.setString(1, "ImageFile");
insertBlobStmt.setString(2, "image");
insertBlobStmt.setBlob(3, new FileInputStream("image_file.jpg"));
insertBlobStmt.executeUpdate();
insertBlobStmt.close();
// Retrieve CLOB data
String selectClobSQL = "SELECT file_text FROM files WHERE name = 'LargeTextFile'";
PreparedStatement selectClobStmt = connection.prepareStatement(selectClobSQL);
ResultSet clobResultSet = selectClobStmt.executeQuery();
if (clobResultSet.next()) {
Reader reader = clobResultSet.getClob("file_text").getCharacterStream();
Writer writer = new FileWriter("retrieved_text_file.txt");
char[] buffer = new char[1024];
int charsRead;
while ((charsRead = reader.read(buffer)) != -1) {
writer.write(buffer, 0, charsRead);
}
reader.close();
writer.close();
}
clobResultSet.close();
selectClobStmt.close();
// Retrieve BLOB data
String selectBlobSQL = "SELECT file_data FROM files WHERE name = 'ImageFile'";
PreparedStatement selectBlobStmt = connection.prepareStatement(selectBlobSQL);
ResultSet blobResultSet = selectBlobStmt.executeQuery();
if (blobResultSet.next()) {
InputStream inputStream = blobResultSet.getBlob("file_data").getBinaryStream();
OutputStream outputStream = new FileOutputStream("retrieved_image_file.jpg");
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
inputStream.close();
outputStream.close();
}
blobResultSet.close();
selectBlobStmt.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (connection != null) {
connection.close();
System.out.println("Disconnected from the database.");
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
Explanation of the CLOB and BLOB
- Connection Setup:
- The JDBC URL, username, and password are specified to connect to the MySQL database.
- The Connection object is created using DriverManager.getConnection().
- Inserting CLOB Data:
- PreparedStatement insertClobStmt = connection.prepareStatement(insertClobSQL); creates a PreparedStatement for inserting CLOB data.
- insertClobStmt.setClob(3, new FileReader(“large_text_file.txt”)); sets the CLOB data from a file.
- The data is inserted using insertClobStmt.executeUpdate().
- Inserting BLOB Data:
- PreparedStatement insertBlobStmt = connection.prepareStatement(insertBlobSQL); creates a PreparedStatement for inserting BLOB data.
- insertBlobStmt.setBlob(3, new FileInputStream(“image_file.jpg”)); sets the BLOB data from a file.
- The data is inserted using insertBlobStmt.executeUpdate().
- Retrieving CLOB Data:
- PreparedStatement selectClobStmt = connection.prepareStatement(selectClobSQL); creates a PreparedStatement for selecting CLOB data.
- ResultSet clobResultSet = selectClobStmt.executeQuery(); executes the query and retrieves the CLOB data.
- The CLOB data is read from the result set using getClob(“file_text”).getCharacterStream() and written to a file.
- Retrieving BLOB Data:
- PreparedStatement selectBlobStmt = connection.prepareStatement(selectBlobSQL); creates a PreparedStatement for selecting BLOB data.
- ResultSet blobResultSet = selectBlobStmt.executeQuery(); executes the query and retrieves the BLOB data.
- The BLOB data is read from the result set using getBlob(“file_data”).getBinaryStream() and written to a file.