Download - Uploading in JSP - Servlet with MySql DB

Hello Friends,

Lets see how can we code to upload and download file in JSP and Servlet.  Here I am using the database MySql.  I'm saving the file in database directly, I used the Medium Blob to store the file in database. Remember as I'm using medium blob(Max. size=16 MB) so I am considering we will upload a file with maximum size 15 MB.

By default MySql packet size is 1 MB so to support the uploading for 15 MB file, we need to change a attribute in my.ini file.

MySql installed directory > bin > "my.ini" file
search for "max_allowed_packet" you will find a line with value "1M" change to "15M"

max_allowed_packet =  1M 

max_allowed_packet = 15M

Now your MySql server is capable to transfer a big data packet to server size upto 15MB.

Create the table to store the document in MySql server

CREATE TABLE IF NOT EXISTS 'documents' (
  'Doc_id' int(11) NOT NULL AUTO_INCREMENT,
  'FileName' varchar(100) NOT NULL,
  'type' varchar(20) NOT NULL,
  'upload_time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'content' mediumblob NOT NULL,
  'upload_by' varchar(50) NOT NULL,
  PRIMARY KEY ('Doc_id')
)

Lets code for first index page which is used to select the file to upload..
Index.html

<form enctype="multipart/form-data" action="uploadfile.jsp" method="post" onsubmit="return verify()">      
<table border='1'>
<tr>
   <td>   
      Remember you can upload only MS-word, MS-Excel, txt and PDF files.max size = 15mb       
   </td>
</tr>
<tr>
   <td>
      <input type="file" name="filename" id="filename"
      accept=".txt,
           application/pdf,
           application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,
           application/vnd.ms-excel,
           application/vnd.openxmlformats-officedocument.wordprocessingml.document,
           application/msword"/>                                                            
   </td>
</tr>
<tr>
   <td>
      <input type="submit" value="Save File" />
   </td>
</tr>
</table>
</form>

Code for uploadfile.jsp file which is used to get the select file and upload it to server.
uploadfile.jsp

<%
String rtempfile = File.createTempFile("temp","1").getParent(); 

// get the file from the previous page form           
// save the file in temporary directory of server
// specify the max size = 15MB
MultipartRequest multi = new MultipartRequest(request,rtempfile, 15*1024*1024);

Enumeration files = multi.getFileNames();

String st="insert into documents(filename, type,content, upload_by) values (?,?,?,?)";

// get the connection object from another class MyConnection's method getConnection();
// and create the prepareStatement
PreparedStatement psmt=MyConnection.getConnection().prepareStatement(st);

         
String name="";
String fileExtesion="";
File ff =null;
FileInputStream fin =null;

while (files.hasMoreElements())
{
   name=(String)files.nextElement();                                        
   ff = multi.getFile(name);
   fileExtesion = ff.getName().substring(ff.getName().lastIndexOf("."));
   
   // check user has select the correct file or not
   boolean fileAllowed = fileExtesion.equalsIgnoreCase(".txt")||
                         fileExtesion.equalsIgnoreCase(".pdf")||
                         fileExtesion.equalsIgnoreCase(".doc")||
                         fileExtesion.equalsIgnoreCase(".docx")||
                         fileExtesion.equalsIgnoreCase(".xls")||
                         fileExtesion.equalsIgnoreCase(".xlsx");
   
   if((ff!=null)&&fileAllowed)
   {
     
     try
     {
       fin=new FileInputStream(ff);
       psmt.setString(1, ff.getName());
       psmt.setString(2, fileExtesion);
       psmt.setBinaryStream(3,(InputStream)fin, (int)(ff.length()));
       psmt.setString(4, "Logged User name or ID");        // pass the user name or id 
       boolean sss = psmt.execute();
       
       out.print("uploaded successfully..");
       out.print("<br/> Go to <a href='downloadfile.jsp'>Download</a> page");
     }
     
     catch(Exception e)
     {
       out.print("Failed due to " + e);
     }
     
     finally
     {
      // next statement is must otherwise file will not be deleted from the temp as fin using f.
      // its necessary to put outside otherwise at the time of exception file will not be closed.
           fin.close();
           ff.delete();
     }
   }
   else
   {
         out.print("Please select the correct file...");
   }// end of if and else
}// end of while

MyConnection.CloseConnection();     // close the connection
%>

get the list of the uploaded file and link to download the file.
downloadfile.jsp

<table border="1">
  <tr>
    <th>File Name</th>
    <th>Uploaded By</th>
    <th>File Type</th>
    <th>Upload Time</th>
    <th>Action</th>
  </tr>
<%
  String query = "select doc_id,filename,type, upload_time, upload_by from documents";
  ResultSet rs = MyConnection.getResultFromSqlQuery(query);
  int count =0;
  while(rs.next())
  {
    out.println("<tr>"
        + "<td>"+rs.getString(2)+"</td>"
        + "<td>"+rs.getString(5)+"</td>"
        + "<td>"+rs.getString(3)+"</td>"
        + "<td>"+rs.getString(4)+"</td>"
        + "<td>"
        +   "<a href='download.jsp?Doc_id="+rs.getInt(1) +"'> Download </a>"
        + "</td>"
        + "</tr>");
    count++;
  }
  rs.close();
  MyConnection.CloseConnection();
  if(count==0)
  {
    out.println("<tr><td colspan='4'> No File Found..!! </td></tr>");
  }
%>            
</table>

This page will give you the list of the file uploaded by any user with few details and a link to download the file from server.  As file is saved in database in blob so we have to create the file from blob and have to send it to client.

Lets see the code which extract the information from database and send the file to client.
download.jsp

<%
  String doc_id = request.getParameter("Doc_id");     
  String query = "select filename, type, content from Documents where doc_id = " + doc_id;
  ResultSet rs = MyConnection.getResultFromSqlQuery(query);
  rs.next();
  
  // clear the response header information.
  response.reset();                        
  // check the file type and set the header contentType accordingly..   
  if(rs.getString(2)==".txt")
  {
      response.setContentType("application/octet-stream");
  }
  else if(rs.getString(2)==".pdf")
  {
      response.setContentType("application/pdf");
  }
  else if((rs.getString(2)==".doc")||rs.getString(2)==".docx")
  {
      response.setContentType("application/msword");
  }
  else if((rs.getString(2)==".xls")||(rs.getString(2)==".xlsx"))
  {
      response.setContentType("application/vnd.ms-excel");
  }
  // add header information to response object
  response.addHeader("Content-Disposition","attachment; filename="+rs.getString(1));
  // create the byte array from Blob
  Blob blb = rs.getBlob(3);
  byte[] bdata = blb.getBytes(1, (int) blb.length());
  
  // get the response Output stream object to write the content of the file into header
  OutputStream output =  response.getOutputStream();
  output.write(bdata);
  output.close();
  // close the obejct of ResultSet
  rs.close();
  
  // close the connection object.. 
  MyConnection.CloseConnection(); 
%>

Remember you have to create the table document as specified at top.

You need to change the MyConnection class.  Provide you database credentials.
MyConnection.java

public class MyConnection
{    
  static Connection con;
  public static Connection getConnection()
  {
    try
    {            
      if(con==null)
      {
        Class.forName("com.mysql.jdbc.Driver");  
        String url = "jdbc:mysql://localhost:3306/Your_DB_Name?"+
                     "user=db_user&password=user_password";
        con= DriverManager.getConnection(url);
      }
    }
    catch (Exception ex)
    {
        ex.printStackTrace();
    }        
    return con;
  }
  
  public static void CloseConnection()
  {
    try
    {
       con.close();
       con = null;
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    } 
  }
  
  public static ResultSet getResultFromSqlQuery(String SqlQueryString)
  {
     Statement stmt;
     ResultSet rs = null;
     try
     {  
        getConnection();  
        stmt = con.createStatement();
        rs = stmt.executeQuery(SqlQueryString);
     }
     catch (SQLException e)
     {
        e.printStackTrace();
     }       
     return rs;
  }
}

To make it work you also required two jar files-
1. MySql Connector jar file download
2. Another jar file to handle the uploading download
you need to add the reference of these two files into your project.

You can download the netbeans sample project. Download

Happy Coding..