Upload and download files from Database using Java Servlet

Database
Upload and download from database
Upload and store in DB
Download from Database

1. Database

** ORACLE **

create table ATTACHMENT
(
  ID          NUMBER(19) not null,
  FILE_NAME   VARCHAR2(50) not null,
  FILE_DATA   BLOB not null,
  DESCRIPTION VARCHAR2(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;

** MYSQL **

create table ATTACHMENT
(
  ID          BIGINT not null,
  FILE_NAME   VARCHAR(50) not null,
  FILE_DATA   BLOB not null,
  DESCRIPTION VARCHAR(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;

** SQL SERVER **

create table ATTACHMENT
(
  ID          BIGINT not null,
  FILE_NAME   VARCHAR(50) not null,
  FILE_DATA   Varbinary(max) not null,
  DESCRIPTION VARCHAR(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;

2. Upload and download from database

In this document I will guide you to upload and store files in the database, then download the data from the database. Upload data files stored in the column with data type is BLOB.
You can use any database, above is a script to create ATTACHMENT table, this table used to store data files that you upload.

You can learn more how to use JDBC in order to connect to Database here:
Java JDBC

3. Upload and store in DB

UploadToDBServlet.java

package org.o7planning.servletexamples;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

@WebServlet("/uploadToDB")
@MultipartConfig(fileSizeThreshold = 1024 * 1024 * 2, // 2MB
        maxFileSize = 1024 * 1024 * 10, // 10MB
        maxRequestSize = 1024 * 1024 * 50) // 50MB
public class UploadToDBServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        RequestDispatcher dispatcher = request.getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDB.jsp");

        dispatcher.forward(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        try {
            // Connection to Database
            // (See more in JDBC Tutorial).
            conn = ConnectionUtils.getMyConnection();
            conn.setAutoCommit(false);

            String description = request.getParameter("description");

            // Part list (multi files).
            for (Part part : request.getParts()) {
                String fileName = extractFileName(part);
                if (fileName != null && fileName.length() > 0) {
                    // File data
                    InputStream is = part.getInputStream();
                    // Write to file
                    this.writeToDB(conn, fileName, is, description);
                }
            }
            conn.commit();

            // Upload successfully!.
            response.sendRedirect(request.getContextPath() + "/uploadToDBResults");
        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("errorMessage", "Error: " + e.getMessage());
            RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDB.jsp");
            dispatcher.forward(request, response);
        } finally {
            this.closeQuietly(conn);
        }
    }

    private String extractFileName(Part part) {
        // form-data; name="file"; filename="C:\file1.zip"
        // form-data; name="file"; filename="C:\Note\file2.zip"
        String contentDisp = part.getHeader("content-disposition");
        String[] items = contentDisp.split(";");
        for (String s : items) {
            if (s.trim().startsWith("filename")) {
                // C:\file1.zip
                // C:\Note\file2.zip
                String clientFileName = s.substring(s.indexOf("=") + 2, s.length() - 1);
                clientFileName = clientFileName.replace("\\", "/");
                int i = clientFileName.lastIndexOf('/');
                // file1.zip
                // file2.zip
                return clientFileName.substring(i + 1);
            }
        }
        return null;
    }

    private Long getMaxAttachmentId(Connection conn) throws SQLException {
        String sql = "Select max(a.id) from Attachment a";
        PreparedStatement pstm = conn.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        if (rs.next()) {
            long max = rs.getLong(1);
            return max;
        }
        return 0L;
    }

    private void writeToDB(Connection conn, String fileName, InputStream is, String description) throws SQLException {

        String sql = "Insert into Attachment(Id,File_Name,File_Data,Description) " //
                + " values (?,?,?,?) ";
        PreparedStatement pstm = conn.prepareStatement(sql);

        Long id = this.getMaxAttachmentId(conn) + 1;
        pstm.setLong(1, id);
        pstm.setString(2, fileName);
        pstm.setBlob(3, is);
        pstm.setString(4, description);
        pstm.executeUpdate();
    }

    private void closeQuietly(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
        }
    }

}

UploadToDBResultsServlet.java

package org.o7planning.servletexamples;

import java.io.IOException;

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

@WebServlet("/uploadToDBResults")
public class UploadToDBResultsServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 

    public UploadToDBResultsServlet() {
        super();
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        RequestDispatcher dispatcher
            = request.getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDBResults.jsp");

        dispatcher.forward(request, response);
    }

}

/WEB-INF/jsps/uploadToDB.jsp

<!DOCTYPE >
<html>
<head>
<title>Upload files</title>
</head>
<body>

    <div style="padding:5px; color:red;font-style:italic;">
       ${errorMessage}
    </div>
   
    <h2>Upload Files</h2>

    <form method="post" action="${pageContext.request.contextPath}/uploadToDB"
        enctype="multipart/form-data">
       
        Select file to upload:
        <br />
        <input type="file" name="file"  />
        <br />
        <input type="file" name="file" />
        <br />
        Description:
        <br />
        <input type="text" name="description" size="100" />
        <br />
        <br />
        <input type="submit" value="Upload" />
    </form>
   
</body>
</html>

/WEB-INF/jsps/uploadToDBResults.jsp

<!DOCTYPE >
<html>
<head>
<title>Upload files</title>
</head>
<body>

    <h3>Upload has been done successfully!</h3>
    <a href="${pageContext.request.contextPath}/uploadToDB">Continue Upload</a>
     
   
</body>
</html>

Running the apps:

The files have been uploaded and stored on Attachment table.

4. Download from Database

DownloadAttachmentServlet.java

package org.o7planning.servletexamples;

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

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

import org.o7planning.servletexamples.model.Attachment;
import org.o7planning.tutorial.jdbc.ConnectionUtils;

@WebServlet("/downloadAttachment")
public class DownloadAttachmentServlet extends HttpServlet {
   private static final long serialVersionUID = 1L;


   @Override
   protected void doGet(HttpServletRequest request, HttpServletResponse response)
           throws ServletException, IOException {
       Connection conn = null;
       try {
           // Get Database Connection.
           // (See more in JDBC Tutorial).
           conn = ConnectionUtils.getMyConnection();
           Long id = null;
           try {
               id = Long.parseLong(request.getParameter("id"));
           } catch (Exception e) {

           }
           Attachment attachment = getAttachmentFromDB(conn, id);

           if (attachment == null) {
               // No record found.
               response.getWriter().write("No data found");
               return;
           }

           // file1.zip, file2.zip
           String fileName = attachment.getFileName();
           System.out.println("File Name: " + fileName);

           // abc.txt => text/plain
           // abc.zip => application/zip
           // abc.pdf => application/pdf
           String contentType = this.getServletContext().getMimeType(fileName);
           System.out.println("Content Type: " + contentType);

           response.setHeader("Content-Type", contentType);

           response.setHeader("Content-Length", String.valueOf(attachment.getFileData().length()));

           response.setHeader("Content-Disposition", "inline; filename=\"" + attachment.getFileName() + "\"");

           // For big BLOB data.
           Blob fileData = attachment.getFileData();
           InputStream is = fileData.getBinaryStream();

           byte[] bytes = new byte[1024];
           int bytesRead;

           while ((bytesRead = is.read(bytes)) != -1) {
               // Write image data to Response.
               response.getOutputStream().write(bytes, 0, bytesRead);
           }
           is.close();

       } catch (Exception e) {
           throw new ServletException(e);
       } finally {
           this.closeQuietly(conn);
       }
   }

   private Attachment getAttachmentFromDB(Connection conn, Long id) throws SQLException {
       String sql = "Select a.Id,a.File_Name,a.File_Data,a.Description "//
               + " from Attachment a where a.id = ?";
       PreparedStatement pstm = conn.prepareStatement(sql);
       pstm.setLong(1, id);
       ResultSet rs = pstm.executeQuery();
       if (rs.next()) {
           String fileName = rs.getString("File_Name");
           Blob fileData = rs.getBlob("File_Data");
           String description = rs.getString("Description");
           return new Attachment(id, fileName, fileData, description);
       }
       return null;
   }

   private void closeQuietly(Connection conn) {
       try {
           if (conn != null) {
               conn.close();
           }
       } catch (Exception e) {
       }
   }

}