Upload File to Database with Servlet, JSP, JDBC and MySQL using BLOB

In this tutorial, we will learn how to develop a simple Java web application (using Servlet, JSP, JDBC, and MySQL database) that uploads files to a server and saves them in a database.

You can download the source code of this tutorial from my GitHub repository; the link is provided at the end of the tutorial.

Top JSP, Servlet and JDBC Tutorials:

• Servlet Tutorial

• JSP Tutorial

• JDBC 4.2 Tutorial

Tools and technologies used

• JSP - 2.2 +
• IDE - STS/Eclipse Neon.3
• JDK - 1.8 or later
• Apache Tomcat - 8.5
• Servlet API - 2.5
• MySQL - mysql-connector-java-8.0.13.jar

Development Steps

1. Create an Eclipse Dynamic Web Project
2. Add Dependencies
3. Project Structure
4. MySQL Database Setup
5. Create a FileUploadDao.java
6. Create a FileUploadServlet.java
7. Creating a User Profile form - upload-file.jsp
8. Create a JSP Page for Success Message - message.jsp
9. Deploying and Testing the Application Demo

1. Create an Eclipse Dynamic Web Project

To create a new dynamic Web project in Eclipse:

1. On the main menu select File > New > Project....

2. In the upcoming wizard choose Web > Dynamic Web Project.

3. Click Next.

4. Enter project name as "jsp-servlet-jdbc-mysql-example";
5. Make sure that the target runtime is set to Apache Tomcat with the currently supported version. 

2. Add Jar Files to Classpath

Add the latest release of below jar files to the lib folder.

• jsp-api.2.3.1.jar
• servlet-api.2.3.jar
• mysql-connector-java-8.0.13.jar
• jstl-1.2.jar

3. Project Structure

Refer below screenshot to create project structure or packaging structure for your reference:

4. MySQL Database Setup

Let's create a database named "java_demo" in MySQL. Now, create a users table using below DDL script:

CREATE DATABASE 'java_demo';

USE demo;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `photo` mediumblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

You can use either MySQL Command Line Client or MySQL Workbench tool to create the database.

5. Create a FileUploadDao.java

Create a new Java class file named FileUploadDao.java under net.javaguides.fileupload.dao package. Add the code shown in the following snippet.

package net.javaguides.fileupload.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class FileUploadDao {

    private static final String url = "jdbc:mysql://localhost:3306/java_demo?useSSL=false";
    private static final String user = "root";
    private static final String password = "root";

    private static final String sql = "INSERT INTO users (first_name, last_name, photo) values (?, ?, ?)";

    public int uploadFile(String firstName, String lastName, InputStream file) {
        int row = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        try (Connection connection = DriverManager
            .getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection
            .prepareStatement(sql)) {

            preparedStatement.setString(1, firstName);
            preparedStatement.setString(2, lastName);
            if (file != null) {
                // fetches input stream of the upload file for the blob column
                preparedStatement.setBlob(3, file);
            }

            // sends the statement to the database server
            row = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            // process sql exception
            printSQLException(e);
        }
        return row;
    }

    private void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

In the above JDBC code, notice that we pass the input stream into the prepared statement:

            if (file != null) {
                // fetches input stream of the upload file for the blob column
                preparedStatement.setBlob(3, file);
            }

6. Create a FileUploadServlet.java

Create a new Java class file named FileUploadServlet.java under net.javaguides.fileupload.controller package. Add the code shown in the following snippet.

package net.javaguides.fileupload.controller;

import java.io.IOException;
import java.io.InputStream;

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 net.javaguides.fileupload.dao.FileUploadDao;

@WebServlet("/uploadServlet")
@MultipartConfig(maxFileSize = 16177215) // upload file's size up to 16MB
public class FileUploadServlet extends HttpServlet {

    private static final long serialVersionUID = 1 L;

    private FileUploadDao fileUploadDao;

    @Override
    public void init() {
        fileUploadDao = new FileUploadDao();
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        // gets values of text fields
        String firstName = request.getParameter("firstName");
        String lastName = request.getParameter("lastName");

        InputStream inputStream = null; // input stream of the upload file

        String message = null;
        // obtains the upload file part in this multipart request
        Part filePart = request.getPart("photo");
        if (filePart != null) {
            // prints out some information for debugging
            System.out.println(filePart.getName());
            System.out.println(filePart.getSize());
            System.out.println(filePart.getContentType());

            // obtains input stream of the upload file
            inputStream = filePart.getInputStream();
        }

        // sends the statement to the database server
        int row = fileUploadDao.uploadFile(firstName, lastName, inputStream);
        if (row > 0) {
            message = "File uploaded and saved into database";
        }

        // sets the message in request scope
        request.setAttribute("Message", message);

        // forwards to the message page
        getServletContext().getRequestDispatcher("/message.jsp")
            .forward(request, response);
    }
}

In this servlet, we use two annotations:

• @WebServlet: marks this servlet so that the servlet container will load it at startup, and map it to the URL pattern /uploadServlet.
• @MultipartConfig: indicates this servlet will handle the multipart request. We restrict the maximum size of the upload file up to 16 MB.

Obtaining the part of upload file in the doPost() method request:

Part filePart = request.getPart("photo");

The name “photo” is the name of the file input field in the upload-file.jsp page. Obtaining the input stream of the upload file:

inputStream = filePart.getInputStream();

7. Creating a User Profile form - upload-file.jsp

Let's create a JSP file named upload-file.jsp and add the following content to it:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>File Upload Servlet JSP JDBC MySQL Example</title>
<link
 href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
 rel="stylesheet"
 integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
 crossorigin="anonymous">
</head>
<body>

 <div class="container col-lg-6">
  <h1 class="text-center">File Upload to Database Example - Servlet JSP JDBC MySQL </h1>
  <div class="card">
   <div class="card-body">
    <form method="post" class="form-group" action="uploadServlet"
     enctype="multipart/form-data">
     <div class="form-group">
      <label for="first name">First Name: </label> <input type="text"
       class="form-control" name="firstName" size="50" />
     </div>
     <div class="form-group">
      <label for="last name">Last Name: </label> <input type="text"
       class="form-control" name="lastName" size="50" />
     </div>

     <div class="form-group">
      <label for="Profile Photo">Profile Photo:</label> <input
       type="file" name="photo" size="50" />
     </div>
     <input type="submit" value="Save" class="btn btn-success">
    </form>
   </div>
  </div>
 </div>
</body>
</html>

Note that we have used bootstrap CSS framework for styling this JSP page.

8. Create a JSP Page for Success Message - message.jsp

Create a JSP page named as message.jsp with the following code:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Success massage</title>
<link
 href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
 rel="stylesheet"
 integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
 crossorigin="anonymous">
</head>
<body>
 <center>
  <div class="alert alert-success">
   <strong><%=request.getAttribute("Message")%></strong>
  </div>
 </center>
</body>
</html>

This page simply displays the value of the variable “Message” in the request scope.

9. Deploying and Testing the Application Demo

Deploy this web application in tomcat server in Eclipse IDE. 

Type the following URL in your web browser to access this application: 

http://localhost:8080/login-jsp-servlet-jdbc-example/file-upload.jsp

Fill the details and upload the file as shown below:

After form data along with a photo (image) store in the database then the user will navigate to the below screen with a success message:

10. Test in MySQL Workbench

To verify that the file is stored successfully in the database, open a new SQL Editor in MySQL Workbench and execute the following query:

select * from users;

The query would return the newly inserted record, right-click on the BLOB cell of the column photo, and select Open Value in Editor from the context menu:

The source code examples are available on my GitHub Repository.

Related Servlet Posts

• What is a Servlet in Java?
  
• Servlet Life Cycle
• Servlet Interface Example
• GenericServlet Class Example
• HttpServlet Class Example Tutorial
• HttpServlet doGet() Method Example
• HttpServlet doPost() Method Example
• @WebServlet Annotation Example
• @WebInitParam Annotation Example
• @WebListener Annotation Example
• @WebFilter Annotation Example
• @MultipartConfig Annotation Example
• How to Return a JSON Response from a Java Servlet
• Servlet Registration Form + JDBC + MySQL Database Example
• Login Form Servlet + JDBC + MySQL Example
• Servlet JDBC Eclipse Example Tutorial
• JSP Servlet JDBC MySQL CRUD Example Tutorial
• Servlet + JSP + JDBC + MySQL Example
• Registration Form using JSP + Servlet + JDBC + Mysql Example
• Login Form using JSP + Servlet + JDBC + MySQL Example
• JSP Servlet Hibernate CRUD Example
• JSP Servlet Hibernate Web Application
• Hibernate Registration Form Example with JSP, Servlet, MySQL
• Login Form using JSP + Servlet + Hibernate + MySQL Example