[ Team LiB ] Previous Section Next Section

Recipe 21.7 Calling a Stored Procedure from a Servlet


You want to call a stored procedure from a servlet.


Use the java.sql.CallableStatement class inside a servlet service method, such as doGet( ) or doPost( ).


Database developers create stored procedures typically for SQL code that they want to execute on a regular basis, similar to a Java developer's reason for creating a method. A stored procedure is a piece of SQL that the database system pre-compiles under a specific name. The stored procedure that I use in this recipe is named addEvent.

Naturally, a web developer who is using a database will want to call these stored procedures. The java.sql.CallableStatement class encapsulates a particular stored procedure, so that you can use these tools within JDBC code.

Table 21-1 shows the table schema for the table that addEvent uses. The table has four columns: EVENT_ID, NAME, LOCATION, and RACEDATE.

Table 21-1. The RACEEVENT database table schema
















Example 21-7 shows the addEvent definition using Oracle 8i's syntax. This stored procedure takes an event name, location, and date as arguments. It then inserts these values into a new row in the RACEEVENT table.

A piece of code called a sequence named log_seq provides the value for the new row's EVENT_ID column. In Oracle's database system, a sequence can keep track of a long sequence of numbers. The database developer creates the sequence, just as they would create a stored procedure.

Example 21-7. A SQL stored procedure designed to add a row to the EVENT table
create or replace procedure addEvent(eventname in varchar2,
    location_ in varchar2,date_ in date)

as -- need to do inserts in raceevent

    insert into raceevent values(log_seq.nextval,

If you're using a database tool such as SQL PLUS from the command line, call the addEvent procedure in the following manner:

exec addEvent('Falmouth Triathlon','Falmouth MA','26-Jul-2003');

Example 21-8 shows how you can call addEvent in a servlet. The following servlet calls the stored procedure from doGet( ) in its own addRaceEvent method. This method has a java.util.List as an argument. The List contains the values that the code uses as arguments to call the addEvent stored procedure.

Example 21-8. A servlet uses CallableStatement to call the stored procedure
package com.jspservletcookbook;           

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class StoredProcServlet extends HttpServlet {

  DataSource pool;
  public void init( ) throws ServletException {
      Context env = null;
           env = (Context) new InitialContext( ).lookup("java:comp/env");
           pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
           if (pool == null)
               throw new ServletException(
                   "'oracle-8i-athletes' is an unknown DataSource");
       } catch (NamingException ne) { 
           throw new ServletException(ne);


  public void doGet(HttpServletRequest request,
    HttpServletResponse response)
      throws ServletException, java.io.IOException {
      String eventName = request.getParameter("eName");
      String location = request.getParameter("eLocation");
      String date = request.getParameter("eDate");
      List paramList = new ArrayList( );

      } catch (SQLException sqle){  
          throw new ServletException(sqle.getMessage( ));

      java.io.PrintWriter out = response.getWriter( );
      out.println("<html><head><title>Add an Event</title></head><body>");

        "<h2>The Event named "+ eventName +
          " has been added to the database</h2>");
  } //doGet
  public Connection getConnection( ){

    Connection  conn = null;
         conn = pool.getConnection( );
    } catch (SQLException sqle){
        throw new ServletException(sqle.getMessage( ));
    } finally {
      return conn;
  public void addRaceEvent(List values) throws SQLException{

      if (values == null)
          throw new SQLException(
          "Invalid parameter in addRaceEvent method.");
      Connection conn = null;
      conn = getConnection( );
      if (conn == null )
        throw new SQLException(
        "Invalid Connection in addRaceEvent method");
      Iterator it = values.iterator( ); 
      CallableStatement cs = null;
      //Create an instance of the CallableStatement
      cs = conn.prepareCall( "{call addEvent (?,?,?)}" );
      for (int i = 1; i <= values.size( ); i++)
          cs.setString(i,(String) it.next( ));
      //Call the inherited PreparedStatement.executeUpdate( ) method
      cs.executeUpdate( );
      // return the connection to the pool
      conn.close( );


Example 21-8 gets a Connection from a connection pool using the techniques explained in the prior recipes. The code uses the Connection to create a CallableStatement that the example can use to call the underlying stored procedure:

cs = conn.prepareCall( "{call addEvent (?,?,?)}" );

The String argument to the Connection's prepareCall method contains question marks (?) as placeholders for the stored procedure's parameters. The code then calls the CallableStatement's setString( ) method to give these placeholders values. Finally, the code calls the CallableStatement's executeUpdate( ) method to execute addEvent.

If calling the stored procedure causes a database error, the addRaceEvent method throws a SQLException.

The servlet receives values for the new row from request parameters. The following URL calls the servlet with three parameters: eName, eLocation, and eDate:


Figure 21-5 shows the servlet's output in a web browser.

Figure 21-5. The browser output of the StoredProcServlet

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on Tomcat; Recipe 21.4-Recipe 21.6 on using DataSources with servlets and JSPs on WebLogic; Recipe 21.8 on calling a stored procedure from a JSP; Recipe 21.9 on converting a java.sql.ResultSet object to a javax.servlet.jsp.jstl.sql Result; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section