How to write data into BLOB and CLOB type columns in a table using JDBC?

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).

Inserting Data into a column of Clob type

You can insert a CLOB type value using the setCharacterStream() or, setClob() methods of the PreparedStatement interface. These methods accepts two parameters −

• An integer holding the index of the bind variable. (representing the column that stores data of type CLOB)
• A reader object holding the file containing the data, you need to store into the table.

FileReader fileReader = new FileReader("javafx_contents.txt");
pstmt.setClob(1, fileReader);
or,
pstmt.setCharacterStream(1, fileReader);

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).

Inserting Data into a column of Blob datatype

You can insert BLOB data into a table using the setBinaryStream() or, setBlob() methods of the PreparedStatement interface. These methods accepts two parameters −

• An integer holding the index of the bind variable. (representing the column that stores data of type BLOB)
• A InputStream object holding the file containing the binary data (image), you need to store into the table.

InputStream inputStream = new FileInputStream("javafx_logo.jpg");
pstmt.setBlob(1, inputStream);
or,
pstmt.setBinaryStream(1, inputStream);

Following JDBC program establishes connection with the MySQL and inserts BLOB and CLOB values into the table articles_data.

Example

import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertingDataToBlobandClob {
   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();
      pstmt.setString(1, "CoffeeScript");
      fileReader = new FileReader("E:\images\coffeescript_contents.txt");
      pstmt.setClob(2, fileReader);
      inputStream = new FileInputStream("E:\images\coffeescript_logo.jpg");
      pstmt.setBlob(3, inputStream);
      pstmt.execute();
      pstmt.setString(1, "JavaFX");
      fileReader = new FileReader("E:\images\cassandra_contents.txt");
      pstmt.setClob(2, fileReader);
      inputStream = new FileInputStream("E:\images\cassandra_logo.jpg");
      pstmt.setBlob(3, inputStream);
      pstmt.execute();
      System.out.println("Records inserted......");
   }
}

Output

Records inserted......

Verification

If you verify the contents of the articles_data table using MySQL workbench you can observe the inserted image and text values as −