Working with Large Objects Using JDBC in Java

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.

1. Binary Large Object (BLOB)
2. 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

/*package whatever // do not write package name here */

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;

// BlobInsertOperation
public class GFG {

    public static void main(String[] args)
    {
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            // Getting the database connection
            // using utility code
            connection = JdbcUtil.getJdbcConnection();

            String sqlInsertQuery = "insert into person(`name`, `image`)values(?, ?)";

            if (connection != null)
                pstmt = connection.prepareStatement(sqlInsertQuery);

            if (pstmt != null) {

                // Setting the first index to String
                pstmt.setString(1, "nitin");

                // Image files is reaching to java application
                File f = new File("nitin.jpg");
                FileInputStream fis = new FileInputStream(f);

                // setting the input information from
                // java and sending the data to database
                pstmt.setBlob(2, fis);

                System.out.println("Inserting image from :: " + f.getAbsolutePath());

                // Executing the Query to get the result
                int noOfRows = pstmt.executeUpdate();

                if (noOfRows == 1) {
                    System.out.println("record inserted succesfully...");
                }
                else {
                    System.out.println("No records inserted.....");
                }
            }
        }
        catch (SQLException e) {
            // handling logic of exception
            // related to SQLException
            e.printStackTrace();
        }
        catch (FileNotFoundException e) {
            // handling logic of exception
            // related to FileOperation
            e.printStackTrace();
        }
        catch (Exception e) {
            // handling logic of exception
            // related to common problem
            e.printStackTrace();
        }
        finally {

            // closing the resource
            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

1. Instead of FileInputStream, we have to take FileReader.
2. 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

/*package whatever // do not write package name here */

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;

// ClobInsertOperation
public class GFG {

    public static void main(String[] args)
    {
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            // Getting the database
            // connection using utility
            // code
            connection = JdbcUtil.getJdbcConnection();

            String sqlInsertQuery
                = "insert into cities(`name`, `history`)values(?, ?)";

            if (connection != null)
                pstmt = connection.prepareStatement(
                    sqlInsertQuery);

            if (pstmt != null) {

                // Setting the first index to String
                pstmt.setString(1, "bengaluru");

                // Image files is reaching to java
                // application
                File f = new File("benagluru_history.txt");
                FileReader reader = new FileReader(f);

                // setting the input information from java
                // and sending the data to database
                pstmt.setCharacterStream(2, reader);

                System.out.println(
                    "File is inserting from :: "
                    + f.getAbsolutePath());

                // Executing the Query to get the result
                int noOfRows = pstmt.executeUpdate();

                if (noOfRows == 1) {
                    System.out.println(
                        "record inserted succesfully...");
                }
                else {
                    System.out.println(
                        "No records inserted.....");
                }
            }
        }
        catch (SQLException e) {
            // handling logic of
            // exception related to
            // SQLException
            e.printStackTrace();
        }
        catch (FileNotFoundException e) {
            // hanlding logic of
            // exception related to
            // FileOperation
            e.printStackTrace();
        }
        catch (Exception e) {
            // hanlding logic of exception
            // related to common
            // problem
            e.printStackTrace();
        }
        finally {

            // closing the resource
            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.

1. Instead of using FileOutputStream, we have to use FileWriter
2. 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.