Recipe 21.8 Calling a Stored Procedure from a JSP

Problem

You want to call a stored procedure from a JSP.

Solution

Using a JSP 2.0 container, develop an Expression Language (EL) function that will call the stored procedure for you.

Discussion

JSP 2.0 introduced functions, which are static methods that you can call inside EL statements.

See Chapter 23 if you need to familiarize yourself with the EL.

This recipe explains the steps for developing a function that calls a stored procedure:

Create the stored procedure in your database system.
Write the Java class that implements the function as a static or class method.
Define the function in a Tag Library Descriptor (TLD), which is an XML configuration file that you incude with the web application.
In the JSP itself, use the taglib directive to declare the tag library that contains the function.
Call the function in the JSP, using the proper prefix for your tag library. The function I use in this recipe looks like this:
```
<cbck:addRaceEvent("My Race", "Anytown USA", "11-Dec-2003") />
```

Example 21-9 shows the Java class that implements this function.

Example 21-9. The Java class that implements an EL function

package com.jspservletcookbook;           

import java.sql.*;

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


public class StoredProcUtil {

  private static DataSource pool;
  private static Context env;
  
  static { //static initialization of the Context and DataSource
 
      try{
           
           env = (Context) new InitialContext( ).lookup("java:comp/env");
             
           pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
             
           if (pool == null)
               throw new Exception(
                   "'oracle-8i-athletes' is an unknown DataSource");
             
       } catch (Exception e) { 
        
            System.out.println(e);

       }
 
 }//static

  /* This static method will be configured in a TLD file and provide the 
  implementation for an EL function. An example use of the function is:
  <cbck:addRaceEvent("My Race","Anytown USA","11-Dec-2003") /> */

  public static void addRaceEvent(String name,String location,String date) {
    
      if( (! check(name)) || (! check(location)) || (! check(date)))
          throw new IllegalArgumentException(
            "Invalid param values passed to addRaceEvent( )");
            
      Connection conn = null;
        
      try{
        
          conn = pool.getConnection( );
        
       if (conn == null )
          throw new SQLException(
          "Invalid Connection in addRaceEvent method");
        
      CallableStatement cs = null;
        
      //Create an instance of the CallableStatement
      cs = conn.prepareCall( "{call addEvent (?,?,?)}" );
        
      cs.setString(1,name); 
      cs.setString(2,location); 
      cs.setString(3,date); 
        
      //Call the inherited PreparedStatement.executeUpdate( ) method
      cs.executeUpdate( );
        
      // return the connection to the pool
      conn.close( );
       
      } catch (SQLException sqle) { }
       

  }//addRaceEvent
  
  private static boolean check(String value){
    
        if(value == null || value.equals(""))
            return false;
            
        return true;
  }
}

The addRaceEvent( ) method creates a java.sql.CallableStatement, which calls the underlying stored procedure (addEvent). Recipe 21.7 explains this process.

The Java method that implements the function for a JSP must be defined as static.

This Java class must be stored in your web application beneath the WEB-INF/classes directory (with a subdirectory structure matching its package name) or in a JAR file stored in WEB-INF/lib. For example, the Java class of Example 21-9 should be stored in WEB-INF/classes/com/jspservletcookbook/StoredProcUtil.class.

Example 21-10 shows the TLD file that defines the EL function.

The TLD file has a .tld extension and lives in a WEB-INF subdirectory of your web application, such as WEB-INF/tlds.

Example 21-10. The TLD file for configuring the EL function

<taglib xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi=
  "http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=
  "http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/
   web-jsptaglibrary_2_0.xsd"
   version="2.0"
>

    <tlib-version>1.0</tlib-version>
    <jsp-version>2.0</jsp-version>
    <short-name>cbck</short-name>
    <uri>jspservletcookbook.com.tags</uri>
    <description>Cookbook custom tags</description>
    
    <function>

        <name>addRaceEvent</name>

        <function-class>
            com.jspservletcookbook.StoredProcUtil
        </function-class>

        <function-signature>
            void addRaceEvent(java.lang.String,
            java.lang.String,java.lang.String)
        </function-signature>

    </function>

    <tag>
        <!-- define a custom tag here if you have to -->
    </tag>

</taglib>

Example 21-10 defines the function with the function tag and its name, function-class, and function-signature attributes. Make sure to include the fully qualified class name under function-class. The JSP container knows how to call the function by inspecting the function-signature. This signature includes the return type ("void" in this case), the function name, and all of its parameters specified by their fully qualified class names.

Example 21-11 is a JSP that calls our defined function. First, the taglib directive declares the tag library and prefix ("cbck") that the function uses.

Example 21-11. A JSP uses an EL function to call a stored procedure

<%@ taglib uri="jspservletcookbook.com.tags" prefix="cbck" %>

<html>
<head><title>Calling a Stored procedure</title></head>
<body>
<h2>This JSP calls a stored procedure with a JSP 2.0 function</h2>

${cbck:addRaceEvent("Falmouth Triathlon","Falmouth MA","26-Jul-2003")}

</body>
</html>

Since this is a feature of the EL, the syntax encapsulates the function call within the "${ }" character string. Next comes the prefix (cbck), a colon, and the function call itself:

${cbck:addRaceEvent("Falmouth Triathlon","Falmouth MA","26-Jul-2003")}

This process appears complicated the first time around, but once you create your first JSP 2.0 function, the rest of them will be much easier! This feature does not involve much more than creating a static Java method, configuring the function with the proper values in an XML file, then calling the function in a JSP. This is a nifty way to call stored procedures!