home movie radio music chord lyrics book game Dictionary clip
HOME HAND MADE RADIO SHOP CHORD LYRICS BOOKS GAME Dictionary Clip
[ Team LiB ] Previous Section Next Section

11.2 Simple Database Access

At this point, you have the basics for connecting to a database. Example 11-1, however, did not really do much. It showed you how to make a database connection and perform simplistic result set management. You are unlikely to ever have such basic functionality in your applications. The basic database behavior common to most applications, however, does not require significantly more complex code.

11.2.1 The Connection

JDBC represents a connection to a database through the Connection interface. Thus, connecting to a database requires you to get an instance of the Connection interface from your JDBC driver. JDBC supports two ways of getting access to a database connection:

  • Through the JDBC DataSource (as shown in Example 11-1)

  • Using the JDBC DriverManager

The data source method is the preferred approach to database connectivity. Data sources, however, tend to be usable only in application server contexts. You should therefore understand both forms of connectivity since you can rely on DriverManager connectivity no matter what environment you are working in.

Connection Troubles

The JDBC connection process is the most difficult part of JDBC to get right. The API itself is very straightforward, but many "gotchas" hide right beneath the surface in the configuration of your environment. If you used the data source approach, you are less likely to run into configuration problems. Unfortunately, this approach is only commonly available to applications running inside a J2EE application server. If you do run into problems making a connection, check whether these problems match the following:

Connection fails with the message "Class not found"

This message usually results from not having the JDBC driver in your CLASSPATH. You should remember to enter .zip and .jar files explicitly in your CLASSPATH. If you put all of your application .class files and your driver .jar file (for this example, driver.jar) in C:\lib, your CLASSPATH should read C:\lib;C:\lib\driver.jar.

Connection fails with the message "Driver not found"

You did not register your JDBC driver with the DriverManager class. This chapter describes several ways to register your JDBC driver.


11.2.1.1 DataSource connectivity

Data source connectivity is very simple. In fact, the following code makes a connection to any database for any application:

Context ctx = new InitialContext( );
DataSource ds = (DataSource)ctx.lookup("jdbc/dsn");
Connection conn = ds.getConnection( );

The only requirement is that you have a JNDI-supported directory service containing a DataSource configured with the name jdbc/dsn.

The first line gets an InitialContext object in accordance with the JNDI[1] specification. If your environment isn't set up properly, you catch a javax.naming.NoInitialContextException. The InitialContext enables you to navigate a directory service. You use it in your database applications to look up a JDBC DataSource instance. Finally, once you have found that DataSource, you use it to make a connection.

[1] You should not be too concerned if you are not familiar with JNDI. For the purposes of basic JDBC connectivity, the preceding three lines of JNDI code are all you need to know. The only thing that changes from application to application is the name of the data source.

Technically, DataSource connectivity does not require a directory service. Instead, you can serialize a configured DataSource instance out to a filesystem or load one in some other fashion. You will nevertheless find that the most common situation you will encounter is looking one up in a directory service via JNDI.

You may be wondering how the DataSource gets into the directory service so you can look it up. JNDI does provide a process for binding objects into a directory service:

SomeDataSourceClass ds = new SomeDataSourceClass( );
Context ctx = new InitialContext( );
   
// configure the data source through various setter methods
ctx.bind("jdbc/dsn", ds);

I have two bits of magic in this code. First, the class SomeDataSourceClass is an implementation of javax.sql.DataSource—generally written by your database or application server vendor. Next, the configuration of your data source—the step I commented out—is highly dependent on the DataSource implementation you are using. In some cases, you may configure a user ID, password, database name, and server name. You will likely specify much more.

Fortunately, few programmers actually write code to bind data sources to JNDI directory services. You will mostly encounter the need to configure a data source via XML or some other means. A sample data-sources.xml file from the Orion application server looks like this:

<?xml version="1.0"?>
<!DOCTYPE data-sources PUBLIC 
  "Orion data-sources" 
  "http://www.orionserver.com/dtds/data-sources.dtd">
   
<data-sources>
        <data-source class="com.evermind.sql.DriverManagerDataSource"
                     name="dsn"
                     location="jdbc/dsn"
                     xa-location="jdbc/xa/HypersonicXADS"
                     ejb-location="jdbc/HypersonicDS"
                     connection-driver="org.gjt.mm.mysql.Driver"
                     username="dvl"
                     password="dvl"
                     url="jdbc:mysql://localhost/dvl"
                     inactivity-timeout="30"/>
</data-sources>

This configuration file places a DataSource instance in the application server's directory service that enables me to connect to a MySQL database named "dvl" on the localhost using the user ID "dvl" and password "dvl".

11.2.1.2 DriverManager connectivity

One of the few implementation classes in the java.sql package is the class DriverManager. It maintains a list of implementations of the java.sql.Driver interface and provides you with connections based on a JDBC URL that you provide. This JDBC URL comes in the form jdbc:protocol:subprotocol. This URL tells a DriverManager which database engine you wish to connect to and provides the DriverManager with enough information to make a connection.

JDBC uses the word "driver" in multiple contexts. When lowercase, a JDBC driver is the collection of classes that together implement the JDBC specification. When uppercase, the Driver is the class that implements java.sql.Driver. Finally, JDBC provides a DriverManager that can be used to keep track of all the different Driver implementations.


The protocol part of the URL refers to a given JDBC driver. The protocol for the GNU MySQL driver, for example, is mysql. The subprotocol provides the implementation-specific connection information. Most drivers minimally require the name of a database to connect to. It is also common to specify a host and even a port number in the subprotocol.

Each driver's JDBC URL is different, and so I cannot say anything more explicit that will tell you what the proper URL is for your driver. Your driver documentation, however, should have easy-to-find documentation describing the exact form of its JDBC URL. Whatever the format of the URL, the primary function of the URL is to uniquely identify the driver needed by the application and pass that driver any information it needs to make a connection to the proper database.

Before you can use a URL to get a connection from the DriverManager, you first need to register your Driver implementation with the DriverManager. You have two main options for registering a Driver:

  • Specify the names of the Driver implementation classes you want to register on the command line of your application using the jdbc.drivers property:

    java -Djdbc.drivers=com.caucho.jdbc.mysql.Driver MyAppClass
  • Explicitly load the class in your program using a new statement or a Class.forName( ):

    Class.forName("com.caucho.jdbc.mysql.Driver").newInstance( );

For portability's sake, I recommend that you put all configuration information in some sort of configuration file, such as a properties file, then load the configuration data at runtime. By taking this approach, your application will not rely on a particular database or JDBC driver. You can simply change the values in the configuration file to move from one driver to another or one database to another.

Once you register a driver, you can ask the DriverManager for a Connection by calling the getConnection( ) method in the driver with the information identifying the desired connection. This information minimally includes a JDBC URL, a user ID, and a password:

Connection conn = 
  DriverManager.getConnection("jdbc:mysql:/localhost/Web",
                                  "userID", "password");

This code returns a connection to the MySQL database named Web using the GNU MySQL driver. This connection occurs under the permissions of the user "userID" identified by the password "password".

An alternative signature to this method enables you to specify a Properties object that may contain values beyond the basic user ID and password:

Properties p = new Properties( );
Connection conn;
   
p.put("user", "userID");
p.put("password", "password");
p.put("encoding"", "UTF-8");
conn = DriverManager.getConnection("jdbc:mysql:/localhost/Web", p);

Example 11-2 provides a full example that connects to a MySQL database.

Example 11-2. Using the DriverManager to make a connection
import java.sql.*;
import java.util.Properties;
   
public class Connect {
    static public void main(String[  ] args) {
        Connection conn = null;
 
        try {
            String url = "jdbc:mysql:/localhost/Web";
            Properties p = new Properties( );
   
            Class.forName("org.gjt.mm.mysql.Driver").
                newInstance( );
            p.put("user", "dvl");
            p.put("password", "password");
            conn = DriverManager.getConnection(url, p);
        }
        catch( SQLException e ) {
            e.printStackTrace( );
        }
        finally {
            if( conn != null ) {
                try { conn.close( ); }
                catch( SQLException e ) { }
            }
        }
    }
}

In this example, I have hardcoded the driver name and connection information in the application. The only reason this is acceptable is because it is an example showing all of the elements of making a connection. In practice, you will always want to follow the best practices in the next section that avoid hardcoding these values.

11.2.1.3 Portability through properties

Java is a language based on the concept of portability. To most people, portability means that you do not write code that will run on only one platform. In the Java world, however, portability means no proprietary dependencies—and that means no database dependencies.

I touched earlier on how the JDBC URL and Driver implementation classes are driver-specific. Because both values are simple strings, you can pass them as command-line arguments or applet parameters. Unfortunately, this approach is hardly elegant since it requires users to remember long command lines or to pass authentication credentials as HTML to an applet tag.

You could, of course, prompt the user for this information. However, this approach demands that the user know a JDBC URL and driver name. The elegant approach is the use of properties files. Java supports the concept of properties-based application configuration through java.util.ResourceBundle and its subclasses.

Using a properties file, you can store all configuration information like the JDBC URL, driver class, user ID, and password and change it as the runtime environment changes. Example 11-3 is a sample properties file.

Example 11-3. A properties file containing driver configuration data
url=jdbc:mysql:/localhost/Web
driver=org.gjt.mm.mysql.Driver
user=dvl
password=dvl

You can now turn Example 11-2 into a portable example of making a connection as shown in Example 11-4.

Example 11-4. Using a properties file to achieve portability
import java.sql.*;
import java.util.*
   
public class Connect {
    static public void main(String[  ] args) {
        Connection conn = null;
 
        try {
            ResourceBundle bdl = ResourceBundle.getBundle("connect");
            String url = bdl.getString("url");
            Properties p = new Propertes( );;
            Enumeration keys = bdl.keys( );
            
            while(  keys.hasMoreElements( ) ) {
                String prop = (String)keys.nextElement( );
                String val = bdl.getString(prop);
   
                p.setProperty(prop, val);
            }
            Class.forName(bld.getString("driver")).newInstance( );
            conn = DriverManager.getConnection(url, p);
        }
        catch( SQLException e ) {
            e.printStackTrace( );
        }
        finally {
            if( conn != null ) {
                try { conn.close( ); }
                catch( SQLException e ) { }
            }
        }
    }
}

We no longer have any code specific to MySQL or the GNU driver. This code will now work against any database engine using any JDBC driver, simply through changing the properties file.

11.2.2 Query Execution

The most basic element of communication over a Connection is the Statement. Your application encapsulates SQL queries into a Statement or one of its subclasses and processes the results. An SQL query can be an INSERT, UPDATE, DELETE, or any other valid SQL statement.

11.2.2.1 Simple queries

The Connection class enables you to create Statement instances via the createStatement( ) method:

Statement stmt = conn.createStatement( );

You can then use that statement to send SQL to the database:

stmt.executeUpdate("UPDATE test SET val = 'cheese' WHERE id = 1");

In this case, we are sending SQL that modifies the database. If the SQL returned results, however, we would use the executeQuery( ) method and get back an instance of ResultSet:

ResultSet rs = stmt.executeQuery("SELECT id, val FROM test");

Example 11-5 shows a query returning results and the processing of those results.

Example 11-5. A query that returns results for processing
Connection conn = null;
   
try {
    Statement stmt = conn.createStatement( );
    ResultSet rs = stmt.executeQuery("SELECT id, val FROM test");
   
    while( rs.next( ) ) {
        System.out.println("ID: " + rs.getInt(1) + ", rs.getString(2));
    }
}

The query in Example 11-5 retrieves every row from the table test. The JDBC code loops through those rows and displays the values for each row's columns.

SQL NULL Versus Java null

SQL and Java do not match up the way they treat the absence of value—null. Specifically, any SQL value can be NULL. In Java, however, only object types can have null values. After retrieving a value from a ResultSet, your Java application needs to ask the ResultSet if the value retrieved is a driver representation of NULL. For example, a call to rs.getInt( ) might return 0 even though the underlying database value for the column is NULL. To find out if the value is actually 0 or NULL, you should call rs.wasNull( ).


Until the first call to next( ), the result set does not point to any row returned by the query. The first call makes the result set point to the first row. Until the next call to next( ), any operations you perform on the result set act on that row. Subsequent calls to next( ) move the result set forward through the rows in the result set. In this example, I move through each row until the next( ) method returns false. A return value of false indicates that there is no next row to move the result set to.

Dealing with a row means retrieving the values for its columns. Whatever the value in the database, you can retrieve it using a getter method in the ResultSet interface. In Example 11-5, I used getInt( ) to retrieve the id column and getString( ) to retrieve the val column. These getter methods can accept either the number of the column—starting with 1—or the column name. You should, however, avoid retrieving columns by name because it is generally much slower than retrieving them by number.

11.2.2.2 Scrollable result sets

By default, you are limited to simple forward navigation through a result set. JDBC does, however, provide a tool for navigating backward and forward through a result set; it is called a scrollable result set. You can get scrollable result sets back from your queries if you indicate that you want a scrollable result set when you create your statement:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_READ_ONLY);

The first argument indicates that you want a scrollable result set. The second argument indicates that you want a read-only result set. Non-read-only result sets are an advanced topic beyond the scope of this chapter.

With a scrollable result set, you can make calls to:


previous( )

To navigate backward through the result set. previous( ) moves the result set over one row—except it moves the result set to the row before the current row. It will return false if there is no previous row to move to.


absolute( )

To move to an arbitrary row (similar to next( )). absolute( ) requires the number of the row to which you want to navigate,


relative( )

To move to an arbitrary row (similar to next( )). relative( ) moves the number of rows you specify forward or backward. A negative number moves the result set backward, and a positive number forward. Thus, relative(1) is like next( ), and relative(-1) is like previous( ).

11.2.3 Transactions

Chapter 3 covered the role of transactions in database programming. The critical job of a transaction is to take the database from one consistent state to another. Your database handles many of the complexities of transaction management. When you modify a table, the underlying database acquires the appropriate locks and guarantees that your changes do not conflict with those of another client.

In order for the database to properly manage your transactions, your application needs to tell it what operations constitute a single transaction. By default, JDBC treats every distinct SQL execution as a transaction. This default is called auto-commit. In other words, each statement is committed the minute it completes unless there is application logic to the contrary. The following code updates the balance of a bank account in the default auto-commit mode:

float ob = account.getBalance( );
Connection conn = null;
   
account.calculateInterest( );
try {
    Statement stmt = null;
   
    conn = ds.getConnection( );
    stmt = conn.createStatement( );
    stmt.executeUpdate("UPDATE account SET balance = " + 
        account.getBalance( ) +
" WHERE id = " + account.getId( ));
}
catch( SQLException e ) {
    account.resetBalance(ob);
}
catch( Error e ) {
    account.resetBalance(ob);
    throw e;
}
catch( RuntimException e ) {
    account.resetBalance(ob);
    throw e;
}
finally {
    if( conn != null ) {
        try { conn.close( ); } 
        catch( SQLException e ) { }
    }
}

From a JDBC perspective, nothing in this sample code differs from what you have done so far. What differs is the exception handling so that your application returns to a state consistent with the database. In this case, when an exception occurs, the account object gets its balance set back to the value prior to calculating interest.

11.2.3.1 Basic transaction management

Rarely are transactions as simplistic as the previous one. A given transaction can make numerous modifications that need to occur together or not at all. The classic example of such a transaction is a transfer of funds from your savings account to your checking account. This transaction includes the following steps:

  1. Debit the savings account.

  2. Credit the checking account.

If the credit to the checking account fails for whatever reason, you as the account holder certainly want that money recredited to the savings account. In JDBC's default auto-commit mode, there is no sure way to achieve this consistency. You therefore need to turn auto-commit off and manually tell JDBC where to commit the transaction. You also need to handle errors so that it will get rolled back in exceptional conditions. The following code shows this account transfer:

float sb = savings.getBalance( );
float cb = checking.getBalance( );
Connection conn = null;
boolean success = false;
   
savings.transfer(checking, 10.00);
try {
    Statement stmt = null;
   
    conn = ds.getConnection( );
    conn.setAutoCommit(false);
    stmt = conn.createStatement( );
    stmt.executeUpdate("UPDATE account SET balance = " + 
                         savings.getBalance( ) +
                       " WHERE id = " + asavings.getId( ));
     stmt.executeUpdate("UPDATE account SET balance = " + 
                          checking.getBalance( ) +
                       " WHERE id = " + achecking.getId( ));
    success = true;
}
catch( SQLException e ) {
    e.printStackTrace( );
}
finally {
    if( conn != null ) {
        if( success ) {
            try { conn.commit( ); }
            catch( SQLException e ) { 
                savings.resetBalance(sb);
                checking.resetBalance(cb);
                try { conn.rollback( ); }
                catch( SQLException e ) { }
            }
        }
        else {
            savings.resetBalance(sb);
            checking.resetBalance(cb);
            try { conn.rollback( ); }
            catch( SQLException e ) {  }
        }
        try { conn.close( ); } 
        catch( SQLException e ) { }
    }
}

The bold sections illustrate what changes for multi-statement transactions. First, you need to turn off auto-commit using setAutoCommit(false). You then execute transactions as you always would. When done, you either commit the transaction (using commit( )) or roll it back (using rollback( )). In this sample, I have tracked the success or failure of the transaction and I perform the commit and rollback in the finally block.

11.2.3.2 Savepoints

As described earlier, transactions work well when you have a very straightforward beginning state with only one possible consistent end state. In other words, the database starts with one set of values and should end up with another specific set of values when the transaction completes. Some situations, however, allow for multiple possible consistent end states dependent on the events that occur during the course of the transaction. Such transactions require a much more fine grained approach to transaction management than the commit/rollback scheme allows. JDBC manages these transactions through savepoints.

A savepoint is a JDBC tool for marking a database state as a possible final consistent state for a transaction. Specifically, you can execute a statement and then establish a savepoint with the connection. Depending on what happens in the transaction, you can commit the transaction, roll it back, or roll it back to the savepoint. If you roll back to the savepoint, you can chose to commit that work or execute an alternative flow for the transaction.

The mechanics of savepoints are simple:

Connection conn = ds.getConnection( );
Statement stmt = conn.createStatement( );
Savepoint sp;
   
stmt.executeUpdate(
    "INSERT INTO test ( id, val ) VALUES ( 1, 'test')");
sp = conn.setSavepoint("safety");
stmt.executeUpdate(
    "INSERT INTO other ( id, name ) VALUES ( 32, 'sample')");
try {
    stmt.executeUpdate("UPDATE test SET other = 32 WHERE id = 1");
}
catch( SQLException e ) {
    conn.rollback(sp);
}
conn.commit( );

The application sets the savepoint after the first SQL. It can now guarantee that no matter what else happens, it can return the database to a state in which a new row is in the test table without missing references or unreferenced values in the other table. If an error occurs in the SQL updating the test table so that the new test value points to the new other value, then the transaction is rolled back so that the test value exists alone in the database.

This example begs the question: why not simply commit after the first statement? One reason is performance. You do not need to release and reacquire any locks to execute the transaction. The other reason, however, would be in more complex transactions in which some branches of logic after the savepoint is set need to roll back completely while others need to roll back only to the savepoint.

To illustrate the kind of logic to which savepoints apply, consider a game in which you toss marbles into a jar. The goal of the game is to end up with the most marbles in the jar. You start the game with no marbles and continue tossing marbles into the jar until your fourth miss or you call it quits. Your score is the number of marbles in the jar after your fourth miss.

The trick behind scoring, however, is that missing two in a row returns your marble count to its number after your first miss. If you miss three in a row, the jar is emptied and your final score is whatever you can get in the jar before your next miss.

In transactional terms, the following events occur:

  • First miss: set a savepoint.

  • Second consecutive miss: roll back to the savepoint.

  • Third consecutive miss: roll back the entire transaction.

  • Fourth miss: commit.

11.2.4 Error Handling and Cleanup

All JDBC method calls can throw SQLException or one of its subclasses if something happens during a database call. Your code should be set up to catch this exception, deal with it, and clean up any database resources that have been allocated. The basic skeleton of any JDBC code I write looks like this:

Connection conn = null;
   
try {
    // create the connection and execute your transaction
}
catch( SQLException e ) {
    // handle the exception
}
finally {
    if( conn != null ) {
        try { conn.close( ); }
        catch( SQLException e ) { }
    }
}

Each of the major JDBC interfaces you have encountered—Connection, Statement, and ResultSet—has a close( ) method. Practically speaking, however, you need only make sure you close your connection instances because closing a connection closes all associated statements. The closing of a statement, in turn, closes all associated result sets. By closing the connection in the finally clause, you guarantee that the connection will be closed even when an error occurs.

11.2.5 Prepared SQL

JDBC Statement instances illustrate basic database programming well, but you rarely want to use them in practice. Unfortunately, Statement sends your SQL to the database each time you execute it. It provides the database with very little opportunity to optimize repeated SQL calls.

Consider the following SQL:

UPDATE account SET balance = 5.00 WHERE id = 2

If you use a Statement to support updating many accounts with a similar SQL call, the database has to process the SQL and determine how it will execute the query every single time you send it to the database. You can avoid this overhead, however, through prepared SQL.

Databases support 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. Your SQL should therefore execute faster. Furthermore, you have a generic reference to the statement for later reuse instead of repeatedly re-creating similar SQL statements.

What Kind of Statement to Use

This tutorial introduces three kinds of JDBC statements. Each kind of statement—even java.sql.Statement—provides performance benefits under certain situations. Unfortunately, you can rarely be certain which kind of statement will definitely provide you with the best performance for a given SQL call without knowing details about the underlying database. I recommend the use of java.sql.PreparedStatement except in a few situations in which stored procedures are demanded. In other words, you should always avoid java.sql.Statement. It almost never is the best choice for optimal performance. Even in the few situations in which it provides the optimal performance, it is uglier code, more error-prone, and more difficult to maintain. I recommend the use of stored procedures only for complex SQL calls that are known to be bottlenecks as prepared statements.


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 SQL you intend to be calling without providing any specific values. For example, the SQL to update account balances looks like this as a prepared statement:

UPDATE account SET balance = ? WHERE id = ?

Instead of sending this SQL as an argument to executeUpdate( ), you pass it to the connection when you create the statement. You then assign values to the two placeholders and finish by calling executeUpdate( ). If you want to make further calls to update other accounts, you can reassign the statement new values and call executeUpdate( ) again.

11.2.5.1 Prepared statements

The PreparedStatement interface extends the Statement interface we used earlier in the chapter. It enables a SQL statement to contain parameters like a function call. You can execute a single statement repeatedly with different values. 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 in the same table. For example, if you update many bank accounts as described earlier, you might have a loop like this:

Statement stmt = conn.createStatement( );
   
for(int i=0; i<accounts.length; i++) {
    stmt.executeUpdate("UPDATE account " +
                       "SET balance = " + 
                       accounts[i].getBalance( ) + " " +
                       "WHERE id = " + accounts[i].getId( ));
}
conn.commit( );

This statement keeps sending slightly different SQL to the database each time it goes through the loop. Instead of calling this statement repeatedly with different inputs, you can instead use a PreparedStatement:

PreparedStatement stmt = conn.prepareStatement("UPDATE account " +
                                               "SET balance = ? " +
                                               "WHERE id = ?");
   
for(int i=0; i<accounts.length; i++) {
    stmt.setFloat(1, accounts[i].getBalance( ));
    stmt.setInt(2, accounts[i].getId( ));
    stmt.executeUpdate( );
    stmt.clearParameters( );
}
conn.commit( );

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 setter methods—like setFloat( ) and setInt( )—that mirror the getter methods you saw in ResultSet. Just as the getter methods read results according to the order in which you constructed your SQL, the setter 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.

11.2.5.2 Stored procedures

While prepared statements enable you to 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. Even if your database does not compile the stored 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 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 is totally irrelevant.

The downside to stored procedures, however, is that every database has its own stored procedure language. If you use stored procedures heavily, you can go to great lengths to make sure your Java application is database-independent yet still be tied to a specific database because of the stored procedures. Worse, different databases do not even share basic semantics that would facilitate porting between database engines. For example, you can retrieve results from a Sybase stored procedure using a plain ResultSet. With Oracle, however, retrieving results from a stored procedure is much more complex.

Using stored procedures, we can revise the balance updating code to the following:

CallableStatement stmt = conn.prepareCall("{call sp_balance(?,?)}");
   
for(int i=0; i<accounts.length; i++) {
    stmt.setInt(1, accounts[i].getId( ));
    stmt.setFloat(2, accounts[i].getBalance( ));
    stmt.executeUpdate( );;
}
conn.commit( );

This example illustrates how close stored procedures are to prepared statements from a JDBC perspective. The difference is that you are referencing the stored procedure by name rather than spelling out the SQL you are calling. The result is simply increased performance at the expense of portability.

Some stored procedures may have output parameters. For those stored procedures, you need to register the output parameter before you execute the SQL:

CallableStatement stmt = 
    conn.prepareCall("{call sp_interest(?, ?)}");
   
stmt.registerOutParameter(2, java.sql.Types.FLOAT);
for(int i=0; i<accounts.length; i++) {
    stmt.setInt(1, accounts[i].getId( ));
    stmt.executeUpdate( );
    accounts[i].setBalancce(stmt.getFloat(2));
}
conn.commit( );

The prepareCall( ) method creates a stored procedure object that will make a call to a specific 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 procedure is set up, you can bind the ID using setInt( ) and then get the output using getFloat( ).

    [ Team LiB ] Previous Section Next Section