Database/Code impedance mismatch

I love natural keys in database design. You have to pay attention, though: the natural impedance mismatch between a programming language representation and the database representation of the key can bite you.

Consider an object whose primary key might contain a date--say, a change log record. Oracle and DB2 both store a DATE as a time containing year, month, day, hours, minutes, and seconds. No timezone. The natural mapping for a Java tool like Hibernate is to map to a java.util.Date, which stores the Date as a time in milliseconds since the epoch GMT, and then maps it to whatever timezone is set on the machine where the code is running for display and conversion.

Now consider what might happen (especially if our change log record is attached to some parent object);

1. We create and save the object; it is persisted. The local cached copy contains a non-zero value for milliseconds, but the database has truncated the milliseconds value and saved it.
2. Later on in the code somewhere, we have reason to save the object again, perhaps as part of some collection operation.
3. Hibernate looks in its cache, compares it with the database, and notes that the values of the Date don't match--so it tries to save the value again.
4. The database dutifully tosses out the spare milliseconds, and bam! we have an attempt to re-insert an existing record, so it throws an exception.

This is all terribly confusing to the programmer, who, inspecting the objects in question, sees no difference between what's in the database and what's in her code, especially since the default display characteristics of her database browser and her debugger don't show the milliseconds.

The easy fix in this case is to declare a class which matches the database representation--in this case, a good choice would be to declare a new class which truncates the milliseconds. A modest example is shown below:

/**
* Public Domain; use or extend at will.
*/
import java.util.Date;

public class DbDate extends Date {
/** increment if you change the state model     */
private static final long serialVersionUID = 1L;

/** @see java.util.Date#Date() */
public DbDate() {
    long t = getTime();
    setTime(t - t%1000);
}

/** @see java.util.Date#Date(long) */
public DbDate(long t) {
    super(t - t%1000);
}

/** @see java.util.Date#setTime(long)     */
@Override
public void setTime(long time) {
    super.setTime(time - time%1000);
}
}

Also note that if you declared the database column as a TIMESTAMP, the Java and database representations more-or-less match--avoiding, in this case, this kind of problem. Note that Oracle doesn't support TIMESTAMP_WITH_TIMEZONE in a primary key, and DB2 doesn't implement TIMESTAMP_WITH_TIMEZONE at all--as of the last time I had access to DB2.

Dealing with timezones is another topic entirely--one which I'll take up in a future post.