Stored Procedures for Java Programmers

This article explains how to use DBMS stored procedures. I explain the
basics and some advanced features such as returning ResultSets.
The article presumes you are fairly familiar with DBMSs and with JDBC. It also
assumes you're fairly comfortable reading code in a foreign language (that is,
not Java), but does not expect any previous stored procedure experience.

A stored procedure is a program that is kept and executed within a database
server. You call the procedure from a Java class using a special syntax. When
you call it, the name of the procedure and the parameters you specify are
sent over the JDBC connection to the DBMS, which executes the procedure and
returns the results (if any) back over the connection.

Using stored procedures has a lot of the same advantages as using
application servers based on EJBs or CORBA. The difference is that stored
procedures come free with lots of popular DBMSs, while application servers are
mostly expensive. This isn't just an issue of license cost. The time it takes
to administer and write code for app servers, and the increased complexity
of the client applications that rely on them, can be almost wholly replaced by a
reliance on your DBMS.

You can write your stored procedures in Java, Python, Perl, or C, but they are
most often written in a language specific to the DBMS you're using. Oracle uses
PL/SQL, PostgreSQL uses pl/pgsql, and DB2 uses Procedural SQL. These languages
are all very similar. Porting between them is no more difficult than porting
Session Beans between versions of Sun's EJB spec. In
addition, stored procedure languages are designed for embedding SQL, which
makes them much better for expressing the database mechanics than languages
like Java or C.

Because stored procedures run in the DBMS itself, they can help to reduce
latency in applications. Rather than executing four or five SQL statements in
your Java code, you just execute one stored procedure that does the operations for you on the server side. Reducing the number of network trips alone can have a
dramatic effect on performance.

Using Stored Procedures

Plain old JDBC supports calling stored procedures with the
CallableStatement class. That class is actually a subclass of
PreparedStatement. Imagine that we have a database of poets. The
database has a stored procedure to set a poet's age at death. Here's an example
of calling that stored procedure with details about the old soak Dylan
Thomas:

The string passed to the prepareCall method is the procedure
call specification. It specifies the name of the procedure to call and a
? for each parameter you need to specify.

Integration with JDBC is a big advantage for stored procedures: in order to
call a procedure from your application, you need no stub classes or config
files, and nothing except the JDBC driver for your DBMS.

When this code is executed, the database procedure is called. We don't get a
result because the procedure doesn't return one. Success or failure can be
signalled with exceptions. Failure can mean a failure when calling the
procedure (such as one of the arguments being specified with the wrong type), or
an application failure (such as throwing an exception to indicate that "Dylan Thomas" doesn't exist in the database of poets).

Combining SQL Operations with Procedures

Mapping Java objects to rows in SQL tables is simple enough, but it usually
involves executing several SQL statements; maybe a SELECT to find
a row ID followed by an INSERT of the data with the specified row
ID. In a highly normalized schema, there might be multiple tables to update, and
therefore many more statements. The Java code can quickly get bloated and the
network overhead for each statement soon adds up.

Moving all of those SQL statements into a stored procedure makes life much
simpler and involves only one network call. All of the associated SQL
operations can take place inside of the database. In addition, stored procedure
languages such as PL/SQL allow you to speak SQL much more naturally than is
possible in Java. Here's our earlier stored procedure written using Oracle's
PL/SQL language:

Unusual implementation, no? I bet you expected to see an UPDATE
on the poets table? This is an indication of how easy it is to
implement things when you use stored procedures. set_death_age is
almost certainly badly implemented. We should probably just have a column on
the poets table. It doesn't matter to the Java code what the
database schema implementation is, because our Java code just calls the
procedure. We can change the schema and the procedure later to improve
performance, but we won't have to change our Java code.

Using static methods like this is a good way to ensure maintainability. It
also makes the code that calls stored procedures into simple boilerplate code.
If you're using a lot of stored procedures, you'll find yourself just using cut
and paste to create methods. Because of the formulaic nature of the code, it's
also possible to script the production of code to call stored procedures.