Writing and Reading MySQL BLOB Using JDBC

Summary: in this tutorial, you will learn how to write and read MySQL BLOB data using JDBC.

This tutorial picks up where the Calling MySQL Stored Procedures from the JDBC tutorial left off.

Adding a BLOB column to the candidates table

We’ll use the candidates table in the mysqljdbc database for the demonstration.

First, connect to the MySQL server:

mysql -u root -p

Second, show the structure of the candidates table:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)  | NO   |     | NULL    |                |
| last_name  | varchar(50)  | NO   |     | NULL    |                |
| dob        | date         | NO   |     | NULL    |                |
| phone      | varchar(20)  | YES  |     | NULL    |                |
| email      | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)Code language: PHP (php)

Third, add a new column called resume to the candidates table:

ALTER TABLE candidates 
ADD COLUMN resume LONGBLOB
NULL AFTER email;Code language: SQL (Structured Query Language) (sql)

Finally, display the structure of the candidates table to verify the change:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)  | NO   |     | NULL    |                |
| last_name  | varchar(50)  | NO   |     | NULL    |                |
| dob        | date         | NO   |     | NULL    |                |
| phone      | varchar(20)  | YES  |     | NULL    |                |
| email      | varchar(100) | YES  |     | NULL    |                |
| resume     | longblob     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.05 sec)Code language: PHP (php)

We’ll read data from a PDF file and insert it into the resume column.

Writing BLOB data to MySQL database

The following defines addResume() method that reads data from a PDF file and inserts it into the resume column of the candidates table:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;

public class Candidate {
    public static void addResume(int candidateId, String filename) {
        var sql = "UPDATE candidates SET resume = ? WHERE id=?";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql)) {

            // read the file
            var file = new File(filename);
            var input = new FileInputStream(file);

            // set parameters
            stmt.setBinaryStream(1, input);
            stmt.setInt(2, candidateId);

            // store the resume file in database
            stmt.executeUpdate();

        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}Code language: Java (java)

The following calls the addResume() method to write binary data from C:\temp\resume.pdf file and insert it into the resume column of the candidates table for the id 1:

public class Main {
    public static void main(String[] args) {
       Candidate.addResume(1, "C:/temp/resume.pdf");
    }
}Code language: Java (java)

If you execute the program query the candidates table, you’ll see the BLOB column updated:

SELECT id, first_name, LENGTH(resume)
FROM candidates 
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+----------------+
| id | first_name | length(resume) |
+----+------------+----------------+
|  1 | Carine     |          23817 |
+----+------------+----------------+
1 row in set (0.01 sec)Code language: plaintext (plaintext)

This query retrieves the length of the resume column for the row id 1.

Reading BLOB data from MySQL database

The following defines the getResume() method that retrieves BLOB data from the resume column and writes it to a file:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;

public class Candidate {
    public static void getResume(int candidateId, String filename){
        // update sql
        var sql = "SELECT resume FROM candidates WHERE id=?";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql)) {
            // set parameter
            stmt.setInt(1, candidateId);
            var file = new File(filename);

            try(var rs = stmt.executeQuery();
                var output = new FileOutputStream(file)){
                // write binary stream into file
                while (rs.next()) {
                    var input = rs.getBinaryStream("resume");
                    byte[] buffer = new byte[1024];
                    while (input.read(buffer) > 0) {
                        output.write(buffer);
                    }
                }
            } catch (IOException e){
                e.printStackTrace();
            }
        } catch (SQLException  e) {
            e.printStackTrace();
        }
    }
    // ...
}Code language: Java (java)

The following shows how to retrieve BLOB data from the resume column of the candidates table and write it to a file in the C:/temp/resume_blob.pdf file:

public class Main {
    public static void main(String[] args){
        Candidate.getResume(1,"C:/temp/resume_blob.pdf");
    }
}Code language: Java (java)

If you execute the program, you’ll see a new file called resume_blob.pdf created in the c:/temp directory.

The resume_blob.pdf file will be the same as the one that we used to insert into the resume column.