Hibernate: Creating and Populating “created_time” and “last_mod_time” Columns on SQL Tables

When writing REST services or web applications, having “created_time” or “last_mod_time” columns on a per table basis is a super common requirement. The preferable way to accomplish this is to generally use a “SQL trigger”.

One of the problems with the trigger based approach is that some ORMs don’t play nice with triggers. Additionally, they often require you to refetch the entity. There are many ways to handle this problem:

Define the “created_time” or “last_mod_time” using only the ORM. Do not use triggers.
Use a hybrid approach, have ORM generated times and “SQL trigger” generated times, resulting in “4” columns instead of “2” columns.
Have the ORM populate values and then design the trigger to not override non-null values.
Accept the extra cost of refetching the entity.

This article will focus on using hibernate to generate the “created_time” and “last_mod_time” fields. Creating or integrating a “SQL trigger” for this code will be an exercise left to the reader.

First, we define our system times collection:

import com.google.common.base.MoreObjects;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.time.Instant;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@MappedSuperclass
public class SystemTypeCollection {
    @Basic
    @Column(name = "system_insert_time", nullable = false)
    private Instant systemInsertTime;
    @Basic
    @Column(name = "system_update_time", nullable = false)
    private Instant systemUpdateTime;
    @Basic
    @Column(name = "system_insert_type", nullable = false, length = -1)
    private String systemInsertType;
    @Basic
    @Column(name = "system_update_type", nullable = false, length = -1)
    private String systemUpdateType;

    @PrePersist
    public void prePersist() {
        systemInsertTime = MoreObjects.firstNonNull(systemInsertTime, Instant.now());
        systemUpdateTime = MoreObjects.firstNonNull(systemUpdateTime, Instant.now());
        systemInsertType = MoreObjects.firstNonNull(systemInsertType, SystemTypes.System.name());
        systemUpdateType = MoreObjects.firstNonNull(systemUpdateType, SystemTypes.System.name());
    }

    @PreUpdate
    public void preUpdate() {
        systemUpdateTime = Instant.now();
        systemUpdateType = MoreObjects.firstNonNull(systemUpdateType, SystemTypes.System.name());
    }
}

Second, we define our System enum:

public enum SystemTypes {
    System
}

Third, we use our previous class in our entities:

import lombok.*;

import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.UUID;

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "sample_line_entity", schema = "sample", catalog = "postgres")
public class SampleLineEntity extends SystemTypeCollection implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Column(name = "id", nullable = false)
    private UUID id;
}

Finally, we create the “SampleLineEntity” with SQL:

create table if not exists sample_line_entity (
    id uuid not null default uuid_generate_v4(),
    system_insert_time timestamp not null,
    system_update_time timestamp not null,
    system_insert_type varchar not null,
    system_update_type varchar not null,
    constraint sample_line_entity_pkey primary key (id),
);

You should be good to go. The “SampleLineEntity”, on save or update, should have the “system_*” fields populated. As said before, adding a trigger will be a an exercise left to the reader.

Leave a Reply Cancel reply