Chapter 9: Database Connectivity

9.5. Advanced JDBC Techniques

Now that we've covered the basics, let's talk about a few advanced techniques that use servlets and JDBC. First, we'll examine how servlets can access stored database procedures. Then we'll look at how servlets can fetch complicated data types, such as binary data (images, applications, etc.), large quantities of text, or even executable database-manipulation code, from a database.

9.5.1. Stored Procedures

Most RDBMS systems include some sort of internal programming language. One example is Oracle's PL/SQL. These languages allow database developers to embed procedural application code directly within a database and then call that code from other applications. RDMBS programming languages are often well suited to performing certain database actions; many existing database installations have a number of useful stored procedures already written and ready to go. Most introductions to JDBC tend to skip over this topic, so we'll cover it here briefly.

The following code is an Oracle PL/SQL stored procedure. If it looks familiar, that's because it's from George Reese's Database Programming with JDBC (O'Reilly):

CREATE OR REPLACE PROCEDURE sp_interest
(id IN INTEGER
bal IN OUT FLOAT) IS
BEGIN
SELECT balance
INTO bal
FROM accounts
WHERE account_id = id;

bal := bal + bal * 0.03;

UPDATE accounts
SET balance = bal
WHERE account_id = id;

END;

This procedure executes a SQL statement, performs a calculation, and executes another SQL statement. It would be fairly simple to write the SQL to handle this (in fact, the transaction example earlier in this chapter does something similar), so why bother with this at all? There are several reasons:

• Stored procedures are precompiled in the RDBMS, so they run faster than dynamic SQL.

• Stored procedures execute entirely within the RDBMS, so they can perform multiple queries and updates without network traffic.

• Stored procedures allow you to write database manipulation code once and use it across multiple applications in multiple languages.

• Changes in the underlying table structures require changes only in the stored procedures that access them; applications using the database are unaffected.

• Many older databases already have a lot of code written as stored procedures, and it would be nice to be able to leverage that effort.

The Oracle PL/SQL procedure in our example takes an input value, in this case an account ID, and returns an updated balance. While each database has its own syntax for accessing stored procedures, JDBC creates a standardized escape sequence for accessing stored procedures using the java.sql.CallableStatement class. The syntax for a procedure that doesn't return a result is "{call procedure_name(?,?)}". The syntax for a stored procedure that returns a result value is "{? = call procedure_name(?,?)}". The parameters inside the parentheses are optional.

Using the CallableStatement class is similar to using the PreparedStatement class:

CallableStatment cstmt = con.prepareCall("{call sp_interest(?,?)}");
cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
cstmt.setInt(1, accountID);
cstmt.execute();
out.println("New Balance: " + cstmt.getFloat(2));

This code first creates a CallableStatement using the prepareCall() method of Connection. Because this stored procedure has an output parameter, it uses the registerOutParameter() method of CallableStatement to identify that parameter as an output parameter of type FLOAT. Finally, the code executes the stored procedure and uses the getFloat() method of CallableStatement to display the new balance. The getXXX() methods in CallableStatement interface are similar to those in the ResultSet interface.

9.5.2. Binaries and Books

Most databases support data types to handle text strings up to several gigabytes in size, as well as binary information like multimedia files. Different databases handle this kind of data in different ways, but the JDBC methods for retrieving it are standard. The getAsciiStream() method of ResultSet handles large text strings; getBinaryStream() works for large binary objects. Each of these methods returns an InputStream.

Support for large data types is one of the most common sources of JDBC problems. Make sure you test your drivers thoroughly, using the largest pieces of data your application will encounter. Oracle's JDBC driver is particularly prone to errors in this area.

Here's some code from a message board servlet that demonstrates reading a long ASCII string. We can assume that connections, statements, and so on have already been created:

try {
  ResultSet rs = stmt.executeQuery(
    "SELECT TITLE, SENDER, MESSAGE FROM MESSAGES WHERE MESSAGE_ID = 9");
  if (rs.next()) {
    out.println("<H1>" + rs.getString("title") + "</H1>");
    out.println("<B>From:</B> " + rs.getString("sender") + "<BR>");
    BufferedReader msgText = new BufferedReader(
      new InputStreamReader(rs.getAsciiStream("message")));
    while (msgText.ready()) {
      out.println(msgText.readLine());
    }
  }
}
catch (SQLException e) {
  // Report it
}

While it is reading from the InputStream, this servlet doesn't get the value of any other columns in the result set. This is important because calling any other getXXX() method of ResultSet closes the InputStream .

Binary data can be retrieved in the same manner using the ResultSet.getBinaryStream() . In this case, we need to set the content type as appropriate and write the output as bytes. Example 9-9 shows a servlet that returns a GIF file loaded from a database.

9.4. Transactions		10. Applet-Servlet Communication

Copyright © 2001 O'Reilly & Associates. All rights reserved.