Database Programming with JDBC & Java, Second Edition

Chapter 4. Advanced JDBC

The only thing that makes the device a quarter-detector rather than a slug detector or a quarter-or-slug detector is the shared intention of the device’s designers, builders, owners, users. It is only in the environment or context of those users and their intentions that we can single out some of the occasions of state Q as “veridical” and others as “mistaken.”

—Daniel C. Dennett, The Intentional Stance

Chapter 3, provides all the JDBC you absolutely need to know to build database applications. If you understand all of it and then put this book away, you will probably never feel like anything is missing. That is exactly how JDBC’s creators intended the API to feel. They wanted to provide a few simple interfaces to support the majority of what database programmers want to do. Extended and complex functionality appears in extra interfaces designed specifically to support that functionality.

Advanced JDBC programming supports advanced needs. These advanced needs break down into two categories: optimizations and extended functionality. This chapter dives into all of the extended functionality included in the JDBC Core API.

Prepared SQL

Each SQL statement you send to the database needs to be parsed by the database engine before it can actually be executed. When the database parses a SQL statement, it reads the SQL to determine what you want the database to do, and then it formulates a plan for carrying out your instructions. This processing is called building a query plan.

In Chapter 3, each SQL statement you sent to the database required the database to treat the statement as a brand-new query and thus build a new query plan for it. This processing is necessary only if each statement requires a distinct query plan. If you are executing statements over and over again that have the same query plan, you are wasting processing power. If, for example, your banking application uses the SQL UPDATE ACCOUNT SET BALANCE = XXX WHERE ACCOUNT_ID = YYY, you would force the database to rebuild the same query plan each time you changed the balance for the account. Databases enable you to optimize repeated calls through prepared SQL.

Databases provide two kinds of prepared SQL: prepared statements and stored procedures. Prepared SQL provides an advantage over the simple SQL statements you have covered so far; a database can get the SQL ahead of time and create a query plan while you are doing other application logic. This means that your SQL should execute faster and that you can have a generic reference to a statement for later reuse rather than repeatedly create new SQL statements for each new access to the database.

The optimization factor comes from the database knowing what you are about to do. When you create a Java instance of a prepared statement or stored procedure, you notify the database of what kind of SQL call that object represents. The database can then create a query plan for that SQL call before you ever actually execute it. When it comes time for you to execute the SQL, the database is ready for you. If you execute the same prepared SQL more than once, the database remains ready for your SQL without having to rebuild the query plan.

Tip

You could get more performance benefits by pooling your prepared SQL resources. For example, if your code makes only a single call to a specific SQL statement, but that call appears in different places throughout the application, you could implement a prepared statement pool that holds the JDBC representation of your prepared SQL open for repeated use. This functionality may end up as a feature of JDBC 3.0.

Prepared Statements

The PreparedStatement interface extends the Statement interface you used in Chapter 3. It enables a SQL statement to contain parameters like a function definition, and you can execute a single statement repeatedly with different values for those parameters. The act of assigning values to parameters is called binding parameters. You might want to use a prepared statement when updating a group of objects stored on the same table. For example, if you were updating many bank accounts at once, you might have a loop calling:

Statement statement = c.createStatement( );
int i;

for(i=0; i<accounts.length; i++)
  statement.executeUpdate("UPDATE account " +
                          "SET balance = " + accounts[i].getBalance( ) +
                          "WHERE id = " + accounts[i].getId( ));
c.commit( );
statement.close( );

This statement creates the same query plan each time through the loop. Instead of calling this same statement repeatedly with different inputs, you can instead use a PreparedStatement:

PreparedStatement statement = c.prepareStatement(
                               "UPDATE account " +
                               "SET balance = ? " +
                               "WHERE id = ?");
int i;

for(i=0; i<accounts.length; i++) {
    statement.setFloat(1, accounts[i].getBalance( ));
    statement.setInt(2, accounts[i].getId( ));
    statement.execute( );
    statement.clearParameters( )
}
c.commit( );
statement.close( );

With a prepared statement, you send the actual SQL to the database when you get the PreparedStatement object through the prepareStatement( ) method in java.sql.Connection. Keep in mind that you have not yet actually executed any SQL. You execute that prepared SQL statement multiple times inside the for() loop, but you build the query plan only a single time.

Before each execution of the prepared statement, you tell JDBC which values to use as input for that execution of the statement. In order to bind the input parameters, PreparedStatement provides setXXX() methods (such as setFloat() and setInt()) that mirror the getXXX() methods you saw in java.sql.ResultSet. Just as the getXXX( ) methods read results according to the order in which you constructed your SQL, the setXXX() methods bind parameters from left to right in the order you placed them in the prepared statement. In the previous example, I bound parameter 1 as a float to the account balance that I retrieved from the account object. The first ? was thus associated with parameter 1.

Stored Procedures

While prepared statements let you access similar database queries through a single PreparedStatement object, stored procedures attempt to take the “black box” concept for database access one step further. A stored procedure is built inside the database before you run your application. You access that stored procedure by name at runtime. In other words, a stored procedure is almost like a method you call in the database. Stored procedures have the following advantages:

• Because the procedure is precompiled in the database for most database engines, it executes much faster than dynamic SQL, which needs to be re-interpreted each time it is issued. Even if your database does not compile the procedure before it runs, it will be precompiled for subsequent runs just like prepared statements.

• Syntax errors in the stored procedure can be caught at compile time rather than at runtime.

• Java developers need to know only the name of the procedure and its inputs and outputs. The way in which the procedure is implemented—the tables it accesses, the structure of those tables, etc.—is completely unimportant.

A stored procedure is written with variables as argument place holders, which are passed when the procedure is called through column binding. Column binding is a fancy way of specifying the parameters to a stored procedure. You will see exactly how this is done in the following examples. A Sybase stored procedure might look like this:

DROP PROCEDURE sp_select_min_bal
GO

CREATE PROCEDURE sp_select_min_bal
         @balance,
AS
SELECT account_id
FROM account
WHERE  balance > @balance

GO

The name of this stored procedure is sp_select_min_bal. It accepts a single argument identified by the @ sign. That single argument is the minimum balance. The stored procedure produces a result set containing all accounts with a balance greater than that minimum balance. While this stored procedure produces a result set, you can also have procedures that return output parameters. Here’s an even more complex stored procedure, written in Oracle’s stored procedure language, that calculates interest and returns the new balance:

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

bal := bal + bal * 0.03;

UPDATE account
SET balance = bal
WHERE account_id = id;

END;

This stored procedure accepts two arguments—the variables in the parentheses—and does complex processing that does not (and cannot) occur in the embedded SQL you have been using so far. It actually performs two SQL statements and a calculation all in one procedure. The first part grabs the current balance; the second part takes the balance and increases it by 3 percent; and the third part updates the balance. In your Java application, you could use it like this:

try {
    CallableStatement statement;
    int i;

    statement = c.prepareCall("{call sp_interest[(?,?)]}");

    statement.registerOutParameter(2, java.sql.Types.FLOAT);
    for(i=1; i<accounts.length; i++) {
        statement.setInt(1, accounts[i].getId( ));
        statement.execute( );
        System.out.println("New balance: " + statement.getFloat(2));
    }
    c.commit( );
    statement.close( );
    c.close( );
}

The CallableStatement class is very similar to the PreparedStatement class. Using prepareCall( ) instead of prepareStatement(), you indicate which procedure you want to call when you initialize your CallableStatement object. Unfortunately, this is one time when ANSI SQL2 simply is not enough for portability. Different database engines use different syntaxes for these calls. JDBC, however, does provide a database-independent, stored-procedure escape syntax in the form {call procedure_name [(?, ?)]}. For stored procedures with return values, the escape syntax is: {? = call procedure_name [(?,?)]}. In this escape syntax, each ? represents a place holder for either procedure inputs or return values. The JDBC driver then translates this escape syntax into the driver’s own stored procedure syntax.

What Kind of Statement to Use?

This book presents you with three kinds of statement classes: Statement , PreparedStatement , and CallableStatement . You use the class that corresponds to the kind of SQL you intend to use. But how do you determine which kind is best for you? The plain SQL statements represented by the Statement class are almost never a good idea. Their only place is in quick and dirty coding. While it is true that you will get no performance benefits if each call to the database is unique, plain SQL statements are also more error prone (no automatic handling of data formatting, for example) and do not read as cleanly as prepared SQL. The harder decision therefore lies between prepared statements and stored procedures. The bottom line in this decision is portability versus speed and elegance. You should thus consider the following in making your decision:

• As you can see from the Oracle and Sybase stored procedures earlier in this chapter, different databases have wildly different syntaxes for their stored procedures. While JDBC makes sure that your Java code will remain portable, the code in your stored procedures will almost never be.

• While a stored procedure is generally faster than a prepared statement, there is no guarantee that you will see better performance in stored procedures. Different databases optimize in different ways. Some precompile both prepared statements and stored procedures; others precompile neither. The only thing you know for certain is that a prepared statement is very unlikely to be faster than its stored procedure counterpart and that the stored procedure counterpart is likely to be moderately faster than the prepared statement.

• Stored procedures are truer to the black-box concept than prepared statements. The JDBC programmer needs to know only stored procedure inputs and outputs—not the underlying table structure—for a stored procedure; the programmer needs to know the underlying table structure in addition to the inputs and outputs for prepared SQL.

• Stored procedures enable you to perform complex logic inside the database. Some people view this as an argument in favor of stored procedures. In three-tier distributed systems, however, you should never have any processing logic in the database. This feature should, therefore, be avoided by three-tier developers.

If your stored procedure has output parameters, you need to register their types using registerOutParameter( ) before executing the stored procedure. This step tells JDBC what datatype the parameter in question will be. The previous example did it like this:

CallableStatement statement;
int i;

statement = c.prepareCall("{call sp_interest[(?,?)]}");
statement.registerOutParameter(2, java.sql.Types.FLOAT);

The prepareCall() method creates a stored procedure object that will make a call to the specified stored procedure. This syntax sets up the order you will use in binding parameters. By calling registerOutParameter(), you tell the CallableStatement instance to expect the second parameter as output of type float. Once this is set up, you can bind the ID using setInt(), and then get the result using getFloat().