How to add Image to MySql database using Servlet and JDBC

Last Updated : 21 Jun, 2020

Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. In this article, we will understand how to add an image to the MYSQL database using servlet.

MYSQL is a relational database. Relational database means the data is stored as well as retrieved in the form of relations (tables). Servlets are the Java programs that run on the Java-enabled web server or application server. They are used to handle the request obtained from the webserver, process the request, produce the response, then send the response back to the webserver. The properties of the servlets are that they work on the server-side. And they are capable of handling complex requests obtained from the webserver. In this article, the Eclipse IDE is used to execute the servlet and MYSQL workbench is used to run the MYSQL database. The following steps can be followed to add an image into the MYSQL database using servlet and JDBC:

Step 1: Open Eclipse IDE and on the top right corner You’ll find 2 icons. Upon hovering, it will show JAVA EE and JAVA. As we will be working with a web project, so we will choose JAVA EE. After selecting it, go to File -> New -> Dynamic Web Project.
Step 2: Now We need to copy and paste the JDBC Driver for MySQL (Connector – JAR_FILE) in lib folder of WEB-INF folder which is present in WebContent folder. (All the external libraries for the project needs to be here).
Step 3: Now, we need to create a home page which will present a form wherein a user can upload an image file. We can create a JSP as well as HTML file in the WebContent folder. For Instance, we will create AddFile.jsp. We need to update the web.xml file as well, to update its Welcome File List. We have to add AddFile.jsp in the Welcome File List.

<welcome-file-list>
<welcome-file>AddFile.jsp</welcome-file>
</welcome-file-list>

Step 4: Now, we need to add the code for the AddFile.jsp which has been created above. The code for this file is as follows:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Add File Here</title>
</head>
<body>
   
     <form method="post" action="serv" enctype="multipart/form-data">
     <div>
      <label>First Name:</label>
      <input type="text" name="firstName" size="50" />
     </div>
     <div>
      <label>Last Name:</label> 
      <input type="text" name="lastName" size="50" />
     </div>
     <div>
      <label>Profile Photo: </label> 
      <input type="file" name="photo" size="50" />
     </div>
     <input type="submit" value="Save">
    </form>
   
   
</body>
</html>

Step 5: Now, we will make a class which will be used to establish the connection with MySql database using JDBC and can use that connection anytime in future to reflect anything upon Database. For instance, we will create “MyConnection.java” class and “getConnection()” static method to get a connection as follows:

import java.sql.*;
  
public class MyConnection {
  
    public static Connection getConnection()
    {
  
        // As java.sql package classes
        // contains Checked Exceptions,
        // we have to surround it with
        // Try/Catch Block.
        try {
  
            // We have to register the
            // Driver class present in
            // com.mysql.jdbc package.
            DriverManager.registerDriver(
                new com.mysql.jdbc.Driver());
  
            // To get the connection,
            // 3 params need to be passed in
            // DriverManager.getConnection() method.
  
            Connection con
                = DriverManager.getConnection(
                    "jdbc:mysql:// localhost:3306/"
                        + "FileUploadDatabase",
                    "root", "root");
  
            /* 1. URL: "jdbc:mysql://", is the address 
                  "localhost: " is the local machine 
                  and "3306" is the port number 
                  and "FileUploadDatabase" is the name 
                  of the database.
  
               2. UserName: "root", which is set 
                  while creating a database server in
                  MySql Workbench.
               3. Password: "root"   
            */
  
            return con;
        }
        catch (SQLException e) {
  
            System.out.println(e.getMessage());
        }
        return null;
    }
}

Step 6: Now, we will create a database and table “users” which will store the contents passed from the HTML Form. The query for the same is as follows:
CREATE DATABASE FileUploadDatabase
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`)
)

Step 7: Now, we will create a Servlet which will take the request from the webpage and perform all the business logic and store the contents in MySql Database. For Instance, we will name it as “serv.java”. The following is the code in this servlet:

// Java program to implement
// the servlet
package controllers;
  
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
  
import javax.servlet
    .ServletConfig;
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 Dao.UploadFileDao;
import connection.copy.MyConnection;
  
// This is the annotation-based
// mapping URL to Servlet.
@WebServlet("/serv")
  
// This annotation defines the maximum
// file size which can be taken.
@MultipartConfig(maxFileSize = 16177215)
  
public class serv extends HttpServlet {
  
    // auto generated
    private static final long serialVersionUID = 1L;
  
    public serv()
    {
        super();
    }
  
    // This Method takes in All the information
    // required and is used to store in the
    // MySql Database.
    public int uploadFile(String firstName,
                          String lastName,
                          InputStream file)
    {
        String SQL
            = "INSERT INTO users "
              + "(first_name, last_name, "
              + "photo) values (?, ?, ?)";
        int row = 0;
  
        Connection connection
            = MyConnection.getConnection();
  
        PreparedStatement preparedStatement;
        try {
            preparedStatement
                = connection.prepareStatement(sql);
  
            preparedStatement
                .setString(1, firstName);
  
            preparedStatement
                .setString(2, lastName);
  
            if (file != null) {
  
                // Fetches the 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) {
            System.out.println(e.getMessage());
        }
  
        return row;
    }
  
    // As Submit button is hit from
    // the Web Page, request is made
    // to this Servlet and
    // doPost method is invoked.
    protected void doPost(
        HttpServletRequest request,
        HttpServletResponse response)
        throws ServletException, IOException
    {
  
        // Getting the parametes from web page
        String firstName
            = request.getParameter("firstName");
  
        String lastName
            = request.getParameter("lastName");
  
        // Input stream of the upload file
        InputStream inputStream = null;
  
        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
            = UploadFileDao
                  .uploadFile(firstName,
                              lastName,
                              inputStream);
        if (row > 0) {
            message
                = "File uploaded and "
                  + "saved into database";
        }
        System.out.println(message);
    }
}

Note: The above code cannot be run on the online IDE.

Output:

We can view the database after running the code to check the images uploaded in the database by clicking on “Open Value In Editor” as follows:
After we click on “Open Value in Editor”, a window will pop up showing the image which is stored in the database in three formats: Binary Format, Text Format and Image format.
Now, if we click on the image option, we will be able to see the image which is uploaded.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes

Related Articles