My latest and most complete articles are in the pumpkin slow say www.pkslow.com, welcome to tea!

1 Database Audit

Database audit refers to that when the database has recorded changes, it can record the change time and the change person of the database, so that it is convenient to recall the responsibility for problems in the future. Changes to audit table records can be made in two ways: one is to create an audit table dedicated to records, and the other is to add fields to the database. This article discusses the second option.

How to add, modify, and delete records at the same time? If each table is logged separately, the code becomes redundant. A better approach would be to do a section or event monitor, and record data changes as they occur.

2 Spring Data JPA Audit

Spring Data JPA provides a convenient Audit facility to mark fields with four annotations:

(1) @createdby: founder

(2) @createdDate: creation time

(3) @lastmodifiedby: LastModifiedBy

(4) @lastModifiedDate: last modified time

So let’s see how it works.

2.1 Project Preparation

Start a PostgreSQL database with Docker:

docker run -itd \ --name pkslow-postgres \ -e POSTGRES_DB=pkslow \ -e POSTGRES_USER=pkslow \ -e POSTGRES_PASSWORD=pkslow  \ -e PGDATA=/var/lib/postgresql/data/pgdata \ -p 5432:5432 \ postgres:10Copy the code

Introducing dependent dependencies:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-security</artifactId>
</dependency>
Copy the code

Spring Security is not required and is used here to get the user name. The following users are configured:

spring.security.user.name=pkslow
spring.security.user.password=123456
Copy the code

2.2 Creating an Entity Parent Class

The parent class is not required. You can configure the parent class for each entity class that you want to Audit, but it is a bit more cumbersome. Instead, create a parent class and have all subclasses that want to Audit inherit it:

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public class Auditable<U> {
    @CreatedBy
    @Column(name = "created_by")
    private U createdBy;

    @CreatedDate
    @Column(name = "created_date")
    private Date createdDate;

    @LastModifiedBy
    @Column(name = "last_modified_by")
    private U lastModifiedBy;

    @LastModifiedDate
    @Column(name = "last_modified_date")
    private Date lastModifiedDate;
  // getter
  //setter
}
Copy the code

@mappedSuperClass allows other child entity classes to inherit related fields and attributes;

EntityListeners set up classes for callbacks to new additions and changes.

With a parent class, subclasses are simple:

@Entity
@Table(name = "pkslow_users")
public class User extends Auditable<String> {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long userId;
    private String name;
    private String email;
    private String country;
    private String website;
  //getter setter
}
Copy the code

2.3 How do I Obtain a Name

Data is always modified, we need to provide an interface to get the name of the modifier, configuration as follows:

@Configuration
@EnableJpaAuditing(auditorAwareRef = "auditorProvider")
public class JpaAuditingConfiguration {

    @Bean
    public AuditorAware<String> auditorProvider(a) {
        return () -> {
            String username = "system";
            SecurityContext context = SecurityContextHolder.getContext();
            if(context ! =null) {
                Authentication authentication = context.getAuthentication();
                if(authentication ! =null) {
                    username = authentication.getName();
                }
            }

            String result = username;
            returnOptional.ofNullable(result); }; }}Copy the code

You can use the Spring Security Context to get the name of the logged-in user. There are other options, such as getting a field in the request header.

Note @enablejpaauditing enables the auditing function.

2.4 test

Let’s add data to a Controller and see what happens:

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserRepository userRepository;
    @PostMapping
    public User save(@RequestBody User user) {
        returnuserRepository.save(user); }}Copy the code

Run the curl command to test the following parameters:

$ curl 'http://localhost:8088/user' -X POST \
> -H 'Content-Type: application/json' \
> -H 'Authorization:Basic cGtzbG93OjEyMzQ1Ng==' \
> -d '{ > "name":"larry", > "email":"[email protected]", > "country":"China", > "website":"www.pkslow.com" > }'
{"createdBy":"pkslow"."createdDate":"The 2021-01-15 T15:08:47. 035 + 0000"."lastModifiedBy":"pkslow"."lastModifiedDate":"The 2021-01-15 T15:08:47. 035 + 0000"."userId": 7,"name":"larry"."email":"[email protected]"."country":"China"."website":"www.pkslow.com"}
Copy the code

Check the database, audit records have been generated:

3 summary

Please check the code: github.com/LarryDpk/pk…