CLOB and BLOB data types in JDBC

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.

CLOB and BLOB

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.

Homepage

Readmore