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

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:

Nancy Den

Published on 22-Mar-2019 10:35:27

• Related Questions & Answers
• Write an JDBC example for inserting value for Clob data type into a table?
• How to write data into BLOB and CLOB type columns in a table using JDBC?
• Write an JDBC example to retrieve Clob value from a table using the getCharacterStream() method?
• How can we retrieve a blob datatype from a table using the getBinaryStream() method in JDBC?
• Inserting Records into Table using Node
• How to insert Date value into table in JDBC?
• Inserting random numbers into a table in MySQL?
• Inserting an Element into Deaps
• How to change the datatype of a column in an existing table using JDBC API?
• Inserting data into a new column of an already existing table in MySQL?
• How to get the datatype of a column of a table using JDBC?
• Write an example JDBC program demonstrating the batch processing with statement object?
• Write an example JDBC program demonstrating the batch processing with PreparedStatement object?
• Write an example JDBC program demonstrating the batch processing with CallableStatement object?
• Inserting records into a MySQL table using PreparedStatement in Java?