Working with Large Objects Using JDBC in Java
Last Updated :
14 May, 2023
Sometimes as part of programming requirements, we have to insert and retrieve large files like images, video files, audio files, resumes, etc with respect to the database.
Example:
- Uploading images on the matrimonial website
- Upload resume on job-related websites
To store and retrieve large information we should go for Large Objects(LOBs). There are 2 types of Large Objects.
- Binary Large Object (BLOB)
- Character Large Object (CLOB)
Binary Large Object (BLOB)
A BLOB is a collection of binary data stored as a single entity in the database. BLOB-type objects can be images, video files, audio files, etc. BLOB datatype can store a maximum of “4GB” binary data. eg: sachin.jpg
Steps to insert BLOB type into the Database:
1. Create a table in the database that can accept BLOB-type data.
create table persons(name varchar2(10), image BLOB);
2. Represent the image file in the form of a Java File object.
File f = new File("sachin.jpg");
3. Create FileInputStream to read binary data represented by an image file
FileInputStream fis = new FileInputStream(f)
4. Create PreparedStatement with insert query.
PreparedStatement pst = con.prepareStatement("insert into persons values(?, ?)");
5. Set values to positional parameters.
pst.setString(1, "katrina");
To set values to BLOB datatype, we can use the following method: setBinaryStream()
public void setBinaryStream(int index, InputStream is)
public void setBinaryStream(int index, InputStream is, int length)
public void setBinaryStream(int index, InputStream is, long length)
6. Execute SQL query
pst.executeUpdate();
Insert BLOB type into the Database
Java
import java.io.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import in.jdbcUtil.JdbcUtil;
public class GFG {
public static void main(String[] args)
{
Connection connection = null ;
PreparedStatement pstmt = null ;
try {
connection = JdbcUtil.getJdbcConnection();
String sqlInsertQuery = "insert into person(`name`, `image`)values(?, ?)" ;
if (connection != null )
pstmt = connection.prepareStatement(sqlInsertQuery);
if (pstmt != null ) {
pstmt.setString( 1 , "nitin" );
File f = new File( "nitin.jpg" );
FileInputStream fis = new FileInputStream(f);
pstmt.setBlob( 2 , fis);
System.out.println( "Inserting image from :: " + f.getAbsolutePath());
int noOfRows = pstmt.executeUpdate();
if (noOfRows == 1 ) {
System.out.println( "record inserted succesfully..." );
}
else {
System.out.println( "No records inserted....." );
}
}
}
catch (SQLException e) {
e.printStackTrace();
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
JdbcUtil.closeConnection( null , pstmt, connection);
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
|
Output:
Steps to Retrieve BLOB Type from Database:
1. Prepare ResultSet to object with BLOB the
ResultSet rs = st.executeQuery("select * from persons");
2. Read Normal data from ResultSet
String name=rs.getString(1);
3. Get InputStream to read binary data from ResultSet
InputStream is = rs.getBinaryStream(2);
4. Prepare target resource to hold BLOB data by using FileOutputStream
FileOutputStream fos = new FOS("katrina_new.jpg");
5. Read Binary Data from InputStream and write that Binary data to output Stream.
int i=is.read();
while(i!=-1)
{
fos.write(i);
is.read();
}
(or)
// take suitable size of array
byte[] b= new byte[2048];
while(is.read(b) > 0){
fos.write(b);
}
Retrieve BLOB type from Database
CLOB (Character Large Objects)
A CLOB is a collection of Character data stored as a single entity in the database. CLOB can be used to store large text documents(may be plain text or XML documents). CLOB Type can store a maximum of 4GB of data. Example: resume.txt
Steps to insert CLOB type file in the Database:
All steps are exactly the same as BLOB, except for the following differences
- Instead of FileInputStream, we have to take FileReader.
- Instead of setBinaryStream() method we have to use setCharacterStream() method.
public void setCharacterStream(int index, Reader r) throws SQLException
public void setCharacterStream(int index, Reader r, int length) throws SQLException
public void setCharacterStream(int index, Reader r, long length) throws SQLException
Java
import in.jdbcUtil.JdbcUtil;
import java.io.*;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class GFG {
public static void main(String[] args)
{
Connection connection = null ;
PreparedStatement pstmt = null ;
try {
connection = JdbcUtil.getJdbcConnection();
String sqlInsertQuery
= "insert into cities(`name`, `history`)values(?, ?)" ;
if (connection != null )
pstmt = connection.prepareStatement(
sqlInsertQuery);
if (pstmt != null ) {
pstmt.setString( 1 , "bengaluru" );
File f = new File( "benagluru_history.txt" );
FileReader reader = new FileReader(f);
pstmt.setCharacterStream( 2 , reader);
System.out.println(
"File is inserting from :: "
+ f.getAbsolutePath());
int noOfRows = pstmt.executeUpdate();
if (noOfRows == 1 ) {
System.out.println(
"record inserted succesfully..." );
}
else {
System.out.println(
"No records inserted....." );
}
}
}
catch (SQLException e) {
e.printStackTrace();
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
JdbcUtil.closeConnection( null , pstmt,
connection);
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
|
Output:
Steps to Retrieving CLOB Type from the Database:
All steps are exactly the same as BLOB, except for the following differences.
- Instead of using FileOutputStream, we have to use FileWriter
- Instead of using getBinaryStream() method we have to use getCharacterStream() method.
What is the Difference Between BLOB and CLOB?
We can use BLOB Type to represent binary information like images, video files, audio files, etc. Whereas we can use CLOB Type to represent Character data like text files, XML files, etc.
Share your thoughts in the comments
Please Login to comment...