Stored Procedures for Java Programmers

Functions

Stored procedures can return values, so the CallableStatement class has methods like getResultSetto retrieve return values. When a procedure returns a value, you must tell the JDBC driver what SQL type the value will be, with the registerOutParameter method. You must also change the procedure call specification to indicate that the procedure returns a value.

Here's a follow on from our earlier example. This time we're asking how old Dylan Thomas was when he passed away. This time, the stored procedure is in PostgreSQL's pl/pgsql:

create function snuffed_it_when (VARCHAR) returns integer '
declare
    poet_id NUMBER;
    poet_age NUMBER;
begin
    -- first get the id associated with the poet.
    SELECT id INTO poet_id FROM poets WHERE name = $1;
    -- get and return the age.
    SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id;
    return age;
end;
' language 'pl/pgsql';

As an aside, note that the pl/pgsql parameter names are referred to by the $n syntax used in Unix and DOS scripts. Also note the embedded comments; this is another advantage over Java. Writing such comments in Java is possible, of course, but they often look messy and disjointed from the SQL text, which has to be embedded in Java Strings.

Here's the Java code to call the procedure:

connection.setAutoCommit(false);
CallableStatement proc =
    connection.prepareCall("{ ? = call snuffed_it_when(?) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString(2, poetName);
cs.execute();
int age = proc.getInt(2);

Related Reading Java Programming with Oracle JDBC By Donald Bales

What happens if you specify the return type incorrectly? Well, you get a RuntimeException when the procedure is called, just as you do when you use a wrong type method in a ResultSetoperation.

Complex Return Values

Many people's knowledge of stored procedures seems to end with what we've discussed. If that's all there was to stored procedures, they wouldn't be a viable replacement for other remote execution mechanisms. Stored procedures are much more powerful.

When you execute a SQL query, the DBMS creates a database object called a cursor, which is used to iterate over each row returned from a query. A ResultSet is a representation of a cursor at a point in time. That's why, without buffering or specific database support, you can only go forward through a ResultSet.

Some DBMSs allow you to return a reference to a cursor from a stored procedure call. JDBC does not support this, but the JDBC drivers from Oracle, PostgreSQL, and DB2 all support turning the pointer to the cursor into a ResultSet.

Consider listing all of the poets who never made it to retirement age. Here's a procedure that does that and returns the open cursor, again in PostgreSQL's pl/pgsql language:

create procedure list_early_deaths () return refcursor as '
declare
    toesup refcursor;
begin
    open toesup for
        SELECT poets.name, deaths.age
        FROM poets, deaths
        -- all entries in deaths are for poets.
        -- but the table might become generic.
        WHERE poets.id = deaths.mort_id
            AND deaths.age < 60;
    return toesup;
end;
' language 'plpgsql';

Here's a Java method that calls the procedure and outputs the rows to a PrintWriter:

static void sendEarlyDeaths(PrintWriter out)
{
    Connection con = null;
    CallableStatement toesUp = null;
    try
    {
        con = ConnectionPool.getConnection();

        // PostgreSQL needs a transaction to do this...
        con.setAutoCommit(false);

        // Setup the call.
        CallableStatement toesUp
            = connection.prepareCall("{ ? = call list_early_deaths () }");
        toesUp.registerOutParameter(1, Types.OTHER);
        getResults.execute();

        ResultSet rs = (ResultSet) getResults.getObject(1);
        while (rs.next())
        {
            String name = rs.getString(1);
            int age = rs.getInt(2);
            out.println(name + " was " + age + " years old.");
        }
        rs.close();
    }
    catch (SQLException e)
    {
        // We should protect these calls.
        toesUp.close();
        con.close();
    }
}

Because returning cursors from procedures is not directly supported by JDBC, we use Types.OTHER to declare the return type of the procedure and then cast from the call to getObject().

The Java method that calls the procedure is a good example of mapping. Mapping is a way of abstracting the operations on a set. Instead of returning the set from this procedure, we can pass in the operation to perform. In this case, the operation is to print the ResultSet to an output stream. This is such a common example it was worth illustrating, but here's another Java method that calls the same procedure:

public class ProcessPoetDeaths
{
    public abstract void sendDeath(String name, int age);
}

static void mapEarlyDeaths(ProcessPoetDeaths mapper)
{
    Connection con = null;
    CallableStatement toesUp = null;
    try
    {
        con = ConnectionPool.getConnection();
        con.setAutoCommit(false);

        CallableStatement toesUp
            = connection.prepareCall("{ ? = call list_early_deaths () }");
        toesUp.registerOutParameter(1, Types.OTHER);
        getResults.execute();

        ResultSet rs = (ResultSet) getResults.getObject(1);
        while (rs.next())
        {
            String name = rs.getString(1);
            int age = rs.getInt(2);
            mapper.sendDeath(name, age);
        }
        rs.close();
    }
    catch (SQLException e)
    {
        // We should protect these calls.
        toesUp.close();
        con.close();
    }
}

This allows arbitrary operations to be performed on the ResultSet data without having to change or duplicate the method that gets the ResultSet! If we want we can rewrite the sendEarlyDeaths method:

static void sendEarlyDeaths(final PrintWriter out)
{
    ProcessPoetDeaths myMapper = new ProcessPoetDeaths()
    {
        public void sendDeath(String name, int age)
        {
            out.println(name + " was " + age + " years old.");
        }
    };
    mapEarlyDeaths(myMapper);
}

This method calls mapEarlyDeaths with an anonymous instance of the class ProcessPoetDeaths. This class instance has an implementation of the sendDeath method, which writes to the output stream in the same way as our previous example. Of course, this technique isn't specific to stored procedures, but combined with stored procedures that return ResultSets, it is a powerful tool.