Read file data from database using JDBC

Details: Written by Nam Ha Minh; Last Updated on 11 June 2019 | Print Email

In this post, you will learn how to read binary data from database with JDBC.

You known, file data is usually stored in database in column of BLOB type, so with JDBC we can use the method getBlob() defined in the java.sql.ResultSet interface. Like other getXXX() methods of the ResultSet interface, there are also two variants of the getBlob()method:

- Blob getBlob(int columnIndex)
- Blob getBlob(String columnLabel)

Both of these methods return a java.sql.Blob object from which we can obtain an InputStream in order to read the binary data. For example:

ResultSet result = statement.executeQuery();

if (result.next()) {
	Blob blob = result.getBlob("photo");
	InputStream inputStream = blob.getBinaryStream();
	// read the input stream...

}

The above code snippet retrieves blob data from the column photo, of the current result set. Then obtain the input stream by invoking the method getBinaryStream() on the Blob object. Reading the input stream, in conjunction with an OutputStream, we can save the binary data into a file, as shown in the following example program:

package net.codejava.jdbc;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * This program demonstrates how to read file data from database and save the
 * data into a file on disk.
 * @author www.codejava.net
 *
 */
public class JdbcReadFile {
	private static final int BUFFER_SIZE = 4096;

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/contactdb";
		String user = "root";
		String password = "secret";

		String filePath = "D:/Photos/Tom.jpg";

		try {
			Connection conn = DriverManager.getConnection(url, user, password);

			String sql = "SELECT photo FROM person WHERE first_name=? AND last_name=?";
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setString(1, "Tom");
			statement.setString(2, "Eagar");

			ResultSet result = statement.executeQuery();
			if (result.next()) {
				Blob blob = result.getBlob("photo");
				InputStream inputStream = blob.getBinaryStream();
				OutputStream outputStream = new FileOutputStream(filePath);

				int bytesRead = -1;
				byte[] buffer = new byte[BUFFER_SIZE];
				while ((bytesRead = inputStream.read(buffer)) != -1) {
					outputStream.write(buffer, 0, bytesRead);
				}

				inputStream.close();
				outputStream.close();
				System.out.println("File saved");
			}
			conn.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
}

The above program connects to a MySQL database called contactdb, queries a record from the table person, reads binary data (image) from the column photo and save the data into a file called Tom.jpg on disk under D:/Photos directory.

Although the program works with MySQL database, the same code can be applied to other database systems as well.

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.