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 8 i '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