How to Insert Blob Data in Mysql Using Java
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.
Code language: SQL (Structured Query Language) ( sql )
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.
Code language: Java ( java )
Connection conn = DriverManager.getConnection(url,username,password);
Then, construct an UPDATE statement and create a PreparedStatement from the Connection object.
Code language: Java ( java )
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 .
Code language: Java ( java )
// 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.
Code language: Java ( java )
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:
Code language: Java ( java )
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 thecandidates table for the candidate with id 122.
Code language: SQL (Structured Query Language) ( sql )
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.
Code language: Java ( java )
Connection conn = MySQLJDBCUtil.getConnection(dbURL,username,password);
Then, construct a SELECT statement and create a PreparedStatement from the Connection object.
Code language: Java ( java )
String selectSQL = "SELECT resume FROM candidates WHERE id=?"; PreparedStatement pstmt = conn.prepareStatement(selectSQL);
Next, set the parameters and execute the query:
Code language: Java ( java )
pstmt.setInt(1, candidateId); ResultSet rs = pstmt.executeQuery();
After that, get BLOB data from the ResultSet and write it into a file:
Code language: Java ( java )
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 theclose() 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.
Code language: Java ( java )
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.
Was this tutorial helpful?
How to Insert Blob Data in Mysql Using Java
Source: https://www.mysqltutorial.org/mysql-jdbc-blob