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:

try

{

    int age = 39;

    String poetName = "dylan thomas";

    CallableStatement proc =

        connection.prepareCall("{ call set_death_age(?, ?) }");

    proc.setString(1, poetName);

    proc.setInt(2, age);

    cs.execute();

}

catch (SQLException e)

{

    // ....

}

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:

create procedure set_death_age(poet VARCHAR2, poet_age NUMBER)

    poet_id NUMBER;

begin

  SELECT id INTO poet_id FROM poets WHERE name = poet;

  INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age);

end set_death_age;

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.

Here's a Java method to call the above procedure:

public static void setDeathAge(Poet dyingBard, int age)

    throws SQLException

{

   Connection con = null;

   CallableStatement proc = null;



   try

   {

      con  = connectionPool.getConnection();

      proc = con.prepareCall("{ call set_death_age(?, ?) }");

      proc.setString(1, dyingBard.getName());

      proc.setInt(2, age);

      proc.execute();

   }

   finally

   {

      try

      {

         proc.close();

      }

      catch (SQLException e) {}

      con.close();

   }

}

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.