Spring Boot中如何审计数据库的增删改事务

一、核心审计表设计 #

单表存储所有审计数据，DDL如下，覆盖你需要的所有字段：

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '审计日志主键',
    snapshot_id VARCHAR(64) NOT NULL COMMENT '快照唯一标识，关联同一次操作的所有记录',
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL COMMENT '操作类型',
    operator VARCHAR(64) NOT NULL COMMENT '操作人账号/标识',
    operate_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    source_file VARCHAR(255) COMMENT '操作来源文件（含行号）',
    target_table VARCHAR(64) NOT NULL COMMENT '被操作的业务表名',
    target_pk VARCHAR(128) NOT NULL COMMENT '被操作的业务记录主键',
    historical_state JSON COMMENT '操作前的历史状态（仅UPDATE/DELETE时非空）',
    current_state JSON COMMENT '操作后的当前状态（仅INSERT/UPDATE时非空）',
    ext_info VARCHAR(512) COMMENT '扩展信息'
);

二、Spring Boot 核心实现步骤 #

1. 依赖配置 #

添加必要的Maven依赖：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
    </dependency>
</dependencies>

2. 审计元数据工具类 #

封装操作人、来源文件、快照ID的获取逻辑：

@Component
public class AuditMetaUtils {
    @Autowired
    private Authentication authentication;

    // 生成全局唯一快照ID
    public String generateSnapshotId() {
        return UUID.randomUUID().toString().replace("-", "");
    }

    // 获取当前操作人（从Spring Security上下文，无登录用户时返回SYSTEM）
    public String getCurrentOperator() {
        if (authentication == null || !authentication.isAuthenticated()) {
            return "SYSTEM";
        }
        return authentication.getName();
    }

    // 获取操作来源文件（跳过框架类，定位业务代码文件）
    public String getSourceFile() {
        StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
        for (StackTraceElement element : stackTrace) {
            String className = element.getClassName();
            if (!className.startsWith("org.springframework.") 
                && !className.startsWith("com.yourproject.audit.")
                && element.getFileName() != null) {
               </think_never_used_51bce0c785ca2f68081bfa7d91973934>
                return element.getFileName() + ":" + element.getLineNumber();
            }
        }
        return "UNKNOWN";
    }
}

3. 审计实体与Repository #

定义JPA实体和数据访问层：

@Entity
@Table(name = "audit_log")
public class AuditLog {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "snapshot_id", nullable = false)
    private String snapshotId;

    @Enumerated(EnumType.STRING)
    @Column(name = "operation_type", nullable = false)
    private OperationType operationType;

    @Column(name = "operator", nullable = false)
    private String operator;

    @Column(name = "operate_time", nullable = false)
    private LocalDateTime operateTime;

    @Column(name = "source_file")
    private String sourceFile;

    @Column(name = "target_table", nullable = false)
    private String targetTable;

    @Column(name = "target_pk", nullable = false)
    private String targetPk;

    @Column(name = "historical_state", columnDefinition = "JSON")
    private String historicalState;

    @Column(name = "current_state", columnDefinition = "JSON")
    private String currentState;

    @Column(name = "ext_info")
    private String extInfo;

    public enum OperationType {
        INSERT, UPDATE, DELETE
    }

    // Getter、Setter、构造器省略
}

public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {
    List<AuditLog> findBySnapshotId(String snapshotId);
    List<AuditLog> findByTargetTableAndTargetPkOrderByOperateTimeDesc(String targetTable, String targetPk);
}

4. AOP切面实现（核心逻辑） #

拦截业务层CRUD方法，自动生成审计日志：

@Aspect
@Component
public class AuditAspect {
    @Autowired
    private AuditLogRepository auditLogRepository;
    @Autowired
    private AuditMetaUtils auditMetaUtils;
    @Autowired
    private ObjectMapper objectMapper;

    // 切入点：拦截业务Service层的save/update/delete方法
    @Pointcut("execution(* com.yourproject.service.*.*(..)) && (execution(* save*(..)) || execution(* update*(..)) || execution(* delete*(..)))")
    public void auditPointcut() {}

    @Around("auditPointcut()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        String methodName = signature.getMethod().getName();
        AuditLog.OperationType operationType = determineOperationType(methodName);

        Object[] args = joinPoint.getArgs();
        if (args.length == 0) {
            return joinPoint.proceed();
        }

        Object targetObj = args[0];
        String targetTable = getTableName(targetObj.getClass());
        String targetPk = getPrimaryKeyValue(targetObj);
        String snapshotId = auditMetaUtils.generateSnapshotId();

        AuditLog auditLog = new AuditLog();
        auditLog.setSnapshotId(snapshotId);
        auditLog.setOperationType(operationType);
        auditLog.setOperator(auditMetaUtils.getCurrentOperator());
        auditLog.setOperateTime(LocalDateTime.now());
        auditLog.setSourceFile(auditMetaUtils.getSourceFile());
        auditLog.setTargetTable(targetTable);
        auditLog.setTargetPk(targetPk);

        // 记录历史状态（UPDATE/DELETE操作）
        if (operationType == AuditLog.OperationType.UPDATE || operationType == AuditLog.OperationType.DELETE) {
            Object historicalObj = getHistoricalObject(targetObj.getClass(), targetPk);
            if (historicalObj != null) {
                auditLog.setHistoricalState(objectMapper.writeValueAsString(historicalObj));
            }
        }

        // 执行原业务方法
        Object result = joinPoint.proceed();

        // 记录当前状态（INSERT/UPDATE操作）
        if (operationType == AuditLog.OperationType.INSERT || operationType == AuditLog.OperationType.UPDATE) {
            Object currentObj = operationType == AuditLog.OperationType.INSERT ? result : targetObj;
            auditLog.setCurrentState(objectMapper.writeValueAsString(currentObj));
        }

        auditLogRepository.save(auditLog);
        return result;
    }

    // 根据方法名判断操作类型
    private AuditLog.OperationType determineOperationType(String methodName) {
        if (methodName.startsWith("save") || methodName.startsWith("add")) {
            return AuditLog.OperationType.INSERT;
        } else if (methodName.startsWith("update")) {
            return AuditLog.OperationType.UPDATE;
        } else if (methodName.startsWith("delete")) {
            return AuditLog.OperationType.DELETE;
        }
        throw new IllegalArgumentException("Unsupported operation method: " + methodName);
    }

    // 获取实体对应的表名
    private String getTableName(Class<?> clazz) {
        Table tableAnnotation = clazz.getAnnotation(Table.class);
        return tableAnnotation != null ? tableAnnotation.name() : clazz.getSimpleName().toLowerCase();
    }

    // 获取实体主键值（假设主键字段为id，可根据业务调整）
    private String getPrimaryKeyValue(Object obj) {
        try {
            Field idField = obj.getClass().getDeclaredField("id");
            idField.setAccessible(true);
            Object idValue = idField.get(obj);
            return idValue != null ? idValue.toString() : "UNKNOWN";
        } catch (Exception e) {
            return "UNKNOWN";
        }
    }

    // 查询历史记录（通过Repository获取原数据，需根据业务调整主键类型）
    private Object getHistoricalObject(Class<?> clazz, String pk) {
        String repoBeanName = clazz.getSimpleName() + "Repository";
        JpaRepository<?, ?> repository = (JpaRepository<?, ?>) SpringContextUtils.getBean(repoBeanName);
        return repository.findById(Long.parseLong(pk)).orElse(null);
    }
}

// Spring上下文工具类
@Component
public class SpringContextUtils implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext context) throws BeansException {
        applicationContext = context;
    }

    public static Object getBean(String beanName) {
        return applicationContext.getBean(beanName);
    }
}

5. 自定义扩展点 #

• 批量操作支持：在切面中遍历参数列表，生成多条关联同一Snapshot ID的审计记录
• 敏感字段屏蔽：自定义Jackson过滤器，序列化状态时屏蔽密码等敏感字段
• 异步审计：将auditLogRepository.save()标记为@Async，避免影响主业务性能

三、常用审计查询示例 #

// 查询某条业务记录的完整操作历史
List<AuditLog> userHistory = auditLogRepository.findByTargetTableAndTargetPkOrderByOperateTimeDesc("user", "1001");

// 查询某次批量操作的所有关联审计记录
List<AuditLog> batchLogs = auditLogRepository.findBySnapshotId("f47ac10b58cc234567890abcdef12345");

四、注意事项 #

• 性能优化：给snapshot_id、target_table + target_pk、operate_time添加索引，定期归档历史审计数据
• 事务一致性：确保审计日志与业务操作在同一事务中，避免业务成功但审计失败
• 序列化兼容：给业务实体添加@JsonIgnoreProperties(ignoreUnknown = true)，兼容字段变更后的历史数据

内容的提问来源于stack exchange，提问作者Ramesh Gadhavi