Write an JDBC example for inserting value for Blob datatype into a table?

JDBCJava 8MySQLMySQLi Database

Assume we already have a table named MyTable in the database with the following description.

| Field | Type         | Null | Key | Default | Extra |
| Name  | varchar(255) | YES  |     | NULL    |       |
| image | blob         | YES  |     | NULL    |       |

If you need to insert a value int to the blob data type using a JDBC program you need to use the methods which sets binary stream data. PreparedStatement interface provides the following methods to insert image into a table.

The void setBinaryStream(int parameterIndex, InputStream x) method sets the data in the given input stream (till the end of the file) as a value to the parameter at the given index.

Other variants of this method are

  • void setBinaryStream(int parameterIndex, InputStream x, int length)

  • void setBinaryStream(int parameterIndex, InputStream x, long length)

The void setBlob(int parameterIndex, Blob x) method sets the given blob object as a value to the parameter at the given index.

Other variants of this method are

  • void setBlob(int parameterIndex, InputStream inputStream)

  • void setBlob(int parameterIndex, InputStream inputStream, long length)

You can set value to the Blob data type using either of these methods.


Following example sets value to a Blob datatype using the setBinaryStream() method.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class IndertingValueForBlob {
   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......");

      //Inserting values
      String query = "INSERT INTO MyTable(Name,image) VALUES (?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "sample_image");
      FileInputStream fin = new FileInputStream("E:\\images\\cat.jpg");
      pstmt.setBinaryStream(2, fin);
      System.out.println("Record inserted .....");


Connection established......
Record inserted ......

If you try to view the blob value in the record using the MySQL work bench you can see the image inserted as shown below:

Published on 22-Mar-2019 15:05:27