Java Servlet Programming

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.

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