Insert and Retrieve Images from MySQL Table Using Java [JDBC]

Author:
In this tutorial, we will learn how to insert and retrieve images from the MySQL database table using Java (JDBC).
Some developers prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with lots of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).
Checkout complete JDBC tutorial at Java Database Connectivity Tutorial.

Video

This tutorial is explained in the below Youtube Video. Subscribe to my youtube channel to receive a notification for future videos at Java Guides - YouTube Channel.

Tools and Technologies Used

  1. Java 1.8+
  2. MySQL connector - 8.0.5
  3. Eclipse IDE
  4. Maven
Make sure that you have installed MySQL server into your machine.

Add MySQL Dependency

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

Database Setup

Use the following statement to create a database:
mysql> create database demo;
Use the following statement to create Images table in an above-created database:
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object). There are four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. 
In this example, we are using a MEDIUMBLOB type. Read more about MEDIUMBLOB type at https://dev.mysql.com/doc/refman/8.0/en/blob.html.

Insert Image into MySQL Database using Java

In the example, we read a PNG image from the current working directory and insert it into the Images table.
package net.javaguides.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Class demonstrate Inserting Image into MySQL Database using Java
 * @author Ramesh Fadatare
 *
 */
public class JdbcWriteImage {

    public static void main(String[] args) {

        String cs = "jdbc:mysql://localhost:3306/demo?useSSL=false";
        String user = "root";
        String password = "root";

        String sql = "INSERT INTO Images(Data) VALUES(?)";

        try (Connection con = DriverManager.getConnection(cs, user, password); PreparedStatement pst = con.prepareStatement(sql)) {

            File myFile = new File("image.png");
            try (FileInputStream fin = new FileInputStream(myFile)) {

                pst.setBinaryStream(1, fin, (int) myFile.length());
                pst.executeUpdate();

            } catch (IOException ex) {

                Logger lgr = Logger.getLogger(JdbcWriteImage.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(JdbcWriteImage.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}
You can check the record inserted in Images table using select statement:
select * from Images;
Let's understand the above program.
This is the SQL to insert an image:
String sql = "INSERT INTO Images(Data) VALUES(?)";
We create a File object for the image file. To read bytes from this file, we create a FileInputStream object:
File myFile = new File("image.png");
try (FileInputStream fin = new FileInputStream(myFile)) {
...
}
The binary stream is set to the prepared statement:
pst.setBinaryStream(1, fin, (int) myFile.length());
We execute the statement:
pst.executeUpdate();

Retrieve Image from MySQL Database Table using Java

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.
package net.javaguides.jdbc;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Retrieve Image from MySQL Database Table using Java
 * @author Ramesh Fadatare
 *
 */
public class JdbcReadImage {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/demo?useSSL=false";
        String user = "root";
        String password = "root";

        String query = "SELECT Data FROM Images LIMIT 1";

        try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query); ResultSet result = pst.executeQuery()) {

            if (result.next()) {

                String fileName = "image.png";

                try (FileOutputStream fos = new FileOutputStream(fileName)) {

                    Blob blob = result.getBlob("Data");
                    int len = (int) blob.length();

                    byte[] buf = blob.getBytes(1, len);

                    fos.write(buf, 0, len);

                } catch (IOException ex) {

                    Logger lgr = Logger.getLogger(JdbcReadImage.class.getName());
                    lgr.log(Level.SEVERE, ex.getMessage(), ex);
                }
            }
        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(JdbcReadImage.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}
We select one record from the table:
String query = "SELECT Data FROM Images LIMIT 1";
The FileOutputStream object is created to write to a file. It is meant for writing streams of raw bytes such as image data:
String fileName = "src/main/resources/tree.png";

try (FileOutputStream fos = new FileOutputStream(fileName)) {
...
}
We get the image data from the Data column by calling the getBlob() method:
Blob blob = result.getBlob("Data");
We figure out the length of the blob data. In other words, we get the number of bytes:
int len = (int) blob.length();
The getBytes() method retrieves all bytes of the Blob object, as an array of bytes:
byte[] buf = blob.getBytes(1, len);
Checkout complete JDBC tutorial at Java Database Connectivity Tutorial.
The source code examples available on my GitHub Repository.

Comments

Post a Comment

Leave Comment