A JSP page to upload local documents
onto a database table

1. Introduction

Here is a solution that explains how you can upload local documents onto the database.
It uses a JSP page, two Java classes, and of course, a JDBC connection.
It allows to store any document taken from the client machine to any column of any database table.
In this sample, we are supposed to store a file in a BLOB column.

UploadFiles

2. The JSP page

uploadDocument.jsp

This is the JSP page that receives the required parameters from the calling application:

the necessary SQL orders to query and update the corresponding database table.

the datasource name to connect through the JDBC driver.

the return url to re-display the calling page.

a non-mandatory comma delimited string that contains the list of the allowed extensions (e.g. ".gif,.jpg")

<%String path = request.getContextPath();%>
<html>
<head>
<title>Attach documents</title>
<%
String sUrlBack = ""; // return Url
String sInsert = ""; // Insert order
String sSelect = ""; // Select order
String sFilter = ""; // file filter
String sDSource = ""; // data-source name
String sLog     = ""; // enable log
// read the parameters
sSelect = request.getParameter("select") != null ? request.getParameter("select") : "" ;
sInsert = request.getParameter("select") != null ? request.getParameter("insert") : "" ;
sFilter = request.getParameter("filter") != null ? request.getParameter("filter") : "" ;
sUrlBack = request.getParameter("urlback") ! = null ? request.getParameter("urlback") : "" ;
sDSource = request.getParameter("datasource") != null ? request.getParameter("datasource") : "" ;
sLog     = request.getParameter("log") != null ? request.getParameter("log") : "" ;
// set parameters in memory
session.setAttribute("select", sSelect);
session.setAttribute("insert", sInsert);
session.setAttribute("urlRedirect", sUrlBack);
session.setAttribute("datasource", sDSource);
session.setAttribute("log", sLog);
%>
<script language="JavaScript">
function checkParams() {
var filename = document.Upload.file.value ;
var filtre = "<%=sFilter%>"
var ext = filename.substring(filename.lastIndexOf("."));
//checkparameters
if("<%=sSelect%>"== '') { alert("SELECT order missing"); return false; }
if("<%=sInsert%>"=='') { alert("INSERT order missing"); return false; }
if (filename=='') {
    alert("Filename must be given");
    document.Upload.file.focus();
}
else
// check filename extensions
if(filtre != "" && filtre.indexOf(ext)==-1)
{
    alert("Extension must be in: "+filtre);
    document.Upload.file.focus();
}
else {
    document.Upload.submit();
}
}
</script>
</head>
<body>
<form name= "Upload"action="<%=path%>/UploadFiles" method="post" enctype="multipart/form-data">
<table cellspacing=0 width="200" border=1>
<tr><td>
<table cellspacing=0 width="200" border=0>
<tr>
    <td height="24">File name</td>
    <td><input type="file" name="file" size="40"></td>
</tr>
<tr></tr>
<tr align=center><td colspan=2>
    <input type=button value="Close" onclick="javascript:self.close();"> 
    <input type=button value="Attach file" onclick="javascript:checkParams();"> 
</td></tr>
</table>
</td></tr>
</table>
</form>
</body>
</html>

The forms’s action (colorized in blue) is to call the UploadFiles Java class with the corresponding parameters.

A javascript function (checkParams()) is used to check that the mandatory parameters have been provided (SQL orders and filename), and the file selected has an allowed extension (if a filter has been provided).

3. The Java classes

UploadFiles.java

This class extends HttpServlet and allows to get the file to read.
It is called by the calling jsp that transmits the required parameters:

- Select and Insert SQL orders and the JDBC connection string.

package util.upload;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class UploadFiles extends HttpServlet {

private String urlRedirect = "";

public void init(ServletConfig config) throws ServletException {
    super.init(config);
}

public void doPost(HttpServletRequest pRequest, HttpServletResponse pReponse) throws ServletException, IOException {
    MultipartRequest multi = null;

    try {
      urlRedirect = (String)pRequest.getSession(false).getAttribute("urlRedirect");
      multi = new MultipartRequest(pRequest);
    }
    catch (IOException e) {
      System.err.println("ERROR UploadFiles :" + e.getMessage());
      e.printStackTrace();
    }
    catch (Exception e) {
      System.err.println("ERROR UploadFiles :" + e.getMessage());
      e.printStackTrace();
    }
    finally {
      System.out.println("urlRedirect="+urlRedirect);
      redirect(pReponse,urlRedirect);
   }

}

    private void redirect (HttpServletResponse pReponse,String pURL) {
      try {
        pReponse.sendRedirect(pReponse.encodeRedirectUrl(pURL));
      } catch (IOException i) {
        System.err.println("ERROR UploadFiles :" + i.getMessage());
      }
    }

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();
    out.println("<html>");
    out.println("<head><title>Servlet1</title></head>");
    out.println("<body>");
    out.println("<p>The servlet has received a GET. This is the reply.</p>");
    out.println("</body></html>");
    out.close();
}

private static final String CONTENT_TYPE = "text/html; charset=ISO-8859-1";

}

MultipartRequest.java

This class gets the file stream then write the content into the provided database column.

4. The web.xml content

<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app>
<description>UploadFile web.xml file</description>
<session-config>
    <session-timeout>35</session-timeout>
</session-config>
<servlet>
    <servlet-name>UploadFiles</servlet-name>
    <servlet-class>util.upload.UploadFiles</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>UploadFiles</servlet-name>
    <url-pattern>/UploadFiles</url-pattern>
</servlet-mapping>
<mime-mapping>
    <extension>html</extension>
    <mime-type>text/html</mime-type>
</mime-mapping>
<mime-mapping>
    <extension>txt</extension>
    <mime-type>text/plain</mime-type>
</mime-mapping>
</web-app>

That allows the JSP page to call the Java class.

5. The sample JSP test page

the go.jsp page is provided to test the solution.

<%@pagecontentType="text/html;charset=windows-1252"%>
<%
    String sPath = request.getContextPath() ;
    String sSelect = "select doc from BINARY_FILES where id=1 for update " ;
    String sInsert = "insert into binary_files (id,name,mimetype,doc) values(seq_binary_files.nextval,'doc','image/jpeg',empty_blob()) " ;
    String sDataSource = "jdbc:oracle:thin:test/test@localhost:1521:XE" ;
    String sReturnUrl = sPath + "/uploadDocument.jsp" ;
%>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>untitled</title>
</head>
<body>
<table>
<tr>
<td>
    
    
   <ahref="<%=sReturnUrl%>?insert=<%=sInsert%>&select=<%=sSelect%>&urlback=<%=sReturnUrl%>
     &datasource=<%=sDataSource%>&log=true">file attachment</a>
</td>
<tr>
</table>
</body>
</html>

The SELECT statement is used to lock the corresponding row, then update the BLOB content.
(In this example, the id transmitted is hard-coded (id=1) but in the real world, the ID would be dynamically set)
The INSERT statement is used to create the row if it does not exist.
The sDataSource variable contains the JDBC connection string.
The sReturnUrl variable contains the url to return after the uploading, so to re-display the main page.
The log is set to true to display some trace information in the console.
A file filter can be provided to validate the allowed file types.

6. The sample table creation script

create table binary_files
(
   id        number(5) primary key
,name      varchar2(128)
,mimetype varchar2(30)
,doc       blob
)
/

create sequence seq_binary_files start with 1
/

Download the corresponding files