O'Reilly logo

Java Servlet & JSP Cookbook by Bruce W. Perry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

Tip

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required