How to insert images in Database using JDBC?

JDBCJava 8MySQLMySQLi Database

The setBinaryStream() method of the PreparedStatement interface accepts an integer representing the index of the parameter and an InputStream object and sets the parameter to the given InputStream object. Whenever you need to send very large binary value you can use this method.

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

Storing image using JDBC

If you need to store an image in a database using the JDBC program create a table with a Blob datatype as shown below:

CREATE TABLE Tutorial(Name VARCHAR(255), Type INT NOT NULL, Logo BLOB);

Now, using JDBC, connect to the database and prepare a PreparedStatement to insert values into the above created table:

String query = "INSERT INTO Tutorial(Name, Type, Logo) VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);

Set values to the place holders using the setter methods of the PreparedStatement interface and for the Blob datatype set value using the setBinaryStream() method.

FileInputStream fin = new FileInputStream("javafx_logo.jpg");

pstmt.setBinaryStream(3, fin);

Example

Following is an example demonstrating how to insert image in to a MySQL database using JDBC program. Here we are creating a table with a blob datatype inserted values to the table (BinaryStream object to the blob type) and, retrieved the contents of the table.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertingImageToDatabase {
   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/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Executing the statement
      String createTable = "CREATE TABLE Tutorial( "
         + "Name VARCHAR(255), "
         + "Type VARCHAR(50), "
         + "Logo BLOB)";
      stmt.execute(createTable);
      //Inserting values
      String query = "INSERT INTO Tutorial(Name, Type, Logo) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      pstmt.setString(2, "Java_library");
      FileInputStream fin = new FileInputStream("E:\\images\\javafx_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();

      pstmt.setString(1, "CoffeeScript");
      pstmt.setString(2, "scripting Language");
      fin = new FileInputStream("E:\\images\\coffeescript_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();
      pstmt.setString(1, "Cassandra");
      pstmt.setString(2, "NoSQL database");
      fin = new FileInputStream("E:\\images\\cassandra_logo.jpg");
      pstmt.setBinaryStream(3, fin);
      pstmt.execute();
      System.out.println("Data inserted");
      ResultSet rs = stmt.executeQuery("Select *from Tutorial");
      while(rs.next()) {
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Tutorial Type: "+rs.getString("Type")+", ");
         System.out.print("Logo: "+rs.getBlob("Logo"));
         System.out.println();
      }
   }
}

Output

Connection established......
Data inserted
Name: JavaFX, Tutorial Type: Java_library, Logo: com.mysql.jdbc.Blob@7dc5e7b4
Name: CoffeeScript, Tutorial Type: scripting Language, Logo:
com.mysql.jdbc.Blob@1ee0005
Name: Cassandra, Tutorial Type: NoSQL database, Logo: com.mysql.jdbc.Blob@75a1cd57

Note: You can store and retrieve only .gif or .jpeg or .png type of images using JDBC program.

raja
Published on 22-Mar-2019 15:01:26
Advertisements