Stored Procedures for Java Programmers
Pages: 1, 2
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);|
Related Reading 
Java Programming with Oracle JDBC |
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.
|
Related Reading 
Practical PostgreSQL |
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
Nic Ferrier is an independent software consultant specializing in web applications.
Return to ONJava.com.
