Chapter 4. Persistence Fundamentals

Objects contain the possibility of all situations.

Ludwig Wittgenstein, Tractatus Logico Philosphicus

Persistence grants immortality to your business applications. Without it, you lose all of your application data every time the server shuts down. Database programming is the development of persistence mechanisms to save an application’s state to a relational database. In this book, I will cover a variety of persistence mechanisms, but this chapter introduces the basics through a custom guest book JSP application.

Patterns of Persistence

The excellent book Design Patterns by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides (Addison-Wesley) has popularized the concept of design patterns. They are recurring forms in software development that you can capture at a low level and reuse across dissimilar applications. Within any application scope are problems you have encountered; patterns are the result of recognizing common problems and leveraging a common solution.

People have been writing database applications for nearly three decades. Over that time, many best practices have evolved into design patterns. As we explore different modes of persistence in this book, we will see many of these patterns over and over again.

Division of Labor

Perhaps the most essential element of good persistence design is a clear separation of application logic into the following areas:

View logic

The view logic is responsible for displaying the user interface. It is the user’s window into the control and business logic.

Control logic

The control logic handles user actions and decides what should happen based on those actions. It handles data validation and triggers the appropriate business logic on behalf of the user.

Business logic

Business logic[4] encapsulates the basic business concepts behind your application. They provide the view with getter methods to access business data and provide the interface for creating, searching, modifying, and deleting the business objects they support.

Data access logic

Data access logic maps business objects to the data storage layer. They are the heart of persistence.

Data storage logic

The database engine provides you with this type of logic, which simply ensures your data is not lost at application shutdown.

Separation of logic with dependencies based on simple interfaces is a core principle of object-oriented software engineering. When you capture the essence of a business concept in a business object without burdening it with other logic, you enable it to be reused in other environments. For example, a bank account object that does not contain display or data access logic can be reused with JSP, Swing, and other kinds of frontends. It can also persist against different database engines.


BEST PRACTICE: Divide application functionality into different logical components to facilitate component reuse.

This same principle extends beyond the business object layer. It also makes it easier to divide the work of building software among developers with different skills. With a good tag library, the view developer needs to know only XHTML and your tag library to write the view. The more difficult work of JDBC programming can be easily handed off to an experienced JDBC programmer without having to hand the entire application to a JDBC programmer.


BEST PRACTICE: Divide application logic into multiple tiers to match the complexity of your application.

Sequence Generation

In almost any database application, you need to generate unique identifiers to serve as primary keys in your database. Most databases have some sort of proprietary mechanism to help you generate sequences. Unfortunately, you cannot port a database application that relies on these proprietary schemes to other databases without changing the code that relies on those schemes.

I always recommend the use of a database-independent approach to sequence generation. Later in this chapter, I develop a sequence generator that will work for most database applications. It stores sequence seeds in the database. When an application needs a new unique number, it requests the unique number from the sequence generator. If the sequence generator has the seed in memory, it uses the following formula to create a new unique number:

unique = (seed * 1000000) + last; 

If the seed is not in memory, it is loaded from the database, incremented, and the incremented value is stored back in the database. When the seed runs out of unique values—when last reaches 1000000[5]—it loads a new seed from the database, increments that new seed, and saves the incremented seed back to the database.

This approach has several important features:

  • It generates unique values in a distributed environment. Multiple application servers can save business objects to multiple databases and still have the guarantee that the sequences being generated are unique across the entire system.

  • You do not need to go to the database every single time you generate a sequence. You go to the database only every million sequences.

  • The sequences are not tied to a specific table. You can create a sequence that is shared among several tables or even across the entire database. Similarly, you can have multiple values in the same table rely on different sequences.


BEST PRACTICE: Use database-independent sequence generation.


In the division of labor discussed earlier, the data access object needs to know about the state of the object it is persisting. You could pass the business object to the data access object, but doing so would require the data access object to know the intimate details of how the business object is implemented. The memento design pattern from the Design Patterns book comes to the rescue here.

A memento enables one object to share its state with another without either object needing to know anything about the other. Consider a common situation in which you have one class (class A) that references the values of another (class B). If you delete an attribute in class B, class A will no longer compile if it has direct references to the deleted attribute of class B. In general, this behavior is exactly what you want.

Sometimes—especially in mapping objects to a database—you want a looser coupling between two classes. The memento pattern creates this independence. It specifically enables you to make code changes to the business objects and data access objects independently of each other. A change to the business object will not require any changes to the data access object unless you are adding new data elements or removing obsolete ones. The data access object knows that the only changes it will care about come in the form of changes in the data contained in the memento. Similarly, any change to the underlying tables in the database is hidden from the business object. It always passes its state to the data access object and lets the data access object worry about persistence issues.


BEST PRACTICE: Use mementos to pass component state between application tiers.

Object Caching

A database application must use the database as a persistent store—not as a memory store. In other words, you need to pull data from the database and hold it in memory in business objects. If you go to the database every time you want to display some data about a business object, your database application will perform terribly and fail to scale at all.

On the other hand, you don’t want to load the entire database in memory and keep it there. If you have a large amount of data, you will quickly run out of memory. It is therefore important to develop an object caching mechanism that strikes a solid balance between memory usage and database access.

In architectures like the EJB architecture, the application server automatically manages caching for you. The Guest Book later in this chapter, however, does not use EJBs. It therefore needs something else to manage caching. It leverages a Cache class that uses a SoftReference to cache objects loaded from the database.


BEST PRACTICE: If you are building your own persistence system, implement an efficient caching scheme to prevent exhausting system resources.

A SoftReference is a special kind of object in java.lang.ref that creates a soft reference to the object it stores. In Java, references between objects are generally strong references. For example:

StringBuffer buffer = new StringBuffer( );

The reference to buffer is a strong reference. The strong reference is in force as long as the reference is in scope. If the references fall out of scope, then the object is said to be no longer strongly reachable. It is thus potentially available for garbage collection.

A soft reference is a reference via a SoftReference object. By storing an object indirectly through a SoftReference instead of directly, you make the object available for potential garbage collection while still maintaining the ability to access the object until it is garbage collected.

The Cache class implements the Java Collection interface. Internally, it even uses a HashMap internally to store data. When an application loads an object from the database, it can put it in the cache using the cache( ) method:

public void cache(Object key, Object val) {
    cache.put(key, new SoftReference(val));

This method creates a soft reference around the business object and then stores the soft reference in the internal HashMap. As time goes by and the business object is no longer in use, the soft reference will expire and the memory the business object occupies will be freed. The code that checks for the existence of a specific business object in the cache thus needs to verify that the soft reference has not expired:

public boolean contains(Object ob) {
    Iterator it = cache.values( ).iterator( );
    while( it.hasNext( ) ) {
        SoftReference ref = (SoftReference) );
        Object item = ref.get( );
        if( item != null && ob.equals(item) ) {
            return true;
    return false;

The get( ) method has to perform similar checks:

public Object get(Object key) {
    SoftReference ref = (SoftReference)cache.get(key);
    Object ob;
    if( ref =  = null ) {
        return null;
    ob = ref.get( );
    if( ob =  = null ) {
    return ob;

A Guest Book Application

To illustrate these most fundamental persistence concepts, I will use a simple Guest Book JSP application from my web site. You can see this example in action at The Guest Book enables visitors to a web site to leave comments and view the comments left by others. To prevent abuse, the application also includes an administrative approval mechanism. The full code for the Guest Book can be found on O’Reilly’s FTP site.

In accordance with the common persistence design patterns described earlier in the chapter, this application divides into view, control, business, data access, and data storage logic. Figure 4-1 is a UML class diagram illustrating this division.

A UML class diagram for the Guest Book application
Figure 4-1. A UML class diagram for the Guest Book application

The view and control logic exist in two separate JSP pages. These JSP pages reference a Comment object containing the business logic. They are blissfully ignorant of any persistence logic—or of the existence of persistence at all. The Comment object, however, knows only that its data is persisted, but not how that data is persisted, because it delegates its data access through a CommentDAO data access object.

I have chosen here to break down the data access even further, into individual objects supporting specific database operations. Without this trick, the data access object fills up with a jumble of SQL and JDBC that becomes difficult to manage.

The View

The view is a JSP page that displays a form and then lists all approved comments. Example 4-1 contains the code for this JSP.

Example 4-1. A JSP view that lists comments and accepts new ones
<%@ page info="Guest Book Form" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="" %>
<%@ taglib uri="/WEB-INF/tlds/dasein.tld" prefix="dasein" %>
<jsp:useBean id="user" scope="session" class=""/>
<% pageContext.setAttribute("user", user); %>
<% String d = request.getParameter("done"); %>
<% boolean done = ((d=  =null) ? false : d.trim( ).equalsIgnoreCase("true")); %>
<% String email, name, comment; %>
<% email = request.getParameter(Comment.EMAIL); %>
<% name = request.getParameter(Comment.NAME); %>
<% comment = request.getParameter(Comment.COMMENT); %>
<% if( user != null ) { %>
  <% String fn = user.getFirstName( ); %>
  <% String ln = user.getLastName( ); %>
  <% name = ((fn =  = null) ? "" : (fn + " ")) + ((ln =  = null) ? "" : ln); %>
  <% email = user.getEmail( ); %>
<% } %>
<% if( email =  = null ) { %>
  <% email = ""; %>
<% } else { %>
  <% email = email.trim( ); %>
<% } %>
<% if( name =  = null ) { %>
  <% name = ""; %>
<% } else { %>
  <% name = name.trim( ); %>
<% } %>
<% if( comment =  = null ) { %>
  <% comment = ""; %>
<% } else { %>
  <% comment = comment.trim( ); %>
<% } %>
<% String err = request.getParameter("errorID"); %>
<% if( err != null ) { %>
  <% err = err.trim( ); %>
  <% if( err.length( ) < 1 ) { %>
    <% err = null; %>
  <% } %>
<% } %>
<% if( err != null ) { %>
<% } else if( done ) { %>
  <p class="text">
    Thank you for your comment! I will review the comment. Assuming
    you did nothing offensive, it will appear below after I review it.
<% } %>
<% if( !done ) { %>
  <p class="text">
    <form method="POST" action="guestbook-action.jsp">
      <label class="text" for="<%=Comment.NAME%>">Name:</label>
      <input id="<%=Comment.NAME%>" type="text" name="<%=Comment.NAME%>"
             value="<%=name%>" size="25"/>
      <label class="text" for="<%=Comment.EMAIL%>">Email:</label>
      <input id="<%=Comment.EMAIL%>" type="text" name="<%=Comment.EMAIL%>"
             value="<%=email%>" size="25"/>
      <label class="text" for="<%=Comment.COMMENT%>">Comments:</label>
      <textarea id="<%=Comment.COMMENT%>" name="<%=Comment.COMMENT%>"
                wrap="virtual" rows="10"
      <input type="submit" value="Submit"/>
<% } %>
<h3 class="section">Comments</h3>
<dl class="guestbook">
  <% ArrayList cmts = Comment.getApproved( ); %>
  <% pageContext.setAttribute("cmts", cmts); %>  
  <dasein:foreach id="cmt" source="cmts" className="">
    <dt>On <%= cmt.getCreated( ) %>, <%=cmt.getName( )%> wrote:</dt>
    <dd><%=cmt.getComment( )%></dd>

The first part of this example pulls CGI (Common Gateway Interface) parameters into Java variables. It is specifically looking for all of the form fields as well as a done parameter and an errorID parameter. As we will see in the controller, whenever an error occurs, it sets the errorID parameter and redisplays the view. If any field values are passed in, it uses those values as default values for the form. On success, it will redisplay the list of comments—minus the form.

After the initial parameter parsing logic, it displays a form unless the done parameter was set. Finally, the page uses a tag library containing a looping construct in the form of the dasein:foreach tag. For each comment it pulls from the Comment.getApproved( ) call, it displays the data from the comment.

The Controller

The form from the view posts to the controller page. Example 4-2 shows this simple code.

Example 4-2. The Guest Book controller that handles new comments
<%@ page info="Guest Book Action" %>
<%@ taglib uri="/WEB-INF/tlds/dasein.tld" prefix="dasein" %>
<%@ taglib uri="/WEB-INF/tlds/guestbook.tld" prefix="guestbook" %>
<%@ page import="org.dasein.jsp.Log" %>
<guestbook:addComment error="error">  
  <% response.sendRedirect("guestbook-form.jsp?done=true"); %>
<dasein:isNull name="error">
  <dasein:when state="false">
    <jsp:include page="guestbook-form.jsp">
      <jsp:param name="errorID" value="<%=Log.storeException(error)%>"/>

The complexity of this action controller is hidden inside a couple of tag libraries. The first is the guestbook:addComment tag. It triggers the action of adding a new comment to the database. On success, the body of the comment is executed. In this case, the body of the comment redirects to the view page with the done parameter set.


BEST PRACTICE: Delegate controller logic in JavaServer Pages through custom tags.

The special tag dasein:isNull will execute the body of the tag if the specified value—in this case, error—is a null value. In this page, error will be null only if an error occurred while attempting to add a comment. It therefore stores the error message for later retrieval and displays the view page again so that the user may correct the error.

As you can see from this simple page, a controller does not do much in and of itself. It simply acts as a traffic cop, determining what should actually happen in response to a user action. In this case, it triggers an event in the business object through a tag library. The code in the tag library is shown in Example 4-3.

Example 4-3. A custom tag to trigger business logic
public int doStartTag( ) throws JspException {
    try {
        ServletRequest request = pageContext.getRequest( );
        String name = request.getParameter(Comment.NAME);
        String email = request.getParameter(Comment.EMAIL);
        String comment = request.getParameter(Comment.COMMENT);
        HashMap data = new HashMap( );
        Comment cmt;
        if( name != null ) {
            name = name.trim( );
            if( name.length( ) < 1 ) {
                name = null;
        if( name =  = null ) {
            if( error != null ) {
                pageContext.setAttribute(error, NO_NAME);
                error = null;
                return SKIP_BODY;
            else {
                throw new JspException(NO_NAME);
        data.put(Comment.NAME, name);
        if( email != null ) {
            email = email.trim( );
            if( email.length( ) < 1 ) {
                email = null;
        data.put(Comment.EMAIL, email);
        if( comment != null ) {
            comment = comment.trim( );
            if( comment.length( ) < 1 ) {
                comment = null;
        if( comment =  = null ) {
            if( error != null ) {
                pageContext.setAttribute(error, NO_COMMENT);
                error = null;
                return SKIP_BODY;
            else {
                throw new JspException(NO_COMMENT);
        data.put(Comment.COMMENT, comment);
        cmt = Comment.create(data);
        pageContext.setAttribute(error, null);
        return EVAL_BODY_TAG;
    catch( PersistenceException e ) {
        if( error != null ) {
            pageContext.setAttribute(error, "<p class=\"error\">" +
                                     e.getMessage( ) +"</p>");
            error = null;
            return SKIP_BODY;
        else {
            throw new JspException(e.getMessage( ));

This tag library reads all of the form parameters and validates them. If they are not valid values, it sets an error value and ignores its body. Valid values are stuck in a HashMap that acts as a memento. This memento is then passed to the Comment.create( ) method to create a new comment in the database.

The Business Object (Model)

Business objects form the heart of any major application. They model the underlying concepts of the application’s problem domain. In the case of the Guest Book, the underlying concepts are users and the comments they leave behind. For simplicity’s sake, we are not capturing users as objects in this system. In a more complex system, we probably would.

The only business object being modeled here, then, is the Comment object. The guestbook-form.jsp view is, in short, a view of Comment objects. The Comment business object encapsulates everything there is about being a comment. It stores comment data captured in the comment forms and manages the creation, deletion, approval, and retrieval of comments. These operations have two elements:

  • Metaoperations such as creation and retrieval of comments via static methods

  • Object-specific operations via instance methods

Example 4-4 contains the meta-operations.

Example 4-4. The metaoperations of the Comment business object
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import org.dasein.persist.PersistenceException;
import org.dasein.persist.Sequencer;
import org.dasein.util.Cache;
public class Comment {
    static private final Cache cache = new Cache( );
    static public final String APPROVED   = "approved";
    static public final String COMMENT    = "comment";
    static public final String COMMENT_ID = "commentID";
    static public final String CREATED    = "created";
    static public final String EMAIL      = "email";
    static public final String NAME       = "name";
     static public Comment create(HashMap data) throws PersistenceException {
        Sequencer seq = Sequencer.getInstance(Comment.COMMENT_ID);
        Comment cmt;
        Long id;
        id = new Long( ));
        data.put(Comment.COMMENT_ID, id);
        cmt = new Comment(id, data);
        synchronized( cache ) {
            cache.cache(id, cmt);
        return cmt;
    static public ArrayList getApproved( ) throws PersistenceException {
        Iterator results = CommentDAO.getApproved( ).iterator( );
        ArrayList cmts = new ArrayList( );
        while( results.hasNext( ) ) {
            Long id = (Long) );
            cmts.add(Comment.getComment(id.longValue( )));
        return cmts;
    static public Comment getComment(long cid) throws PersistenceException {
        Long id = new Long(cid);
        synchronized( cache ) {
            Comment cmt = (Comment)cache.get(id);
            if( cmt =  = null ) {
                HashMap data = CommentDAO.getComment(cid);
                data.put(Comment.COMMENT_ID, id);
                cmt = new Comment(id, data);
                cache.cache(id, cmt);
            return cmt;
    static public ArrayList getPending( ) throws PersistenceException {
        Iterator results = CommentDAO.getPending( ).iterator( );
        ArrayList cmts = new ArrayList( );
        while( results.hasNext( ) ) {
            Long id = (Long) );
            cmts.add(Comment.getComment(id.longValue( )));
        return cmts;

In addition to representing a comment, the Comment class acts as a factory that contains four meta-operations:

create( )

Creates new comment objects

getApproved( )

Retrieves all approved comments

getComment( )

Retrieves a specific comment by its comment ID

getPending( )

Retrieves all comments awaiting approval

The central data element for these operations is the comment cache stored in the static cache attribute. This cache uses the Cache class described earlier in the chapter. Whenever a comment is sought externally, this cache is checked first to see if the desired Comment instance has already been loaded. If not, the class will go to the data access object to load a new instance from the database. Otherwise, we can avoid a costly trip to the database and pull the object straight from the cache.


BEST PRACTICE: Always define literal values in constants.

You probably also notice the constants defined at the top of the class. We saw them referenced earlier in the view page. It is simply a solid coding practice never to use literals in code. Instead, you should use constants like these to help avoid application bugs caused by spelling errors.

    private Boolean approved  = null;
    private String  comment   = null;
    private Long    commentID = null;
    private Date    created   = null;
    private String  email     = null;
    private String  name      = null;
    private Comment(Long cid, HashMap data) {
        super( );
        commentID = cid;
    public String getComment( ) {
        return comment;
    public long getCommentID( ) {
        return commentID.longValue( );
    public Date getCreated( ) {
        return created;
    public String getEmail( ) {
        return email;
    public String getName( ) {
        return name;
    public boolean isApproved( ) {
        return approved.booleanValue( );
    private void load(HashMap data) {
        approved = (Boolean)data.get(Comment.APPROVED);
        comment = (String)data.get(Comment.COMMENT);
        commentID = (Long)data.get(Comment.COMMENT_ID);
        created = (Date)data.get(Comment.CREATED);
        email = (String)data.get(Comment.EMAIL);
        name = (String)data.get(Comment.NAME);
    public void remove( ) throws PersistenceException {
        HashMap data = new HashMap( );
        data.put(Comment.COMMENT_ID, commentID);
        synchronized( cache ) {
    public void save(HashMap data) throws PersistenceException {
        data.put(Comment.COMMENT_ID, commentID);;

The instance operations are largely simple getter methods. The exceptions are the following:

load( )

The load method pulls data from our HashMap memento and assigns that data to instance variables.

remove( )

The remove( ) method deletes the object and removes it from the cache.

save( )

The save( ) method tells the data access object to save changes to the comment.

The most critical thing to notice about the business object is that it hides all knowledge about persistence from the view and the controller. The view and controller simply do not need to know if the object persists or how it persists. In fact, the business object knows only that it persists—it knows nothing about how it persists. That knowledge is saved for the data access objects.

The Data Access Objects

The data access object, CommentDAO, provides a simple interface to the business object for persisting comments to the database. In short, it has methods to load, delete, update, and create comments. When the methods require data from the comment, the data is passed via a memento. They throw generic persistence exceptions. The data access object thus needs to know nothing about the internal structure of comments, and comments need to know nothing about the persistence details of the data access object. Example 4-5 contains the code for the CommentDAO class.

Example 4-5. The CommentDAO data access object
import java.util.ArrayList;
import java.util.HashMap;
import org.dasein.persist.Execution;
import org.dasein.persist.PersistenceException;
public abstract class CommentDAO {
    static public void create(HashMap data) throws PersistenceException {
        CreateComment.getInstance( ).execute(data);
    static public ArrayList getApproved( ) throws PersistenceException {
        HashMap data = new HashMap( );
        data.put(Comment.APPROVED, new Boolean(true));
        data = ListComments.getInstance( ).execute(data);
        return (ArrayList)data.get(ListComments.COMMENTS);
    static public HashMap getComment(long cid) throws PersistenceException {
        HashMap data = new HashMap( );
        data.put(Comment.COMMENT_ID, new Long(cid));
        data = LoadComment.getInstance( ).execute(data);
        return data;
    static public ArrayList getPending( ) throws PersistenceException {
        HashMap data = new HashMap( );
        data.put(Comment.APPROVED, new Boolean(false));
        data = ListComments.getInstance( ).execute(data);
        return (ArrayList)data.get(ListComments.COMMENTS);
    static public void save(HashMap data) throws PersistenceException {
        SaveComment.getInstance( ).execute(data);
    static public void remove(HashMap data) throws PersistenceException {
        RemoveComment.getInstance( ).execute(data);

This data access object further delegates to operation-specific objects to avoid clutter in this class.

Loading comments

These delegates use the framework I described earlier in the chapter. Example 4-6 shows the LoadComment delegate that performs the SQL to load a comment from the database.

Example 4-6. Loading a comment through a special delegate
import java.sql.SQLException;
import java.util.HashMap;
import org.dasein.persist.Execution;
import org.dasein.persist.PersistenceException;
public class LoadComment extends Execution {
    static public LoadComment getInstance( ) {
        return (LoadComment)Execution.getInstance(LoadComment.class);
    static private final String LOAD =
        "SELECT approved, email, name, comment, created " +
        "FROM Comment " +
        "WHERE Comment.commentID = ?";
    static private final int COMMENT_ID = 1;
    static private final int APPROVED   = 1;
    static private final int EMAIL      = 2;
    static private final int NAME       = 3;
    static private final int COMMENT    = 4;
    static private final int CREATED    = 5;
    public HashMap run( ) throws PersistenceException, SQLException {
        long id = ((Long)data.get(Comment.COMMENT_ID)).longValue( );
        HashMap res = new HashMap( );
        String tmp;
        statement.setLong(COMMENT_ID, id);
        results = statement.executeQuery( );
        if( ! ) ) {
            throw new PersistenceException("No such comment: " + id);
        tmp = results.getString(APPROVED);
                new Boolean(tmp.trim( ).equalsIgnoreCase("Y")));
        tmp = results.getString(EMAIL);
        if( results.wasNull( ) ) {
            res.put(Comment.EMAIL, null);
        else {
            res.put(Comment.EMAIL, tmp.trim( ));
        res.put(Comment.NAME, results.getString(NAME));
        res.put(Comment.COMMENT, results.getString(COMMENT));
        res.put(Comment.CREATED, results.getDate(CREATED));
        return res;
    public String getDataSource( ) {
        return "jdbc/george";
    public String getStatement( ) {
        return LOAD;

You should notice here again the liberal use of constants instead of literals throughout the code. This practice is very important in JDBC programming since the most efficient way to access columns in a result set is by column number.


BEST PRACTICE: Access JDBC columns by number and use constants to keep those column values readable and maintainable.

The code executes a SQL SELECT and places the result into a memento. That memento goes back to the calling business object, which then sends it through the business object’s load( ) method. If a JDBC error or some other exception occurs, the exception will be wrapped up in a PersistenceException and sent back to the calling business object.

Sequence generation

Throughout this book, I reference the best practice of relying on your own, database-independent primary key generation mechanism. No discussion of the data access tier would be complete without a discussion of primary key generation.

Every database engine provides a feature that enables applications to automatically generate values for identity columns. MySQL, for example, has the concept of AUTO_INCREMENT columns:

    lastName   VARCHAR(30)     NOT NULL,
    firstName  VARCHAR(25)     NOT NULL

When you insert a new person into this table, you omit the primary key columns:

INSERT INTO Person ( lastName, firstName)
VALUES ( 'Wittgenstein', 'Ludwig' );

MySQL will automatically generate the value for the personID column based on the highest current value. If one row exists in the database with a personID of 1, Ludwig Wittgenstein’s personID will be 2. Some other databases have similar ways to generate primary keys; others provide wildly different tools.

Reliance on your database engine’s primary key generation tools has the following drawbacks:

  • Every database engine handles key generation differently. It is thus difficult to build a truly portable JDBC application that uses proprietary key generation schemes.

  • Until JDBC 3.0, a Java application had no clear way of finding out what keys were generated on an insert.

  • In many databases, you can autogenerate only a single unique value per table.

  • In many databases, you cannot use the primary key generation mechanism to generate values unique across multiple tables.

I recommend the development of a database-independent primary key generation API that stores potential primary key values in the database. If you take this approach, you need to take care not to make too many trips to the database. You can avoid this pitfall by generating keys in memory and storing seed values in the database.

The heart of this database-independent scheme is the following table:

CREATE TABLE Sequencer (
    name        VARCHAR(20)      NOT NULL,
    seed        BIGINT UNSIGNED  NOT NULL,
    PRIMARY KEY ( name, lastUpdate )

The first time your application generates a key, it grabs the next seed from this table, increments the seed, and then uses that seed to generate keys until the seed is exhausted. Example 4-7 through Example 4-9 contain some of the code for a database-independent utility that handles unique number generation. It enables your application to simply use the following calls to create primary keys:

Sequencer seq = Sequencer.getInstance("personID");
personID = );

The tool guarantees that you will receive a value that is unique across all personID values. Example 4-7 contains the static elements that implement the singleton design pattern to hand out shared sequencers.

Example 4-7. The code to serve up sequencers
public class Sequencer {
    static private final long    MAX_KEYS   = 1000000L;
    static private final HashMap sequencers = new HashMap( );
    static public final Sequencer getInstance(String name) {
        synchronized( sequencers ) {
            if( !sequencers.containsKey(name) ) {
                Sequencer seq = new Sequencer(name);
                sequencers.put(name, seq);
                return seq;
            else {
                return (Sequencer)sequencers.get(name);

The code provides two critical guarantees for sequence generation:

  • All code that needs to create new numbers for the same sequence (like personID) will share the same sequencer object.

  • Because of the synchronized block, two attempts to get a previously unreferenced sequence at the same instant will not cause two different sequencers to be generated.

The attribute declarations and initialization for a sequencer define two attributes that correspond to values in the Sequencer table as well as a third attribute, sequence, to track the values handed out for the current seed, as shown in Example 4-8.

Example 4-8. Setting up the sequencer
private String name     = null;
private long   seed     = -1L;
private long   sequence = 0L;
private Sequencer(String nom) {
    super( );
    name = nom;

The core element of the sequencer—its public API—is the next( ) method. It contains the algorithm for generating unique numbers. The algorithm has the following process:

  • Check to see if the seed is valid. The seed is invalid if this is a newly generated sequencer or if the seed is exhausted. A seed is exhausted if the next sequence has a value greater than MAX_KEYS.

  • If the seed is not valid, get a new seed from the database.

  • Increment the sequence.

  • Multiply the seed by MAX_KEYS and add that value to the incremented sequence. This is the unique key.

Example 4-9 contains the algorithm.

Example 4-9. Generating a sequence ID
public synchronized long next( ) throws PersistenceException {
    Connection conn = null;

    // when seed is -1 or the keys for this seed are exhausted,
    // get a new seed from the database
    if( (seed =  = -1L) || ((sequence + 1) >= MAX_KEYS) ) {
        try {
            String dsn = System.getProperty(DSN_PROP, DEFAULT_DSN);
            InitialContext ctx = new InitialContext( );
            DataSource ds = (DataSource)ctx.lookup(dsn);

            conn = ds.getConnection( );
        catch( SQLException e ) {
            throw new PersistenceException(e);
        catch( NamingException e ) {
            throw new PersistenceException(e);
        finally {
            if( conn != null ) {
                try { conn.close( ); }
                catch( SQLException e ) { }
    // up the sequence value for the next key
    // the next key for this sequencer
   return ((seed * MAX_KEYS) + sequence);

The rest of the code is the database access that creates, retrieves, and updates seeds in the database. The next( ) method triggers a database call via the reseed( ) method when the seed ceases to be valid.

The logic for reseeding the sequencer is fairly straightforward:

  • Fetch the current values for the sequence in question from the database.

  • If the sequence does not yet exist in the database, create it.

  • Increment the seed from the database.

  • Update the database

  • Set the new seed and reset the sequence attribute to -1 (this makes the first number generated 0).

You can find the full code for the Sequencer class on O’Reilly’s FTP site in the directory for this book.

[4] You do not need to be writing a business application to have business logic. Business logic is a generic term that refers to any of the basic concepts in your problem domain. If you are building a first-person shooter game, your “business objects” are monsters, weapons, hazards, and the like.

[5] The value 1,000,000 depends on the system. You will want lower numbers for systems with short uptimes and larger numbers for systems with long uptimes.

Get Java Database Best Practices now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.