Java SQL Blob

Filed Under: Java

Interface Blob is part of java.sql package. Blob is a Java representation of SQL blob datatype. Blob is used to save and retrieve binary data from databases. Blob is supported by ResultSet, PreparedStatement, and CallableStatement.

Methods of  Java SQL Blob

  1. long length(): This method returns the size of the blob object.
  2. byte[] getBytes(long pos, int length): This method returns a byte array of blob object using specified length and starting from the given position.
  3. InputStream getBinaryStream(): This method returns InputStream of current Blob Object.
  4. InputStream getBinaryStream(long pos, long length): This method returns InputStream of current Blob Object of specified length and starting from given position.
  5. void free(): This method frees the blob object and releases the resources used by Blob Object.
  6. OutputStream setBinaryStream(long pos) : This method returns OutputStream which can be used to set Blob data.
  7. int setBytes(long pos, byte[] bytes): This method writes the specified array of bytes to the current Blob Object starting at the specified position and returns the number of bytes written.
  8. void truncate(long len): This method truncates the value of Blob Object using a specified length.

Java SQL Blob Example

We will see two different examples of Blob. We will be using MySQL database to demonstrate usage of Blob. Use below maven dependency in pom.xml of your java project.

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.49</version>
</dependency>

In 1st example, we will save the content of the file in DB, and in 2nd example, we will save Object of custom Java Class to DB using Serialization.

1. Save & Get File in DB

Use below database scripts to create database and tables.

create database blobdb;
use blobdb;
create table tblfile(
	fileid int,
 	filevalue blob
);

Let’s have look at the below example program to save data into Blob column.

package com.journaldev.example;

/**
 * Java example program to read and write blob object using file
 * 
 * @author pankaj
 * 
 */

import java.io.*;
import java.sql.*;

public class BlobFileDemo {
	
	public static void main(String[] args) throws Exception {
		Connection connection = null;
		try {
			File file = new File("/home/demo/blob.txt");
			// Initialize the file object using text file location as above
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/blobdb?useSSL=false", "root",
					"root");
			writeObject(file, connection);
			readObject(connection);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connection.close();
		}
	}

	public static void writeObject(File file, Connection connection) throws Exception {
		PreparedStatement pStmt = null;
		ByteArrayOutputStream bAout = new ByteArrayOutputStream();
		try {
			pStmt = connection.prepareStatement("insert into tblfile(fileid, filevalue) values(?, ?)");
			pStmt.setInt(1, 1);
			pStmt.setBlob(2, new FileInputStream(file));
			// Set FileInputStream object as blob
			pStmt.execute();
		} finally {
			bAout.close();
			pStmt.close();
		}
	}

	public static void readObject(Connection connection) throws Exception {
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select fileid,filevalue from tblfile where fileid=1");
			if (resultSet.next()) {
				int fildId = resultSet.getInt("fileid");
				Blob blob = resultSet.getBlob("filevalue");
				// getBlob method of ResultSet is used to get Blob from database.
				//Now we can use Blob to save data to file or transfer data to other place.
				System.out.println("FileId:" + fildId);
				System.out.println("Blob:" + blob);
				int length = (int) blob.length();
				// length() method will return size of Blob Object.
				System.out.println("length:" + length);
				System.out.println(new String(blob.getBytes(1L, length)));
				//I have used text file so that It can be displayed on console.
			}
		} finally {
			statement.close();
		}
	}
}

Output:

FileId:1
Blob:com.mysql.jdbc.Blob@41906a77
length:40
This is an example file for blob object

Database Value:

Value will depend on contents of file that you have used.

MariaDB [blobdb]> select * from tblfile where fileid=1;
+--------+------------------------------------------+
| fileid | filevalue                                |
+--------+------------------------------------------+
|      1 | This is an example file for blob object
 |
+--------+------------------------------------------+

2. Save & Get custom Java Object in Database

Use below database scripts to create table.

create table tblstudent( 	
 	studentObj blob
);

Let’s have look at the below example program.

package com.journaldev.example;

/**
 * Java example program to read and write blob object
 * 
 * @author pankaj
 * 
 */

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class BlobDemo {

	public static void main(String[] args) throws Exception {
		Connection connection = null;
		try {
			Student student = new Student("pankaj", "pankaj@mail.com");
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/blobdb?useSSL=false", "root",
					"root");
			writeObject(student, connection);
			Student dbStudent = readObject(connection);
			System.out.println(dbStudent);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connection.close();
		}
	}

	public static void writeObject(Student student, Connection connection) throws Exception {
		PreparedStatement pStmt = null;
		ByteArrayOutputStream bAout = new ByteArrayOutputStream();
		ObjectOutputStream objOut = new ObjectOutputStream(bAout);
		try {
			objOut.writeObject(student);
			objOut.flush();
			pStmt = connection.prepareStatement("insert into tblstudent(studentObj) values(?)");
			pStmt.setBlob(1, new ByteArrayInputStream(bAout.toByteArray()));
			pStmt.execute();
		} finally {
			objOut.close();
			bAout.close();
			pStmt.close();
		}
	}

	public static Student readObject(Connection connection) throws Exception {
		Student student = null;
		ObjectInputStream objIn = null;
		ByteArrayInputStream bIn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select studentObj from tblstudent");
			if (resultSet.next()) {
				Blob studBlob = resultSet.getBlob("studentObj");
				bIn = new ByteArrayInputStream(studBlob.getBytes(1, (int) studBlob.length()));
				objIn = new ObjectInputStream(bIn);
				student = (Student) objIn.readObject();
			}
		} finally {
			objIn.close();
			bIn.close();
			statement.close();
		}
		return student;
	}
}

class Student implements Serializable {
	private static final long serialVersionUID = 1L;
	private String name;
	private String email;

	public Student(String name, String email) {
		super();
		this.name = name;
		this.email = email;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Override
	public String toString() {
		return "Student [name=" + name + ", email=" + email + "]";
	}

}

Output: Student [name=pankaj, email=pankaj@mail.com]

Note: Class Student implements Serializable Interface. ObjectOutputStream and ObjectInputStream are used to convert Student objects to binary form and vice versa.

3. Save & Get Image file Object in Database

package com.journaldev.example;

/**
 * Java example program to read and write blob object using image file
 * 
 * @author pankaj
 * 
 */

import java.io.*;
import java.sql.*;
import java.util.Base64;

public class BlobImageDemo {
	
	public static void main(String[] args) throws Exception {
		Connection connection = null;
		try {
			File file = new File("/home/demo/img.jpg");
			// Initialize the file object using image file location as above
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/blobdb?useSSL=false", "root",
					"root");
			writeObject(file, connection);
			readObject(connection);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connection.close();
		}
	}

	public static void writeObject(File file, Connection connection) throws Exception {
		PreparedStatement pStmt = null;
		ByteArrayOutputStream bAout = new ByteArrayOutputStream();
		try {
			pStmt = connection.prepareStatement("insert into tblfile(fileid, filevalue) values(?, ?)");
			pStmt.setInt(1, 1);
			pStmt.setBlob(2, new FileInputStream(file));
			// Set FileInputStream object as blob
			pStmt.execute();
		} finally {
			bAout.close();
			pStmt.close();
		}
	}

	public static void readObject(Connection connection) throws Exception {
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select fileid,filevalue from tblfile where fileid=1");
			if (resultSet.next()) {
				int fildId = resultSet.getInt("fileid");
				Blob blob = resultSet.getBlob("filevalue");
				// getBlob method of ResultSet is used to get Blob from database.
				//Now we can use Blob to save data to file or transfer data to other place.
				System.out.println("FileId:" + fildId);
				System.out.println("Blob:" + blob);
				int length = (int) blob.length();
				// length() method will return size of Blob Object.
				System.out.println("length:" + length);
				String base64Image = Base64.getEncoder().encodeToString(blob.getBytes(1L, length));
				// Encode image byte array into base64
				//System.out.println("Base64 Image data: "+base64Image);
				// Remove above comments to print image data
			}
		} finally {
			statement.close();
		}
	}
}

Output:

FileId:1
Blob:com.mysql.jdbc.Blob@4b85612c
length:11397

That’s all for Java SQL Blob example tutorial, I hope nothing important got missed here.

Reference: SQL Blob

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages