- What database column types you should use
Your first question was:
What data types would you use in the database (assuming MySQL, possibly in a different timezone that the JVM)? Will the data types be timezone-aware?
In MySQL, the TIMESTAMP column type does a shifting from the JDBC driver local time zone to the database timezone, but it can only store timestamps up to 2038-01-19 03:14:07.999999, so it's not the best choice for the future.
So, better to use DATETIME instead, which doesn't have this upper boundary limitation. However, DATETIME is not timezone aware. So, for this reason, it's best to use UTC on the database side and use the hibernate.jdbc.time_zone Hibernate property.
- What entity property type you should use
Your second question was:
What data types would you use in Java (Date, Calendar, long, ...)?
On the Java side, you can use the Java 8 LocalDateTime. You can also use the legacy Date, but the Java 8 Date/Time types are better since they are immutable, and don't do a timezone shifting to local timezone when logging them.
Now, we can also answer this question:
What annotations would you use for the mapping (e.g. @Temporal)?
If you are using the LocalDateTime or java.sql.Timestamp to map a timestamp entity property, then you don't need to use @Temporal since HIbernate already knows that this property is to be saved as a JDBC Timestamp.
Only if you are using java.util.Date, you need to specify the @Temporal annotation, like this:
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "created_on")
private Date createdOn;
But, it's much better if you map it like this:
@Column(name = "created_on")
private LocalDateTime createdOn;
How to generate the audit column values
Your third question was:
Whom would you make responsible for setting the timestamps—the database, the ORM framework (Hibernate), or the application programmer?
What annotations would you use for the mapping (e.g. @Temporal)?
There are many ways you can achieve this goal. You can allow the database to do that..
For the create_on column, you could use a DEFAULT DDL constraint, like :
ALTER TABLE post 
ADD CONSTRAINT created_on_default 
DEFAULT CURRENT_TIMESTAMP() FOR created_on;
For the updated_on column, you could use a DB trigger to set the column value with CURRENT_TIMESTAMP() every time a given row is modified.
Or, use JPA or Hibernate to set those.
Let's assume you have the following database tables:

And, each table has columns like:
- created_by
- created_on
- updated_by
- updated_on
Using Hibernate @CreationTimestamp and @UpdateTimestamp annotations
Hibernate offers the @CreationTimestamp and @UpdateTimestamp annotations that can be used to map the created_on and updated_on columns.
You can use @MappedSuperclass to define a base class that will be extended by all entities:
@MappedSuperclass
public class BaseEntity {
 
    @Id
    @GeneratedValue
    private Long id;
 
    @Column(name = "created_on")
    @CreationTimestamp
    private LocalDateTime createdOn;
 
    @Column(name = "created_by")
    private String createdBy;
 
    @Column(name = "updated_on")
    @UpdateTimestamp
    private LocalDateTime updatedOn;
 
    @Column(name = "updated_by")
    private String updatedBy;
 
    //Getters and setters omitted for brevity
}
And, all entities will extend the BaseEntity, like this:
@Entity(name = "Post")
@Table(name = "post")
public class Post extend BaseEntity {
 
    private String title;
 
    @OneToMany(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<PostComment> comments = new ArrayList<>();
 
    @OneToOne(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true,
        fetch = FetchType.LAZY
    )
    private PostDetails details;
 
    @ManyToMany
    @JoinTable(
        name = "post_tag",
        joinColumns = @JoinColumn(
            name = "post_id"
        ),
        inverseJoinColumns = @JoinColumn(
            name = "tag_id"
        )
    )
    private List<Tag> tags = new ArrayList<>();
 
    //Getters and setters omitted for brevity
}
However, even if the createdOn and updateOn properties are set by the Hibernate-specific @CreationTimestamp and @UpdateTimestamp annotations, the createdBy and updatedBy require registering an application callback, as illustrated by the following JPA solution.
Using JPA @EntityListeners
You can encapsulate the audit properties in an Embeddable:
@Embeddable
public class Audit {
 
    @Column(name = "created_on")
    private LocalDateTime createdOn;
 
    @Column(name = "created_by")
    private String createdBy;
 
    @Column(name = "updated_on")
    private LocalDateTime updatedOn;
 
    @Column(name = "updated_by")
    private String updatedBy;
 
    //Getters and setters omitted for brevity
}
And, create an AuditListener to set the audit properties:
public class AuditListener {
 
    @PrePersist
    public void setCreatedOn(Auditable auditable) {
        Audit audit = auditable.getAudit();
 
        if(audit == null) {
            audit = new Audit();
            auditable.setAudit(audit);
        }
 
        audit.setCreatedOn(LocalDateTime.now());
        audit.setCreatedBy(LoggedUser.get());
    }
 
    @PreUpdate
    public void setUpdatedOn(Auditable auditable) {
        Audit audit = auditable.getAudit();
 
        audit.setUpdatedOn(LocalDateTime.now());
        audit.setUpdatedBy(LoggedUser.get());
    }
}
To register the AuditListener, you can use the @EntityListeners JPA annotation:
@Entity(name = "Post")
@Table(name = "post")
@EntityListeners(AuditListener.class)
public class Post implements Auditable {
 
    @Id
    private Long id;
 
    @Embedded
    private Audit audit;
 
    private String title;
 
    @OneToMany(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<PostComment> comments = new ArrayList<>();
 
    @OneToOne(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true,
        fetch = FetchType.LAZY
    )
    private PostDetails details;
 
    @ManyToMany
    @JoinTable(
        name = "post_tag",
        joinColumns = @JoinColumn(
            name = "post_id"
        ),
        inverseJoinColumns = @JoinColumn(
            name = "tag_id"
        )
    )
    private List<Tag> tags = new ArrayList<>();
 
    //Getters and setters omitted for brevity
}