Java Enterprise Best Practices

Do Not Rely on Built-in 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:

CREATE TABLE Person (
    personID   BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    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. For example, if one row exists in the database with a personID of 1, Ludwig Wittgenstein’s personID will be 2.

However, using the supported key generation tools of your database of choice presents several problems:

• Every database engine handles key generation differently. Thus, it is 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 which keys were generated on an insert.

• Automated key generation wreaks havoc with EJBs.

You can avoid the difficulties of proprietary key generation schemes by writing your own. Your first inclination might be to create a table in the database to hold the generated keys, and this inclination is correct. It comes with some caveats, however.

Relying solely on a database table to hold the keys requires two trips to the database ...