Teach with us

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.

Example

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);
      pstmt.execute();
      System.out.println("Record inserted .....");
   }
}

Output

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:

raja
Nancy Den
Updated on 30-Jul-2019 22:30:25

Previous Page Print Page Next Page  
Advertisements