How to add Image to MySql database using Servlet and JDBC

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:

  1. 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.

  2. 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).

  3. 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.
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

  4. 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:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <%@ 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>

    chevron_right

    
    

  5. 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:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    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;
        }
    }

    chevron_right

    
    

  6. 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`)
    )

  7. 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:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    // 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);
        }
    }

    chevron_right

    
    

    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 Java and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready.




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.