Storing files in MySql database using jsp and servlet.

Question

I am trying to store files (text or image) in mySql database using jsp and servlet.

This is index.jsp.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>File Upload to Database Demo</title>
</head>
<body>
<center>
    <h1>File Upload to Database Demo</h1>
    <form method="post" action="FileUploadDBServlet" enctype="multipart/form-data">
        <table border="0">
            <tr>
                <td>First Name: </td>
                <td><input type="text" name="firstName" size="50"/></td>
            </tr>
            <tr>
                <td>Last Name: </td>
                <td><input type="text" name="lastName" size="50"/></td>
            </tr>
            <tr>
                <td>Portrait Photo: </td>
                <td><input type="file" name="photo" size="50"/></td>
            </tr>
            <tr>
                <td colspan="2">
                    <input type="submit" value="Save">
                </td>
            </tr>
        </table>
    </form>
</center>
</body>
</html>

This is my FileUploadDBServlet.

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

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;


@WebServlet(name = "FileUploadDBServlet", urlPatterns ={"/FileUploadDBServlet"})          
public class FileUploadDBServlet extends HttpServlet {

   // database connection settings
     String dbURL = "jdbc:mysql://localhost:3306/rep";
      String dbUser = "root";
      String dbPass = "";

    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

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

}
 //System.out.println(inputStream);
Connection conn = null; // connection to the database
String message = null;  // message will be sent back to client

try {
    // connects to the database
    DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    conn = DriverManager.getConnection(dbURL, dbUser, dbPass);

    // constructs SQL statement
    String sql = "INSERT INTO contacts (first_name, last_name, photo) values (?, ?, ?)";
    PreparedStatement statement = conn.prepareStatement(sql);

    statement.setString(1, firstName);
    statement.setString(2, lastName);

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

    // sends the statement to the database server
    int row = statement.executeUpdate();
    if (row > 0) {
        message = "File uploaded and saved into database";
    }
} catch (SQLException ex) {
    message = "ERROR: " + ex.getMessage();
    ex.printStackTrace();
} finally {
    if (conn != null) {
        // closes the database connection
        try {
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    // sets the message in request scope
    request.setAttribute("Message", message);

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

And this is my Message.jsp

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

 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"/>
 <title>Message</title>
 </head>
 <body>
 <center>
      <h3><%=request.getAttribute("Message")%></h3>
 </center>
 </body>
 </html>

The message page displays "ERROR: No value specified for parameter 3". So, I think null value is being stored in inputStream. Why is it so and how can it be handled? Thanks.

I would recommend to save the file in a location and save the location in the database. — Masudul
– Masudul, Commented Apr 14, 2015 at 6:29
@actuallyashish, I think inputstream is null.Debug and inspect the value of the inputstream. — VSK
– VSK, Commented Apr 14, 2015 at 6:34
@Masud Can you provide some links or tutorials where I can study about the same? — actuallyashish
– actuallyashish, Commented Apr 14, 2015 at 7:03

Jens · Accepted Answer · 2015-04-14 06:27:53Z

1

You can use statement.setNull(3, java.sql.Types.BLOB); if yoúr inputstream is null.

if (inputStream != null) {
    // fetches input stream of the upload file for the blob column
    statement.setBlob(3, inputStream);
} else {
    statement.setNull(3, java.sql.Types.BLOB);
}

answered Apr 14, 2015 at 6:27

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

actuallyashish Over a year ago

thanks for your answer. Using setNull() helped, and now I get the message "File uploaded and saved into database". However, basic problem remains that I am not able to store my data in the database. Null is being stored in all the columns. Now, I understand it for the photo column (since, setNull) but what about first_name and last_name? Also, how can I store values in the column photo?

Jens Over a year ago

@actuallyashish Can you print out request.getParameter("firstName"); to see if the parameter is set in the request?

actuallyashish Over a year ago

I tried that before and as a matter of fact, both are null. I can't understand why those parameters aren't being fetched.

kush · Accepted Answer · 2015-11-27 05:07:47Z

0

As you are submitting form data with enctype="multipart/form-data" For this you have to mention @MultipartConfig(maxFileSize = "max file size") in your upload servlet.

answered Nov 27, 2015 at 5:07

kush

792 silver badges11 bronze badges

Collectives™ on Stack Overflow

Storing files in MySql database using jsp and servlet.

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related