Java servlet to download file from database

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

In this article, I will show you how to implement a solution that allows users downloading files which are stored in database, using Java servlet, JDBC and MySQL.

Assuming that we want to let the users downloading files from the following table (MySQL script):

CREATE TABLE `files_upload` (
  `upload_id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(128) DEFAULT NULL,
  `file_data` longblob,
  PRIMARY KEY (`upload_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Information of a file is stored in a record with two columns:

- file_name: name of the file.
- file_data: contains binary data of the file. This column has type of longblob which can hold up to 4GB of binary data.

NOTE:For how to insert/upload files into database, see the tutorial: Upload files to database (Servlet + JSP + MySQL).

The steps to retrieve a file from database and send it to client would be as follows:

- Connect to the database and query for a record based on a specific condition such as upload ID or file name.
- Retrieve the file’s binary data using the method getBlob(column_name) of the returned ResultSet, and obtain its input stream as follows:
```
Blob blob = result.getBlob("file_data");
InputStream inputStream = blob.getBinaryStream(); 
```
- Set content type and content length (file’s length) for the response.
- Set response’s header Content-Disposition so that the client will force the users downloading the file.
- Read byte arrays from the file’s input stream and write them to the client using the response’s output stream, until reaching end of the input stream.

For more information regarding reading binary data from database, read How to read file data from database using JDBC.

The following servlet code implements the above steps:

package net.codejava.servlet;

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;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * A servlet that retrieves a file from MySQL database and lets the client 
 * downloads the file.
 * @author www.codejava.net
 */
@WebServlet("/downloadFileServlet")
public class DBFileDownloadServlet extends HttpServlet {

	// size of byte buffer to send file
	private static final int BUFFER_SIZE = 4096;	
	
	// database connection settings
	private String dbURL = "jdbc:mysql://localhost:3306/FileDB";
	private String dbUser = "root";
	private String dbPass = "secret";
	
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// get upload id from URL's parameters
		int uploadId = Integer.parseInt(request.getParameter("id"));
		
		Connection conn = null;	// connection to the database
		
		try {
			// connects to the database
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			conn = DriverManager.getConnection(dbURL, dbUser, dbPass);

			// queries the database
			String sql = "SELECT * FROM files_upload WHERE upload_id = ?";
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, uploadId);

			ResultSet result = statement.executeQuery();
			if (result.next()) {
				// gets file name and file blob data
				String fileName = result.getString("file_name");
				Blob blob = result.getBlob("file_data");
				InputStream inputStream = blob.getBinaryStream();
				int fileLength = inputStream.available();
				
				System.out.println("fileLength = " + fileLength);

				ServletContext context = getServletContext();

				// sets MIME type for the file download
				String mimeType = context.getMimeType(fileName);
				if (mimeType == null) {			
					mimeType = "application/octet-stream";
				}				
				
				// set content properties and header attributes for the response
				response.setContentType(mimeType);
				response.setContentLength(fileLength);
				String headerKey = "Content-Disposition";
				String headerValue = String.format("attachment; filename=\"%s\"", fileName);
				response.setHeader(headerKey, headerValue);

				// writes the file to the client
				OutputStream outStream = response.getOutputStream();
				
				byte[] buffer = new byte[BUFFER_SIZE];
				int bytesRead = -1;
				
				while ((bytesRead = inputStream.read(buffer)) != -1) {
					outStream.write(buffer, 0, bytesRead);
				}
				
				inputStream.close();
				outStream.close();				
			} else {
				// no file found
				response.getWriter().print("File not found for the id: " + uploadId);	
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			response.getWriter().print("SQL Error: " + ex.getMessage());
		} catch (IOException ex) {
			ex.printStackTrace();
			response.getWriter().print("IO Error: " + ex.getMessage());
		} finally {
			if (conn != null) {
				// closes the database connection
				try {
					conn.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}			
		}
	}
}

In this servlet, we override only the doGet() method, so we will access it via normal URL. The upload id is passed via the URL’s parameter id. In case of error or no record found, the servlet sends appropriate message to the client.

Supposing the application is deployed under the context DatabaseFileDownload on localhost at port 8080, and we want to download file of the record whose upload_id is 123, type the following URL into browser’s address bar:

http://localhost:8080/DatabaseFileDownload/downloadFileServlet?id=123

A dialog box (Firefox) appears to ask the user saving the file (default):

download file dialog

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.