Upload players photos into Oracle BLOB column and list players photos

In this post, I will show how to do the following tasks using Java EE 6.0. I have written a blog about the same a few years back when Java Servlets didn't provide any API for file upload. You can read that blog here, if you are using Java EE 5.0 or before.

How to upload a photo (file) from client to server
How to read the content of the uploaded file using Java Servlets 3.0 API
How to store uploaded image into Oracle BLOB column using JDBC
How to read image stored in BLOB column and send it to browser through outputstream

Create web project and add libraries

First of all create a new web project - uploadblog, using NetBeans 7.1.1. I am using Tomcat 7. Add Oracle JDBC driver, ojdbc6.jar, to the project libraries.

Create PLAYERS 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 players
( playername   varchar2(50) primary key,
  playerphoto  blob
);

Create HTML form to upload player's information

Create a HTML form to take data regarding a player. It contains a textbox for player name and file field to upload player’s photo.

<!DOCTYPE html>
<html>
    <head>
        <title></title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <h2>Add Player</h2>
        <form id="form1" enctype="multipart/form-data" action="addplayer" method="post">
            <table>
                <tr>
                    <td>Enter Player Name :</td>
                    <td><input  type="text"  name="playername"/></td>
                </tr>
                <tr>
                    <td>Select Photo  </td>
                    <td><input type="file"  name="playerphoto" />
                </tr>
            </table>
            <p/>
            <input type="submit" value="Add Photo"/>
        </form>
        <p/>
        <a href="listplayers">List Players</a>
    </body>
</html>

AddPlayerServlet

When user clicks on Submit button, the above form calls /addplayer, which is url pattern for AddPlayerServlet. The servlet inserts a row into PLAYERS table. The code for AddPlayerServlet.java is given below.


package servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;
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 = "AddPlayerServlet", urlPatterns = {"/addplayer"})
@MultipartConfig
public class AddPlayerServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
           
            Part p =  request.getPart("playername");
            Scanner scanner  = new Scanner( p.getInputStream());
            String playername = scanner.nextLine(); 
            
            
            Part photo =  request.getPart("playerphoto");
                        
           
            // 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 players values(?,?)");
            ps.setString(1, playername);
            // size must be converted to int otherwise it results in error
            ps.setBinaryStream(2, photo.getInputStream(), (int)  photo.getSize());
            ps.executeUpdate();
            con.commit();
            con.close();
            out.println("Added Player Successfully. <p> <a href='listplayers'>List Players </a>");
        } 
        catch(Exception ex) {
            System.out.println(ex.getMessage());
        }
        finally {            
            out.close();
        }
    }
}

ListPlayerServlet

ListPlayersServlet displays the list of players from PLAYERS table. It uses DisplayImageServelt to display each player's photo.

package servlets;

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.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(name = "ListPlayersServlet", urlPatterns = {"/listplayers"})
public class ListPlayersServlet extends HttpServlet {
    @Override 
    protected void doGet(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 players");
            ResultSet rs = ps.executeQuery();
            out.println("<h1>Players</h1>");
            while ( rs.next()) {
                  out.println("<h3>" + rs.getString("playername") + "</h3>");
                  out.println("<img width='300' height='300' src=displayphoto?name=" +  rs.getString("playername") + "></img> <p/>");
            }
            con.close();
        }
        catch(Exception ex) {
            System.out.println(ex.getMessage());
        }
        
        finally {            
            out.close();
        }
    }
}

DisplayPhotoServlet

DisplayPhotoServlet sends a single player's photo that is stored in PLAYERPHOTO column of PLAYERS table to browser's outputstream. This servelt takes playername as parameter.

package servlets;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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

    @Override 
    protected void doGet(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 playerphoto from players where playername = ?");
            String name = request.getParameter("name");
            ps.setString(1,name );
            ResultSet rs = ps.executeQuery();
            rs.next();
            Blob b = rs.getBlob("playerphoto");
            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());
        }
    }

}

After all servlets are created, build the project and deploy it. Then run addplayer.html and enter details of a player. Use List Players link to see the list of players names and their photos that are stored in the database.