How to track database changes dynamically using spring?

Question

I am using spring,spring-data,spring-cronjobs and java-mail. I have a requirement of instantly scanning the changes in a table present in my-sql database and fire a mail to the admin regarding the changes.

All I am doing to achieve this is running a cronjob to scan all the changes in the table, but it is a heavy process as the table is related to monetary transaction and the consumes a lot of resources as a result the application becomes too slow.

So, Is there any better process by which I can track the current changes in the database. For example If there is any method to set watchers in spring to trigger a process on database change it would be helpful.

The following is a sample of the entity of the table I am scanning.

/** Import statements **/

@Entity
public class UserWalletTransaction {

    @Id
    @GeneratedValue
    private Long Id;
    private String toAccount;
    @ManyToOne(fetch = FetchType.LAZY)
    User user;
    @ManyToOne(fetch = FetchType.LAZY)
    Wallet wallet;
    private String senderOrMobile;
    private String benificiaryName;
    private String beniMobile;
    private Double transferAmount;
    private Double sTax;
    private Double charge;
    private Double netAmount;
    private String apiTId;
    private String apiComment;
    private String agentId;
    private Double apiSTax;
    private Double apiCharge;
    private Double apiNetAmount;
    private Double apiBalanceAmount;
    private String transactionMode;
    private String bankName;
    private String userTrackId;
    private String referenceNumber;
    private String operatorDescription;
    private String mobileNumber;
    private String rechargeDateTime;
    private String operatorTransactionId;
    private String hermesPnr;
    private String utId;
    private String status;
    private Double previousAmount;
    private Double balanceAmount;
    private Double amountTransacted;
    private String transactionType;
    private boolean isRaised;
    private boolean isRefunded;
    @Column(name = "created_by")
    private String createdBy;
    @Column(name = "created_date")
    private Date createdDate;
    @Column(name = "updated_by")
    private String updatedBy;
    @Column(name = "updated_date")
    private Date updatedDate;
    private String operationPerformed;
    @OneToOne
    private UserWalletTransaction relationalTransaction;
    private String errorComments;
    @OneToOne
    private User debitUser;
    @OneToOne
    private User creditUser;
    @OneToOne
    private ServiceProvider serviceProvider;
    @OneToOne   
    private RefundRequest refundRequests;

    ..... Getters and setters......
}

And the following is a sample cron job I am using to fire the mail after scanning the database.

/** Import statements **/
@Component
@EnableScheduling
public class ValidateCron {
    @Autowired
    RechargeService rechargeService;
     @Scheduled(fixedDelay = 2)
     public void demoServiceMethod() {
        // Perform tasks related to scan and track the number of records added //after the previous scan. Fire a mail if any change in the database is found. 
     }
}

Please share any method by which I can improve the performance of this functionality. Thanks in advance.

Answer 1

Hibernate has an interceptor mecanism that allows you to get notified, at specific times, when database events occurs.

Such events are creation/deletion/flush of the session. As you get access to the objects being subject to the given event, you have a mean to fire a process when a given object of a given class (which you can easily map to a table in your schema) is modified.

The javadoc can be found here : https://docs.jboss.org/hibernate/orm/4.0/manual/en-US/html/events.html

Apart from your use case, I once used interceptors in an Oracle based, partitionned schema. The idea was to use a technical date to partition some of our tables, and the challenge was to have the same "partition date" for all the objects in a given tree (if we had used SYSDATE at the time of insertion, nothing would prevent one part of the object tree to have a partition date at day N and the rest of the tree at day N+1... which is worrysome at some other levels). An interceptor was used, then, to propagate the same date along the object tree on insertion.