Stored Procedures for Java Programmers

solar.xie

自：
java调用SP时，检查是否调用成功，不用从连接属性去判断（PB中用事务的sqlca的值），java中，检查SQLEXCEPTION, 属于系统错误，用户级别的错误，利用返回值判断。
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.
[/url]
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
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.
from:
[url=http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=1]http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=1


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/68079/showart_1422373.html