I am building a new web app and I am using Spring, JPA/Hibernate, and Postgres. Some of my tables have creation_ts and lastupdate_ts columns which are timestamp columns that track when an insert occurred and when the last update occurred on a row.
I am also using a naming convention for columns in my tables so as a matter of design policy every table is guaranteed to have two columns pkey which is an integer surrogate key, and version for optimistic locking.
I have two ways to keep these fields up to date.
Option A: use triggers
This is the solution I have in place right now, I have two Postgres trigger that fire on insert and update and will keep these fields up to date. and I have two classes.
@MappedSuperclass
public abstract class PersistableObject
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="pkey")
private Integer pkey;
@Version
@Column(name="version")
private Integer version;
public Integer getPkey()
{
return this.pkey;
}
public Integer getVersion()
{
return this.version;
}
}
and I have
@MappedSuperclass
public class TimeStampedPersistableObject extends PersistableObject {
@Column(name = "creation_ts")
@Temporal(TemporalType.DATE)
@org.hibernate.annotations.Generated(value = GenerationTime.INSERT)
private Date creationTimestamp;
@Column(name = "update_ts")
@Temporal(TemporalType.DATE)
@org.hibernate.annotations.Generated(value = GenerationTime.ALWAYS)
private Date updateTimestamp;
public Date getCreationTimestamp()
{
return this.creationTimestamp;
}
public Date getUpdateTimestamp()
{
return this.updateTimestamp;
}
}
Option B: Use JPA listeners
In this option I would use JPA listeners to keep to timestamp columns up-to-date.
My Question:
Which of those two approaches is better? As I see things here is my personal list of pros and cons of each option and I very interested from hearing the experience of others with these two choices.
Option A pros:
Option A cons:
Option B pros:
Option B cons:
How you would solve this problem for a new app where you have total control over the database and the java code.