Recipe 21.7 Calling a Stored Procedure from a Servlet

Problem

You want to call a stored procedure from a servlet.

Solution

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

Discussion

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

Name

Null?

Type

EVENT_ID

NOT NULL

NUMBER

NAME

NOT NULL

VARCHAR2(30)

LOCATION

NOT NULL

VARCHAR2(30)

RACEDATE

DATE

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

begin
    insert into raceevent values(log_seq.nextval,
        eventname,location_,date_);
end;
/

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;
        
      try{
           
           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( );
      paramList.add(eventName);
      paramList.add(location);
          paramList.add(date);

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

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

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

    Connection  conn = null;
    
    try{
    
         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( );

  }//addRaceEvent
}

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:

http://localhost:8080/home/servlet/com.jspservletcookbook.
  StoredProcServlet?eName=
   Falmouth%20Triathlon&eLocation=Falmouth%20MA&eDate=26-July-2003

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

Figure 21-5. The browser output of the StoredProcServlet