4.1. Session Beans and JDBC

Suppose a record store wants to provide users with a way to display recordings from their Music Collection database. If the database is accessible from an EJB, recordings could be available on the web and from computers in the store. The Music EJB will be similar to our Loan EJB from the previous chapter. Using the J2EE architecture, Figure 4-1 shows the design approach.

On the database server machine, the Music EJB accesses the Music Collection database in read-only mode. Music EJB is a stateless session bean because it does not perform any tasks whose results needs to be saved to persistent storage. This implies multiple clients may share the same instance of the Music EJB with no contention problems.

The Music Collection database has a specific format, which we'll describe shortly. Clients use the Music EJB to access recordings from the database and fetch tracks from a specific recording. To provide this information to the client, the bean implementation class must provide JDBC statements to access the music database and build objects that manage data requested by the client.

Two clients will use the Music EJB: a JSP web component and a stand-alone application with a Java Swing graphical user interface. The application client runs on a client machine, the web and EJB components run on a J2EE server machine, and the database runs on a database server machine.

Because the Music EJB is a stateless session bean, much of its structure should be familiar to you. However, by adding JDBC capability, we create a distributed, multitiered enterprise application that is the foundation for developing larger and more powerful enterprise applications.

Frequently, enterprise developers must create applications that use a pre-existing database. A new application might only access a portion of the stored data, or it may create composite objects from data stored in different parts of the database.

Database Fundamentals

Let's begin with an overview of databases and JDBC. We'll discuss database tables and how to use JDBC to access the data. If you're already familiar with these subjects, you can skip to the next section.

A relational database consists of one or more tables, where each row in a table represents a database record and each column represents a field. Within each table, a record must have a unique key. This key, called a primary key, enables a database to distinguish one record from another. Database software prevents you from creating records with duplicate primary keys. A field within a table is either a primary key, a foreign key (used by the database to reference another table), or just plain data.

A very simple database will consist of a single table. However, many database schemata require multiple tables to efficiently represent related data. For example, in our Music Collection database, we centralize the information about each recording artist in one table, then cross reference a RecordingArtist- ID field in a different table that stores data about the recording itself. Thus, if a recording artist has more than one recording, you don't have to duplicate the recording artist information.

To achieve cross referencing and not duplicate data, a field in a database table may be marked as a foreign key. A foreign key in one table will match either a primary or foreign key in another table. By matching foreign keys to keys in other tables, we “relate” two or more tables.

Music Collection Database

The Music Collection database consists of four related tables. The database stores information about music recordings, a generic term we apply to music CDs and older LPs (long-playing records). Figure 4-2 shows the four tables, the fields in each table, and how they relate to each other through the foreign keys.

The Recordings table contains the bulk of the information about a recording. Its primary key (denoted PK) is the field RecordingID. It has two foreign key fields (denoted FK): RecordingArtistID and MusicCategoryID. These foreign key fields refer to records in the Recording Artists table and the Music Categories table, respectively. For each row in the Music Categories table, there may be multiple rows in the Recordings table. (We indicate this relationship by placing the word Many next to the Recordings table and a 1 next to the Music Categories table.) Similarly, for each row in the Recording Artists table, there may be multiple rows in the Recordings table. In the diagram, we show foreign key field names on the arrow lines that relate two tables.

The Tracks table contains the information about each track belonging to a recording. To determine which recording a track belongs to, we include the RecordingID as a foreign key in the Tracks table. Thus, for each row in the Recordings table, there are multiple rows in the Tracks table.

Introducing JDBC

To communicate with a database using the JDBC API, you must obtain a database connection. In the J2EE environment, access to a database resource is done the same way as an Enterprise Java Bean reference. First, you obtain the context and then use the JNDI API to perform a lookup. A successful lookup returns a DataSource object, which you use to obtain a connection. Here's an example.

// get context and perform a lookup of the coded name
InitialContext ic = new InitialContext();
DataSource ds =
    (DataSource) ic.lookup("java:comp/env/jdbc/MusicDB");
// Obtain a Connection from the DataSource
Connection con = ds.getConnection();

To communicate with various database vendors, JDBC provides a Java Driver Manager mechanism to handle the different JDBC drivers. Theoretically, the JDBC API provides access to relational databases from any vendor. The Sun Microsystems reference implementation includes the Cloudscape relational database server with the JDBC driver automatically configured.

(The key word here is theoretically. We have found minor differences in SQL punctuation, use of quotation marks, and other special characters among different database vendors. We'll discuss how to cope with these database portability issues later on with the J2EE Data Access Object pattern. See “Data Access Object Pattern” on page 119 of this chapter.)

Design Guideline

When the JDBC software detects a problem, it throws an SQLException. Therefore, you must either place SQL code inside a try block or within a method that includes SQLException in its throws clause.

Statement Object

Once you have a connection, the next step is to create an SQL Statement object and execute it. The SQL Select command lets you search a database for certain records that satisfy some criteria. Calling the Statement object's executeQuery() method with a Select command as a String argument makes the database server return the data in a JDBC ResultSet object. Here's an example.

// con is a Connection object
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
  "Select RecordingTitle,RecordingLabel From Recordings");

SQL also has Insert, Update, and Delete commands that modify a database. With these commands, you would use the executeUpdate() method, which does not return data.

ResultSet Object

JDBC's ResultSet object provides access to the data generated by the SQL Select command. Unfortunately, the ResultSet is a somewhat clumsy object to work with. Basically, it's a table that consists of columns and rows. A cursor points to the current row, where you can extract data from the columns as fields. (You'd better know what data type is in these fields or you'll get gibberish!) Initially, the cursor is positioned before the first row, so you'll have to increment it before grabbing any data. The JDBC 2.0 specification includes ResultSet methods to manipulate the cursor position, but for maximum portability, you should not read the data more than once. (If this is necessary, clone() the ResultSet before reading it.) Accessing the data in a ResultSet object is normally done in a loop, until the ResultSet indicates that there is no more data to read.

A ResultSet object provides a set of getter methods to extract data. The method you use depends on a field's data type. For example, getString(arg) is used for strings and getInt(arg) for longs or shorts. The argument to these methods is an integer representing the ResultSet's column number (starting at column 1) or a field name in the database table. Here's an example.

// ResultSet rs is set from previous executeQuery() call
while (rs.next()) {
  String title = rs.getString(1);         // get column 1
  String label = rs.getString(2);         // get column 2
  // do something with title and label
}

The ResultSet object provides a next() method to access the data row by row. For each row, we get the first and second fields and store them into strings. The database will return the fields in the order they appear in the query.

Getter methods with field name arguments are not as efficient as integer arguments but may be more maintainable and readable. Looking ahead, the strategy we use is to step through a ResultSet in a loop, instantiate appropriate value objects with data from the ResultSet, and place these objects in a collection (such as ArrayList).

Design Guideline

Recall that since the EJB container oversees thread issues, ArrayList is a more efficient data collection object than Vector, for example. Although both collections grow automatically and are serializable, the fetch and store methods are not synchronized with ArrayList (as they are with Vector). So with ArrayList, you do not pay twice for synchronization. And, you can easily manipulate elements with Iterator objects.

PreparedStatement Object

The PreparedStatement object extends Statement and allows precompiled SQL statements, which are more efficient. When you instantiate a PreparedStatement with the prepareStatement() method of the Connection object, the SQL string is sent to the database and compiled. Any statement that is valid with executeQuery() may be precompiled in a PreparedStatement object.

PreparedStatement objects also provide positional input parameters. Positional parameters (there can be more than one) appear in an SQL string with ? (question mark). Typically, you assign values to positional parameters with PreparedStatement setter methods and then invoke executeQuery() to run the SQL command. Here's an example.

// create a PreparedStatement from Connection con
PreparedStatement trackStmt = con.prepareStatement(
    "Select * From Tracks Where RecordingID = ?");
trackStmt.setInt(1, 6);        // set parameter 1 to value 6
// execute statement, return results in rs
ResultSet rs = trackStmt.executeQuery();

The Select statement accesses all columns (fields) from the Tracks table whose RecordingID is set at run time via a positional parameter. The setInt() method assigns value 6 (a RecordingID) to the first positional parameter. A call to executeQuery() with the PreparedStatement object fetches the data from the database.

JDBC Code Sample

Now let's look at several snippets of code in a J2EE environment that access an SQL database using the JDBC API. First, we look up the datasource from the initial context and obtain a Connection. Getting a connection from a datasource is efficient, since the EJB container maintains a pool of database connections. All JDBC related statements should be placed in try blocks to handle exceptions.

Design Guideline

After connecting, make sure you release the connection as soon as you're done with the database operation. This technique makes the connection resource available to other clients, since the EJB container maintains a pool of database connections.

// dbName contains the coded name of the database source
String dbName = "java:comp/env/jdbc/MusicDB";
Connection con = null;
PreparedStatement trackStmt = null;
ResultSet rs = null;

try {
  // get context and perform a lookup of the coded name
  InitialContext ic = new InitialContext();
  DataSource ds = (DataSource) ic.lookup(dbName);
  // Obtain a Connection from the DataSource
  con = ds.getConnection();

Next, we'll create a Select statement to access the Tracks table and initialize a PreparedStatement with the connection object. This Select statement has a positional parameter which finds a record with a specific RecordingID and orders the track numbers from lowest to highest.

// SQL query that selects all rows from the Tracks table
// where the RecordingID is equal to the input parameter
// Sort the records by the TrackNumber field

String selectQuery = "Select * From Tracks " +
       "Where RecordingID = ? Order By TrackNumber";
// Create a PreparedStatement with the above SQL query
trackStmt = con.prepareStatement(selectQuery);

// Set the first input parameter to 5
// This makes the SQL query read:
// "Select all records from the Tracks table
// where the RecordingID field is 5.
// Order the records by field TrackNumber."
trackStmt.setInt(1, 5);

A call to executeQuery() executes our SQL Select statement and returns the data in a ResultSet. Now we can build an ArrayList collection of Widget value objects with the data in the ResultSet.

// execute the query
// store results into object ResultSet rs
rs = trackStmt.executeQuery();
// Put Widgets into ArrayList collection
ArrayList tList = new ArrayList();

while (rs.next())
{
  // create Widget object and add to tList ArrayList
    tList.add(new Widget(
    rs.getInt(2),           // Track Number
    rs.getString(3),        // Track Title
    rs.getString(4)));      // Track Length
}

How do you determine what numbers and which ResultSet getter methods to use? Recall that the database returns fields in the order they appear in the query. When you use * in a Select statement (indicating all fields in the table), the database builds a ResultSet with fields in the same order as the database table. The Tracks table contains five fields listed in Figure 4-2 on page 88. Field 2 is the Track Number (integer), field 3 is the Track Title (string) and field 4 is the Track Length (string).

When we exit the loop, we close the ResultSet, PreparedStatement, and Connection objects in a finally block.

} catch (Exception ex) {
    throw new EJBException("Unexpected error: " +
      ex.getMessage());

} finally {
  if (rs != null) rs.close();  // close ResultSet
  if (trackStmt) != null)
      trackStmt.close();       // close Prepared Statement
  if (con != null)
      con.close();             // close connection
}

The catch handler throws an EJBException for any exceptions generated by methods called in the try block. This includes SQLException objects, which can be thrown from JDBC calls.

Design Guideline

It's a good idea to close database connections in a finally block. This technique ensures that we always close the connection, even if a thrown exception makes us exit the try block early. Closing a connection returns it to the container's pool of database connections.