Callable Statements and Stored Procedures
One way of improving the productivity of database developers has been to provide a means to store
commonly executed SQL statements in the database so they can be reused. These stored statements are
often called procedures or functions and they provide code reusability and other advantages to database
developers. The Stored Procedures section provides a brief overview of this functionality.
Since the classes and methods we've seen earlier in the book can be used to execute almost any kind of SQL
statement, they can also be used to create stored procedures. In the Procedure Examples section we'll look at
some simple stored procedures and show one example of using a Java class to create a stored procedure.
The DatabaseMetaData interface, which is part of the java.sql package, provides methods for
determining the support provided for stored procedures by the database and driver. The
ShowDatabaseMetaData class is modified again to demonstrate how to call these methods.
Next we look at how to actually call stored procedures from JDBC code. This capability is provided
through the CallableStatement interface, which is again part of the java.sql package. In the
CallableStatements section, we look at how to create a CallableStatement object, how to set or
register the parameters, and how to get a return value from a CallableStatement object.
In the final section of this chapter, New in JDBC 3.0, we look at new functionality for callable statements
provided by the JDBC 3.0 specification.
Stored Procedures
The first part of this chapter provides an introduction to stored procedures. If you already have
experience with stored procedures, you may want to skip the first part of this chapter and go directly
to the Java material.
Stored procedures are, as the name might suggest, procedures that are stored in the database. They
consist of some set of SQL statements and procedural language (PL) statements that can be called by
name to perform some work in the database. The stored procedure may take some input parameters as
arguments, or it may take no parameters. It can also return a value to the caller, or it can return nothing.
In this sense, stored procedures are similar to methods in Java classes; Java class methods are called by
name, they can take some or no parameters, and they can return a value or nothing (void).
In this chapter, the term 'stored procedure' is used generically to refer to both procedures and
functions. The main difference between the two is that a function returns a value and a procedure
does not return a value. If your database supports storing procedural and SQL statements in the
database for execution, but uses a different term, you should consider 'stored procedure' to be a
synonym for the term used by your database.
There are many reasons why we would use stored procedures. Some of the services provided stored
procedures are encapsulation and reuse of functionality, control of transactions, and standardization of
business rules.
Encapsulation and Re-Use
Different parts of our Music Store application will probably need to perform similar functions. For
example, out application will probably have code that performs a query of recordings for inventory
control; at the same time, there will be a query of recordings for customers to purchase. Rather than
have two places in the application with code that queries the Recordings table, we could put the query
functionality into a stored procedure. This allows any part of the application to have the ability to
perform this query. Yet, no part of the application has to know how to structure the query because
we've encapsulated the SQL into a procedure that can be called by a simple name.
If you are working with a legacy database that supports stored procedures, chances are good that many
stored procedures have already been written. These stored procedures have probably been well
exercised over the years, so we are fairly confident they are bug free. Why should we redevelop this
capability when we can reuse something that's already in place? By reusing existing stored procedures,
we can concentrate on new functionality.
Transaction Control
Basically, transactions are a way to treat a set of SQL statements as one indivisible (atomic) unit of work. If all
the statements succeed, then the changes made by the procedure during the transaction can be committed
(saved); if the one of the statements in the transaction fails, the changes can be undone. Because stored
procedures provide a means to group SQL statements together, they fit well with transaction control. The
procedure is a natural unit of work. We can start a transaction, call a stored procedure, and then either save
or undo the change when the procedure ends. Further, we can group multiple procedures together and build
larger transactions (as long as none of the procedures saves changes in its body).
We look at transactions and transaction control in greater detail in Chapter 14.
Standardization
If three different developers are providing the same functionality in different parts of the application, it
is a certainty that they will each develop the functionality in different ways. Further, if at some point in
the future this functionality needs to change, there are three places in the application where the change
must be applied. By providing this functionality in a stored procedure we can be sure that every part of
the application that uses the functionality does so in the same way. More importantly, when the
functionality is changed, there is only one place to make the fix, and then everyone who uses the
procedure automatically gets the change.
As a stored procedure in a database, this procedure also becomes accessible to any code that can
connect to the database. This is helpful in a distributed application. In a distributed application, we
might have application components running on half a dozen or more servers. If the functionality were
implemented as a Java class library, or a shared library in another language, the library would have to
be physically accessible, either directly or through a mapped drive, to all the components that needed it.
By having a procedure in a database, the code exists in one place only, yet is accessible to anyone that
can connect to the database.
Even though they provide useful features, not all databases implement stored procedures. The MySQL
database is one of those. Other databases may provide similar functionality in a different manner.
Cloudscape, for example, doesn't support storing SQL statements, but does support storing and
executing Java classes. In the next section, we'll examine some of the general requirements that apply to
creating and using stored procedures.
New on the Java Boutique:
New Review:
Time Management Made Easy with the Quartz Enterprise Job Scheduler
Why not just use the Java timer API? This open source scheduling
API boasts simplicity, ease-of-integration, a well-rounded feature
set, and it's free!
New Applet:
Reverse Complement
Reverse Complement is a simple applet that converts DNA or RNA
sequences into three useful formats.
Elsewhere on internet.com:
WebDeveloper Java
Lots of Java information on webdeveloper.com
WDVL Java
Thorough Java resource at the Web Developer's Virtual Library.
ScriptSearch Java
Hundreds of free Java code files to download.
jGuru: Your View of the Java Universe
Customizable portal with online training, FAQs, regular news updates, and tutorials.
| 
