Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to insert images in Database using JDBC?
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.
4K+ Views
