- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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 retrieve binary data from a table using JDBC?
SQL databases provide a datatype named Blob (Binary Large Object) in this, you can store large binary data like images.
To retrieve binary (stream) values from a table JDBC provides a method called getBinaryStream() in the PreparedStatement interface.
It accepts an integer representing the index of the column of the table and retrieves the binary data from it.
You can retrieve binary data from a table using this method as shown below −
FileInputStream fin = new FileInputStream("javafx_logo.jpg"); pstmt.setBinaryStream(3, fin);
Example
Let us create a table with name tutorials_data in MySQL using the CREATE statement as shown below −
CREATE TABLE tutorials_data( Name VARCHAR(255), Type VARCHAR(50), Logo BLOB );
Following JDBC program establishes connection to MySQL and, inserts 3 records into the tutorials_data table.
As a value to the 3rd column LOGO, this program stores binary data (images from local directory)and, retrieves all the records of this table.
import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class BinaryDataToTable { 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/sampledatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Inserting values String query = "INSERT INTO tutorials_dataa(Name, Type, Logo) VALUES (?, ?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "JavaFX"); pstmt.setString(2, "Java_library"); FileInputStream fin = new FileInputStream("javafx_logo.jpg"); pstmt.setBinaryStream(3, fin); pstmt.execute(); pstmt.setString(1, "CoffeeScript"); pstmt.setString(2, "scripting Language"); fin = new FileInputStream("coffeescript_logo.jpg"); pstmt.setBinaryStream(3, fin); pstmt.execute(); pstmt.setString(1, "Cassandra"); pstmt.setString(2, "NoSQL database"); fin = new FileInputStream("cassandra_logo.jpg"); pstmt.setBinaryStream(3, fin); pstmt.execute(); System.out.println("Records inserted......"); } }
Output
Connection established...... Data inserted Name: JavaFX, Tutorial Type: Java_library, Logo: java.io.ByteArrayInputStream@7bfcd12c Name: CoffeeScript, Tutorial Type: scripting Language, Logo: java.io.ByteArrayInputStream@42f30e0a Name: Cassandra, Tutorial Type: NoSQL database, Logo: java.io.ByteArrayInputStream@24273305
- Related Articles
- How to retrieve a DATALINK object from a table using JDBC?
- How to insert Binary data into a table using JDBC?
- How to retrieve Date from a table in JDBC?
- How to read/retrieve data from Database to JSON using JDBC?
- How to retrieve a record from an existing table in a database using JDBC API?
- How to retrieve particular columns of a table using JDBC program?
- How can we retrieve time from a table in JDBC?
- How to retrieve a record from an existing table in oracle database using JDBC API?
- How can we retrieve a blob datatype from a table using the getBinaryStream() method in JDBC?
- Write an JDBC example to retrieve Clob value from a table using the getCharacterStream() method?
- How to Read data from BLOB and CLOB type columns from a table using JDBC?
- What is Result in JDBC? How to retrieve data from ResultSet object?
- How can we retrieve file from database using JDBC?
- How to retrieve multiple ResultSets from a stored procedure using a JDBC program?
- How to drop a table from JavaDB using JDBC?
