Writing and Reading MySQL BLOB Using JDBC?

Summary: this tutorial shows you how to write and read MySQL BLOB data using JDBC API.

We will use the candidates table in the mysqljdbc sample database. For the sake of demonstration, we will add one more column named resume into the candidates table. The data type of this column will be  MEDIUMBLOB that can hold up to 16MB.

The following ALTER TABLE statement adds resume column to the candidates table.

1 2	ALTER TABLE candidates ADD COLUMN resume LONGBLOB NULL AFTER email;

We will use a sample resume in PDF format and load this file into the resume column of the candidates table later. You can download the sample PDF file for practicing via the following link:

Download John Doe Resume in PDF format

Writing BLOB data into MySQL database

The steps for writing BLOB data into MySQL database is as follows:

First, open a new connection to the database by creating a new Connection object.

1	Connection conn = DriverManager.getConnection(url,username,password);

Then, construct an UPDATE statement and create a PreparedStatement from the Connection object.

1 2 3 4 5

String updateSQL = "UPDATE candidates "                 + "SET resume = ? "                 + "WHERE id=?";   PreparedStatement pstmt = conn.prepareStatement(updateSQL);

Next, read data from the sample resume file using FileInputStream and call setBinaryStream()method to set parameters for the PreparedStatement .

1 2 3 4 5 6 7

// read the file File file = new File(filename); FileInputStream input = new FileInputStream(file);   // set parameters pstmt.setBinaryStream(1, input); pstmt.setInt(2, candidateId);

After that, call the executeUpdate() method of the PreparedStatement object.

1	pstmt.executeUpdate();

Finally, close the PreparedStatement and Connection objects by calling the close() methods.

To simplify the Connection creation process, we use the MySQLJDBCUtil class that we developed in the previous tutorial to open a new connection. The complete example of writing BLOB data into MySQL database is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

package org.mysqltutorial;   import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException;   /** * * @author mysqltutorial.org */ public class Main {       /**      * Update resume for a specific candidate      *      * @param candidateId      * @param filename      */     public static void writeBlob(int candidateId, String filename) {         // update sql         String updateSQL = "UPDATE candidates "                 + "SET resume = ? "                 + "WHERE id=?";           try (Connection conn = MySQLJDBCUtil.getConnection();                 PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {               // read the file             File file = new File(filename);             FileInputStream input = new FileInputStream(file);               // set parameters             pstmt.setBinaryStream(1, input);             pstmt.setInt(2, candidateId);               // store the resume file in database             System.out.println("Reading file " + file.getAbsolutePath());             System.out.println("Store file in the database.");             pstmt.executeUpdate();           } catch (SQLException | FileNotFoundException e) {             System.out.println(e.getMessage());         }     }       /**      * @param args the command line arguments      */     public static void main(String[] args) {           writeBlob(122, "johndoe_resume.pdf");       }   }

Let’s run the program.

Now we check the  candidates table for the candidate with id 122.

1	SELECT * FROM candidates WHERE id = 122;

As you see, we have BLOB data updated in the resume column of the candidates table for record with id 122.

Reading BLOB data from MySQL database

The process of reading BLOB data from the database is similar to the process of writing BLOB except for the part that we write BLOB data into the file.

First, open a new connection to the database.

1	Connection conn = MySQLJDBCUtil.getConnection(dbURL,username,password);

Then, construct a SELECT statement and create a PreparedStatement from the Connection object.

1 2	String selectSQL = "SELECT resume FROM candidates WHERE id=?"; PreparedStatement pstmt = conn.prepareStatement(selectSQL);

Next, set the parameters and execute the query:

1 2	pstmt.setInt(1, candidateId); ResultSet rs = pstmt.executeQuery();

After that, get BLOB data from the ResultSet and write it into a file:

1 2 3 4 5 6 7 8 9 10 11

File file = new File(filename); FileOutputStream output = new FileOutputStream(file);   System.out.println("Writing to file " + file.getAbsolutePath()); while (rs.next()) {     InputStream input = rs.getBinaryStream("resume");     byte[] buffer = new byte[1024];     while (input.read(buffer) > 0) {         output.write(buffer);     } }

Finally, call the close() methods of PreparedStatment and Connection objects. If you use try-with-resources statement, you don’t have to do it explicitly.

The following example illustrates how to read BLOB data from MySQL database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

package org.mysqltutorial;   import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;   /** * * @author Main.org */ public class Main {       /**      * Read resume of a candidate and write it into a file      *      * @param candidateId      * @param filename      */     public static void readBlob(int candidateId, String filename) {         // update sql         String selectSQL = "SELECT resume FROM candidates WHERE id=?";         ResultSet rs = null;           try (Connection conn = MySQLJDBCUtil.getConnection();                 PreparedStatement pstmt = conn.prepareStatement(selectSQL);) {             // set parameter;             pstmt.setInt(1, candidateId);             rs = pstmt.executeQuery();               // write binary stream into file             File file = new File(filename);             FileOutputStream output = new FileOutputStream(file);               System.out.println("Writing to file " + file.getAbsolutePath());             while (rs.next()) {                 InputStream input = rs.getBinaryStream("resume");                 byte[] buffer = new byte[1024];                 while (input.read(buffer) > 0) {                     output.write(buffer);                 }             }         } catch (SQLException | IOException e) {             System.out.println(e.getMessage());         } finally {             try {                 if (rs != null) {                     rs.close();                 }             } catch (SQLException e) {                 System.out.println(e.getMessage());             }         }       }       /**      * @param args the command line arguments      */     public static void main(String[] args) {         //         readBlob(122, "johndoe_resume_from_db.pdf");     }   }

After running the program, browsing the project the folder, you will see that there is a new file named johndoe_resume_from_db.pdf created.

In this tutorial, we have shown you how to work with MySQL BLOB data from JDBC.