8.2 Primary Key Patterns

Primary keys are the unique identifiers that allow one row in a database to be referenced by other rows in other tables. In the example we've been using throughout the chapter, there are primary keys on the PATIENT, STAFF, and VISIT tables identifying unique patients, staff members, and office visits. The primary keys allow us to reference a specific entity. Staff members and patients can be associated with multiple visits, and multiple visit notes can be associated with a single visit by referencing the appropriate primary key.

Primary keys are at the heart of relational database design: any J2EE application that directly inserts new records into a relational data structure has to be able to generate primary keys. Schemes for primary key generation vary widely. Many databases have field types that increment with each row, making it easy to generate new keys as needed. Since this kind of field isn't standard SQL-92, other database types require alternate approaches, and auto-increment fields aren't always the best approach anyway, particularly when you need to know the new key before writing anything to the database.

The patterns in this section describe approaches for creating primary keys within J2EE applications.

8.2.1 PK Block Generator Pattern

Most Java-based primary key generation mechanisms rely on an object that hands out IDs upon request. These objects are generally shared by an entire application to prevent duplicate assignments. Objects requiring a new primary key request one from this core object as needed. The challenge is to assure that no key is ever given out twice.

The PK Block Generator pattern works by generating a block of unique numerical IDs based on a value retrieved from a database sequence. Sequences, which produce a unique number on request, are available in one form or another in most database packages (those that don't generally include the concept of an auto-incrementing field—in these cases, the implementation can either be adapted or the Stored Procedures for Primary Keys pattern can be used instead). The PK Block Generator approach ensures uniqueness and scalability, although it does not (like most approaches) guarantee that the generated keys will be contiguous or even in perfect order.^[3]

^[3] In most applications, this works just fine. Primary keys should serve as unique identifiers and nothing else; assigning them additional significance based on order or other attributes simply overloads the field and makes the application more difficult to maintain. The same applies to primary keys with real-world significance: using name, city, and state as a unique identifier may work for a while, but eventually there will be two John Smiths in New York City.

The simplest sequence-based primary key scheme is to retrieve a new value from the sequence for every request. This process can get expensive, particularly when large numbers of keys are required. The PK Block Generator pattern modifies that approach by retrieving a base number from the sequence and multiplying it by a block size. The singleton object can then hand out keys from that block until the block runs out, at which point it retrieves a new base number and generates a new block. Since each base number produces a unique block, and the database will give out a single value from the sequence at most once, this approach works even in a networked environment where multiple JVMs share access to the same database.

Proper sizing of the block depends on the frequency with which new primary keys must be generated. The smaller the block, the more frequently a new one must be retrieved from the database, but the less likely you are to "waste" keys, since the remains of a block are discarded on VM restart.

If the PK Block Generator, as implemented in Example 8-4, is called from within an EJB, it's possible that it will get caught up in the bean's transaction. If that transaction is rolled back, the generator could theoretically end up handing out duplicate sets of IDs. The implementation in Example 8-4 deals with this problem by taking advantage of a quirk of Oracle's sequence implementation: sequence value requests aren't included in transactions. So even if the transaction that retrieves a sequence value is rolled back, the blocks won't be duplicated.

A better way of dealing with this in a pure EJB environment is to front the block generator with a stateless session bean, configured with the TX_REQUIRES_NEW attribute. Other beans can call the session bean when they need a new primary key, with the assurance that any SQL calls will be included in a new transaction.

Example 8-4. SequenceBlock.java

import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class SequenceBlock  {

  private static int BLOCK_SIZE = 10;
  private static long current = -1;
  private static long getNextAt = -1;
  
  public static synchronized long getNextId(  ) {
    if((current > -1) && (current < getNextAt))
      return current++;

    // We need to retrieve another block from the database
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      con = getConnection(  );
      stmt = con.createStatement(  );
      // Oracle specific
      rs = stmt.executeQuery("SELECT SEQ_PK.NEXTVAL FROM DUAL"); 
      rs.next(  ); // Exception handler will kick in on failure
      long seqVal = rs.getLong(1);
      current = seqVal * BLOCK_SIZE;
      getNextAt = current + BLOCK_SIZE;
      return current++;
    } catch (SQLException e) {
      throw new IllegalStateException("Unable to access key store");
    } finally {
      if(rs != null) try { rs.close(  ); } catch (SQLException e) {}
      if(stmt != null) try { stmt.close(  ); } catch (SQLException e) {}
      if(con != null) try { con.close(  ); } catch (SQLException e) {}
    }
  }

  private static Connection getConnection(  ) throws SQLException {
    try {
      Context jndiContext = new InitialContext(  );
      DataSource ds =
 (DataSource)jndiContext.lookup("java:comp/env/jdbc/DataChapterDS");
      return ds.getConnection(  );
    } catch (NamingException ne) {
        throw new SQLException (ne.getMessage(  ));
    }
  }
}

This code generates blocks of primary keys by retrieving unique numbers from a database sequence. We then multiply this value by BLOCK_SIZE to get the initial key value for the block. We then give out keys from seqVal * BLOCK_SIZE through (seqVal * BLOCK_SIZE) + BLOCK_SIZE - 1. Once we've given out the full range of available keys, we get another sequence value and start again. If the system restarts, the code will retrieve a new sequence value and start over again: producing a gap in the order of the keys but never assigning the same key twice. Using the database sequence guarantees that every key will be unique.

It's also worth noting that while we've implemented this object as static, it probably isn't going to be static in real life. At the bare minimum, you'll need one instance of the SequenceBlock object per JVM, and if you have multiple class loaders (for different web applications, different EJB packages, and so on) you'll have one instance per loader. This is nice behavior, since it allows you to use the same object in different web applications on the same server, pointing to different databases and generating different sets of keys. But take heart—even multiple instances of the object pointing to the same database will produce unique primary keys by virtue of the database's role as an intermediary.

8.2.2 Stored Procedures for Primary Keys Pattern

Another approach to primary key generation is to use stored procedures to insert new records into the database via the Stored Procedures for Primary Keys pattern.^[4] These procedures can take advantage of a variety of mechanisms to insert new records into the database with a unique primary key. Rather than running a SQL insert statement, your Java code calls a stored procedure within the database that is responsible for generating the new primary key and inserting the new record in the appropriate tables.

^[4] We are considering running a contest for a better name.

Broadly, implementation strategies for this pattern include:

• Database sequences

• Database auto-increment fields

• Computing a new key based on previous values

The first two approaches are the most common. The last method involves paying careful attention to transaction issues and providing redundant checks to ensure that no two entries have the same primary key; it should be avoided whenever possible (we mention it here because sometimes, when sequences and auto-increment fields are unavailable, it's the only possible approach). No matter which strategy you use, the key assignment algorithm is implemented in a stored procedure.

Here's a simple example in Oracle PL/SQL. The procedure retrieves a new primary key from a sequence, uses it to create a new row, and returns the new patient identifier (the pat_no field) as an out parameter.

PROCEDURE INSERT_NEW_PATIENT 
(
  fname_in  in  patient.fname%type,
  lname_in  in  patient.lname%type,
  pat_no    out patient.pat_no%type
) AS
new_pat_no  patient.pat_no%type;
BEGIN
  select seq_pk.nextval into new_pat_no from dual;

  insert into patient (pat_no, fname, lname) 
    values (new_pat_no, fname_in, lname_in);
    
  pat_no := new_pat_no;
END;

We can run this procedure in Oracle's SQL*PLUS environment in order to insert a new row and then print the new key:

SQL> var new_pn number;
SQL> exec insert_new_patient('Wilbur', 'Porcino', :new_pn);

PL/SQL procedure successfully completed.

SQL> print new_pn;

        NP
----------
        10

It's obviously more useful to be able to do this in Java. We can use this procedure to add an ejbCreate( ) method to the Patient bean, allowing clients to create a new patient record by providing the first and last name of the patient. This is a logical place for a PAO or a stored-procedure-aware DAO, but in order to preserve our code, we'll keep it in the ejbCreate( ) method:

public Long ejbCreate(String firstname, String lastname) 
  throws CreateException {
  Connection con = null;
  CallableStatement cstmt = null;

  fname = firstname; //Set EJB fields
  lname = lastname;
    
   try {
    con = getConnection(  );
    cstmt = con.prepareCall("{ call insert_new_patient(?,?,?) }");
    cstmt.setString(1, fname);
    cstmt.setString(2, lname);
    cstmt.registerOutParameter(3, Types.INTEGER); 

    cstmt.execute(  );
    pat_no = new Long(cstmt.getLong(3));
      
  } catch (SQLException e) {
    throw new CreateException(e.getMessage(  ));
  } finally {
    if (cstmt != null) try { cstmt.close(  ); } catch (SQLException se) {}
    if (con != null) try { con.close(  ); } catch (SQLException se) {}
  }

  return pat_no;
}

The only complexity here is that we use a CallableStatement rather than a PreparedStatement to execute the stored procedure. Once the procedure is executed, we can retrieve the new patient number from the output parameter.