JDBC - Save image file into database example




Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

In this post, we will show you how to save a long binary string or image file into a BLOB (Binary Large Object) column of a database table using the JDBC API.

The PreparedStatement#setBinaryStream() method is used to save the binary string into a database as a InputStream, whereas the PreparedStatement#getBinaryStream() return a OutputStream to read binary string from a database.

 

Table Creation

The following is a SQL statement for table creation in MySQL database.

CREATE TABLE boraji.user_profile (
  USER_ID INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(45) NOT NULL,
  IMAGE BLOB NULL,
  PRIMARY KEY (USER_ID));

 

Save image into database example

Here is an example of inserting an image file into a database.

ImageFileSaveExample.java

package com.boraji.tutorial.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;

/**
 * @author imssbora
 */
public class ImageFileSaveExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "INSERT INTO USER_PROFILE(NAME,IMAGE) VALUES(?,?)";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {
         File image = new File("sample.jpg");
         try (FileInputStream inputStream = new FileInputStream(image);
               PreparedStatement stmt = conn.prepareStatement(sql);) {

            stmt.setString(1, "Mike");
            stmt.setBinaryStream(2, inputStream, image.length());
            stmt.executeUpdate();
            System.out.println("Image saved successfully.");
         } catch (IOException e) {
            e.printStackTrace();
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

Image saved successfully.

 

Read image from database example

Here is an example of reading an image file from a database.

ImageFileReadExample.java

package com.boraji.tutorial.jdbc;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class ImageFileReadExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "SELECT * FROM USER_PROFILE WHERE USER_ID=?";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); 
            PreparedStatement stmt = conn.prepareStatement(sql);) {

         stmt.setInt(1, 1);
         try (ResultSet rs = stmt.executeQuery();) {
            if (rs.next()) {
               int id = rs.getInt(1);
               String name = rs.getString(2);

               File file = new File("Image_" + id + ".jpg");
               try (InputStream inputStream = rs.getBinaryStream(3); 
                     FileOutputStream outputStream = new FileOutputStream(file)) {
                  int i = 0;
                  while ((i = inputStream.read()) != -1) {
                     outputStream.write(i);
                  }
                  outputStream.flush();
               } catch (IOException e) {
                  e.printStackTrace();
               }

               System.out.println("ID=" + id);
               System.out.println("Name=" + name);
               if (file.exists()) {
                  System.out.println("File Location=" + file.getAbsolutePath());
               }
            }
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

ID=1
Name=Mike
File Location=D:\Workspace\jdbc_tutorial\Image_1.jpg