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:
Published on 22-Mar-2019 15:05:27
