Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
« Previous • Trail • Next »
SQL Statements for Creating a Stored Procedure

> A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

> Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, this simple example of what a stored procedure looks like and how it is invoked from JDBC is not intended to be run.

> This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use `begin . . . end` , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:

> >     create procedure SHOW_SUPPLIERS
> >     as
> >     select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
> >     from SUPPLIERS, COFFEES
> >     where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
> >     order by SUP_NAME

> The following code puts the SQL statement into a string and assigns it to the variable `createProcedure`, which we will use later:

> >     String createProcedure = "create procedure SHOW_SUPPLIERS " +
> >     			 "as " +
> >     			 "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
> >     			 "from SUPPLIERS, COFFEES " +
> >     			 "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
> >     			 "order by SUP_NAME";

> The following code fragment uses the `Connection` object `con` to create a `Statement` object, which is used to send the SQL statement creating the stored procedure to the database:

> >     Statement stmt = con.createStatement();
> >     stmt.executeUpdate(createProcedure);

> The procedure `SHOW_SUPPLIERS` is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

> ### Calling a Stored Procedure from JDBC

> JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a `CallableStatement` object. As with `Statement` and `PreparedStatement` objects, this is done with an open `Connection` object. A `callableStatement` object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure `SHOW_SUPPLIERS` using the connection `con`. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters `"{call SHOW_SUPPLIERS}"`, it will translate this escape syntax into the native SQL used by the database to call the stored procedure named `SHOW_SUPPLIERS`.

> >     CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
> >     ResultSet rs = cs.executeQuery();

> The `ResultSet` `rs` will be similar to the following:

> >     SUP_NAME			COF_NAME
> >     ----------------		-----------------------
> >     Acme, Inc.			Colombian
> >     Acme, Inc.			Colombian_Decaf
> >     Superior Coffee			French_Roast
> >     Superior Coffee			French_Roast_Decaf
> >     The High Ground			Espresso

> Note that the method used to execute `cs` is `executeQuery` because `cs` calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method `executeUpdate` would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method `execute` should be used to execute the `CallableStatement` .

> The class `CallableStatement` is a subclass of `PreparedStatement`, so a `CallableStatement` object can take input parameters just as a `PreparedStatement` object can. In addition, a `CallableStatement` object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method `execute` are used rarely.

« Previous • Trail • Next »
Previous page: Stored Procedures
Next page: Creating Complete JDBC Applications