Update date only if record updated with data modification

Question

I'm using Oracle JPA for my project. In my project, we receive file and insert or update (if any field value modified) the records in to database table. There is a chances of receiving duplicate (already received) records without field value modifications.

My need is

If the same record received without any change, date column should not be updated.
If new record or same record with any modification, date column should update.

As an example

First insert:

Record to be inserted:

EmpID  |  Name  | Designation  | Salary
=========================================
00001  |  Raj   | Team Lead    |600000
00002  |  Kumar | Developer    |400000

Table after insert:

Emp ID | Name  | Designation | Salary | DATE
===========================================================
00001  | Raj   | Team Lead   | 600000 | 12-04-2018 01:00:00
00002  | Kumar | Developer   | 400000 | 12-04-2018 01:00:00

Second insert:

Record to be inserted:

EmpID  |  Name  | Designation      | Salary
==============================================
00001  |  Raj   | Team Lead        |600000
00002  |  Kumar | Senior Developer |500000

Table after insert/update:

Emp ID | Name  | Designation      | Salary | DATE
===========================================================
00001  | Raj   | Team Lead        | 600000 | 12-04-2018 01:00:00
00002  | Kumar | Senior Developer | 500000 | 13-04-2018 01:00:00

Here only send row date changed as it is only really modified.

Doing this validation in code is tedious task. Kindly say is there any possible solution.

I would calculate an hash of the datas and check if it's different from the very same hash calculated on table datas in a pre-update TRIGGER, if so, update, otherwise skip — BigMike, Apr 13 '18 at 09:53
Possible duplicate of [How to autogenerate created or modified timestamp field?](https://stackoverflow.com/questions/5257709/how-to-autogenerate-created-or-modified-timestamp-field) — crizzis, Apr 13 '18 at 10:15

Bejond · Answer 1 · 2018-04-13T11:38:16.993

You need a Listener to set current time

public class BaseEntityListener {

    @PreUpdate
    public void preUpdate(BaseEntity entity) {

        Date date = new Date();
        entity.setUpdateDate(date);
    }

}

Here is Model defination:

@SuppressWarnings("serial")
@MappedSuperclass
@EntityListeners(BaseEntityListener.class)
public class BaseEntity implements java.io.Serializable {

    @Basic(optional = false)
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "updateDate")
    private Date updateDate;

And update entity use

        entityManager.merge(model);
        entityManager.flush();

updateDate would be updated when any column is changed. It won't be updated when no column is changed.

Additionally the `BaseEntity` class should be annotated with a `@MappedSuperclass` and defined as `abstract`. Furthermore using a `@Basic` annotation with `optional=false` in conjunction with a `@Column` with `nullable=false` annotation is not necessary. — Sal, Apr 13 '18 at 11:37

Update date only if record updated with data modification

1 Answers1