Stored Procedures for Java Programmers

Functions

Stored procedures can return values, so the CallableStatement class has methods like getResultSet to 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);

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 ResultSet operation.

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.

Conclusion

Stored procedures can help achieve logical separation in your code, which is nearly always a good thing. The benefits of this separation are:

• Applications can be built quickly, using a schema that grows and improves with the application.
• The schema can be changed later without affecting the Java objects; when we've finished the application, we can design a good schema.
• The procedure makes complex SQL easier to understand through better embedding of SQL.
• Writing stored procedures has better tool support than writing embedded SQL in Java -- most editors even provide syntax coloring!
• Stored procedures can be tested from any SQL command line, making debugging easier!

Not all databases support stored procedures, but there are many good implementations, both free/open source and non-free, so portability probably isn't an issue. Oracle, PostgreSQL, and DB2 have very similar stored procedure languages that are well supported by online communities.

Stored procedure tools are widespread. There are editors, debuggers, and IDEs such as TOAD or TORA that provide great environments for writing and maintaining PL/SQL or pl/pgsql.

Stored procedures do add overhead to your code, but they add much less overhead than most application servers. If your code is complex enough to need a DBMS, I wholly recommend adopting the stored procedure approach.

Resources

• JDBC specification
• PostgreSQL
• Oracle Corporation's Oracle database server
• IBM's DB2 database server

Nic Ferrier is an independent software consultant specializing in web applications.

Return to ONJava.com.