How to retrieve binary data from a table using JDBC?

SQL databases provide a datatype named Blob (Binary Large Object) in this, you can store large binary data like images.

To retrieve binary (stream) values from a table JDBC provides a method called getBinaryStream() in the PreparedStatement interface.

It accepts an integer representing the index of the column of the table and retrieves the binary data from it.

You can retrieve binary data from a table using this method as shown below −

FileInputStream fin = new FileInputStream("javafx_logo.jpg");
pstmt.setBinaryStream(3, fin);

Example

Let us create a table with name tutorials_data in MySQL using the CREATE statement as shown below −

CREATE TABLE tutorials_data(
   Name VARCHAR(255),
   Type VARCHAR(50),
   Logo BLOB
);

Following JDBC program establishes connection to MySQL and, inserts 3 records into the tutorials_data table.

As a value to the 3rd column LOGO, this program stores binary data (images from local directory)and, retrieves all the records of this table.

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class BinaryDataToTable {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampledatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Inserting values
      String query = "INSERT INTO tutorials_dataa(Name, Type, Logo) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      pstmt.setString(2, "Java_library");
      FileInputStream fin = new FileInputStream("javafx_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();
      pstmt.setString(1, "CoffeeScript");
      pstmt.setString(2, "scripting Language");
      fin = new FileInputStream("coffeescript_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();
      pstmt.setString(1, "Cassandra");
      pstmt.setString(2, "NoSQL database");
      fin = new FileInputStream("cassandra_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();
      System.out.println("Records inserted......");
   }
}

Output

Connection established......
Data inserted
Name: JavaFX, Tutorial Type: Java_library, Logo: java.io.ByteArrayInputStream@7bfcd12c
Name: CoffeeScript, Tutorial Type: scripting Language, Logo: java.io.ByteArrayInputStream@42f30e0a
Name: Cassandra, Tutorial Type: NoSQL database, Logo: java.io.ByteArrayInputStream@24273305

Anvi Jain

Published on 04-Jul-2019 12:13:19