1. Introduction

When we design the database, we will definitely bring the audit information such as new, updated time and operator. The reason to bring this information is if one day the company’s database was deleted artificially, although there may be a database backup to recover the data. But we still need to track down who did it, when it was done, what it was done and so on, so that we can fix the blame and fix it. But it’s tedious for us to change every piece of data without explicitly changing the information, and we want to process the information imperceptibly.

2. General mode

So what’s a good solution? Provide @createdby and @lastModifiedby in the Spring Data framework to capture who created or modified entities and @CreatedDate and @lastModifiedDate to capture appropriately created or modified entities. You can use these features if you use the relevant framework. So in fact, we know that Spring Data JDBC, Spring Data JPA is not the mainstream, the mainstream is Mybatis. So what are our options?

2.1 Develop Mybatis audit plug-in

If you use native Mybatis, you can write an audit plug-in to implement these features. I explained the Mybatis plugin tutorial earlier, it’s not too difficult. If you want to use it, GitHub offers a variety of Mybatis audit components to choose from. I was going to write one by hand, but it’s a good one. You can search for them by using the keyword Mybatis Audit and choose the one that suits you best.

2.2 Mybatis Plus fills automatically

If you are using Mybatis Plus, you can use its auto-fill feature to do this.

Based on Mybatis Plus 3.3.0

Just implement the MetaObjectHandler interface:

@Component
public class MybatisAuditHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        // Declare the logic for automatically filling fields.
        String userId = AuthHolder.getCurrentUserId();
        this.strictInsertFill(metaObject,"creator",String.class, userId);
        this.strictInsertFill(metaObject,"createTime", LocalDateTime.class,LocalDateTime.now());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        // Declare the logic for automatically filling fields.
        String userId = AuthHolder.getCurrentUserId();
        this.strictUpdateFill(metaObject,"updater",String.class,userId);
        this.strictUpdateFill(metaObject,"updateTime", LocalDateTime.class,LocalDateTime.now()); }}Copy the code

Then we extend Mybatis Plus’ Model

to place the public audit field and declare the corresponding padding policy:

public abstract class BaseEntity<T extends Model<? >>extends Model<T> {

    @TableField(fill = FieldFill.INSERT)
    private String creator;
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime addTime;
    @TableField(fill = FieldFill.UPDATE)
    private String updater;
    @TableField(fill = FieldFill.UPDATE)
    private LocalDateTime updateTime;
}
Copy the code

Finally, our entity class no longer inherits Model

directly but BaseEntity

above:

@Data
@EqualsAndHashCode(callSuper = false)
public class UserInfo extends BaseEntity<UserInfo> {
    @TableId(value = "user_id", type = IdType.ASSIGN_ID)
    private String userId;
    private String username;

    @Override
    protected Serializable pkVal(a) {
        return this.userId; }}Copy the code

So we don’t have to worry about these public fields anymore, but you can add as many fields as you need.

3. Summary

Today we SQL audit in some common fields of automatic filling of the common scheme for some introduction, especially to Mybatis Plus provides the function of the introduction I believe can help you simplify some boilerplate code. But SQL auditing is more than that, and can be designed differently depending on your business. If the design is more refined, all database access traffic will be collected by mirroring or probe, and all database access and operation behaviors will be recorded based on SQL syntax and semantic parsing technology. You can get relevant information from the Internet when you are free. Today is here, pay attention to: code farmers xiao Pangge, get more practical programming dry goods.