Upload photos into Oracle BLOB column from Java Servlet and listing photos

In this post, I will show how to do the following tasks using Java.

How to upload a photo (file) from client to server.
How to read the content of the uploaded file using Apache Commons-Fileupload library.
How to store uploaded content into Oracle BLOB column using JDBC.
How to send photos stored in BLOB column to browser through outputstream, which is taken by IMG tag in HTML.

Create web project and add libraries

First of all create a new web project and add the following libraries to it. I named the project as photos. You can give any name to the project. I am using NetBeans IDE 6.5 for this example. You can use any IDE of your choice and any version of NetBeans.

Oracle library - ojdbc14.jar
Apache commons-fileupload library - commons-fileupload-1.2.1.jar
Apache commons-io library - commons-io-1.4.jar

Create PHOTOS table

Create a table in Oracle database as follows. This table contains id, which is unique for each photo, title of the photo and photo itself stored as BLOB.

  create table photos
  ( 
    id  number(5) primary key,
    title  varchar(50),
    photo  blob
  );

Download Commons-Fileupload library

As we have to deal with File upload, we need to use apaches commons-fileupload library. This library depends on commons-io library. So, we need to download both these libraries from commons-fileupload and commons-io.

After you download these libraries, add .jar files to project using libraries node in NetBeans project window. Do whatever is necessary to include these libraries if you are using IDEs other than NetBeans.

Create HTML form to upload photo

Create a HTML form to take data regarding a new photo. It contains two text fields and one file field to upload photo to server.

 <html>
    <head>
        <title>Add Photo</title>
    </head>
    <body>
        <h2>Add Photo</h2>
        <form id="form1" enctype="multipart/form-data" action="addphoto" method="post">
            <table>
                <tr>
                    <td>Enter Photo Id :</td>
                    <td><input  type="text"  name="id"/></td>
                </tr>
                <tr>
                    <td>Enter Title For Photo :</td>
                    <td><input  type="text"  name="title"/></td>
                </tr>
                <tr>
                    <td>Select Photo  </td>
                    <td><input type="file"  name="photo" />
                </tr>
            </table>
            <p/>
            <input type="submit" value="Add Photo"/>
        </form>

        <p/>
        <a href="listphotos">List Photos </a>
    </body>
</html>

AddPhotoServlet

When user clicks on Submit button, the above form calls AddPhotoServlet, which inserts a row into PHOTOS table. The code for AddPhotoServlet.java is given below. Create this servlet and assign addphoto as the url pattern for it.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class AddPhotoServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            // Apache Commons-Fileupload library classes
            DiskFileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload sfu  = new ServletFileUpload(factory);

            if (! ServletFileUpload.isMultipartContent(request)) {
                System.out.println("sorry. No file uploaded");
                return;
            }

            // parse request
            List items = sfu.parseRequest(request);
            FileItem  id = (FileItem) items.get(0);
            String photoid =  id.getString();
            
            FileItem title = (FileItem) items.get(1);
            String   phototitle =  title.getString();

            // get uploaded file
            FileItem file = (FileItem) items.get(2);
                        
            // Connect to Oracle
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
            con.setAutoCommit(false);

            PreparedStatement ps = con.prepareStatement("insert into photos values(?,?,?)");
            ps.setString(1, photoid);
            ps.setString(2, phototitle);
            // size must be converted to int otherwise it results in error
            ps.setBinaryStream(3, file.getInputStream(), (int) file.getSize());
            ps.executeUpdate();
            con.commit();
            con.close();
            out.println("Photo Added Successfully. <p> <a href='listphotos'>List Photos </a>");
        }
        catch(Exception ex) {
            out.println( "Error --> " + ex.getMessage());
        }
    } 
}

ListPhotosServlet

Create another servlet - ListPhotosServlet.java - to display the list of photos from PHOTOS table. It uses DisplayImageServlet to display each photo.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ListPhotosServlet extends HttpServlet {
   
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
            PreparedStatement ps = con.prepareStatement("select * from photos");
            ResultSet rs = ps.executeQuery();
            out.println("<h1>Photos</h1>");
            while ( rs.next()) {
                  out.println("<h4>" + rs.getString("title") + "</h4>");
                  out.println("<img width='600' height='600' src=displayphoto?id=" +  rs.getString("id") + "></img> <p/>");
            }

            con.close();
        }
        catch(Exception ex) {

        }
        finally { 
            out.close();
        }
    } 

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    } 

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
}

DisplayPhotoServlet

DisplayPhotoServlet.java sends a a single photo that is taken from PHOTO column of PHOTOS table to browser as outputstream. This servlet takes id as parameter (querystring) and sends photo to IMG tag.

DisplayPhotoServlet.java

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DisplayPhotoServlet extends HttpServlet {
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
            PreparedStatement ps = con.prepareStatement("select photo from photos where id = ?");
            String id = request.getParameter("id");
            ps.setString(1,id);
            ResultSet rs = ps.executeQuery();
            rs.next();
            Blob  b = rs.getBlob("photo");
            response.setContentType("image/jpeg");
            response.setContentLength( (int) b.length());
            InputStream is = b.getBinaryStream();
            OutputStream os = response.getOutputStream();
            byte buf[] = new byte[(int) b.length()];
            is.read(buf);
            os.write(buf);
            os.close();
        }
        catch(Exception ex) {
             System.out.println(ex.getMessage());
        }
    } 

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    } 
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
}

Here is web.xml file that contains entries related to Servlets.

    <servlet>
        <servlet-name>AddPhoto</servlet-name>
        <servlet-class>AddPhotoServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>ListPhotos</servlet-name>
        <servlet-class>ListPhotosServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>DisplayPhotoServlet</servlet-name>
        <servlet-class>DisplayPhotoServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>AddPhoto</servlet-name>
        <url-pattern>/addphoto</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>ListPhotos</servlet-name>
        <url-pattern>/listphotos</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>DisplayPhotoServlet</servlet-name>
        <url-pattern>/displayphoto</url-pattern>
    </servlet-mapping>

After all servlets are created, build the project and deploy it. Then run addphoto.html and enter details of some photos. Then you can click on List Photos link to see the list of photos placed in the database.