Java实现数据库审计案例

wen java案例 2026-08-04 1

本文目录导读：

Java实现数据库审计案例

Maven依赖配置
数据库配置
核心实体类
审计注解定义
审计核心实现
审计服务层
Mapper层
工具类
异步配置
使用示例
查询接口
最佳实践建议

我将为您提供一个完整的Java数据库审计案例,包含核心实现、配置和最佳实践。

Maven依赖配置

<!-- pom.xml -->
<dependencies>
    <!-- Spring Boot -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- MyBatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.3.0</version>
    </dependency>
    <!-- MySQL -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!-- AOP for audit logging -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
    <!-- JSON处理 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>2.0.25</version>
    </dependency>
    <!-- 连接池监控（可选） -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

数据库配置

-- 审计日志表
CREATE TABLE `audit_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT COMMENT '操作用户ID',
    `username` VARCHAR(64) COMMENT '操作用户名',
    `operation_type` VARCHAR(32) COMMENT '操作类型：INSERT/UPDATE/DELETE/SELECT',
    `table_name` VARCHAR(128) COMMENT '操作表名',
    `record_id` BIGINT COMMENT '操作记录ID',
    `operation_time` DATETIME(3) COMMENT '操作时间',
    `ip_address` VARCHAR(64) COMMENT 'IP地址',
    `client_info` VARCHAR(255) COMMENT '客户端信息',
    `old_value` JSON COMMENT '修改前数据',
    `new_value` JSON COMMENT '修改后数据',
    `sql_statement` TEXT COMMENT '执行的SQL',
    `method_name` VARCHAR(255) COMMENT '方法名',
    `execution_time` BIGINT COMMENT '执行耗时(ms)',
    `status` TINYINT COMMENT '状态：0-成功 1-失败',
    `error_message` TEXT COMMENT '错误信息',
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_operation_time` (`operation_time`),
    KEY `idx_table_name` (`table_name`),
    KEY `idx_operation_type` (`operation_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库审计日志表';
-- 审计配置表
CREATE TABLE `audit_config` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `table_name` VARCHAR(128) NOT NULL COMMENT '表名',
    `aop_pointcut` VARCHAR(255) COMMENT '切点表达式',
    `enabled` TINYINT DEFAULT 1 COMMENT '是否启用',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审计配置表';

核心实体类

// AuditLog.java
package com.example.audit.entity;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class AuditLog {
    private Long id;
    private Long userId;
    private String username;
    private String operationType;
    private String tableName;
    private Long recordId;
    private LocalDateTime operationTime;
    private String ipAddress;
    private String clientInfo;
    private String oldValue;
    private String newValue;
    private String sqlStatement;
    private String methodName;
    private Long executionTime;
    private Integer status;
    private String errorMessage;
}
// AuditConfigEntity.java
package com.example.audit.entity;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class AuditConfigEntity {
    private Long id;
    private String tableName;
    private String aopPointcut;
    private Integer enabled;
    private LocalDateTime createTime;
}

审计注解定义

// @AuditOperation.java
package com.example.audit.annotation;
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AuditOperation {
    // 操作类型
    String operationType() default "";
    // 表名
    String tableName() default "";
    // 是否记录SQL
    boolean recordSql() default true;
    // 描述
    String description() default "";
}
// @AuditField.java
package com.example.audit.annotation;
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AuditField {
    // 字段名称
    String fieldName() default "";
    // 是否敏感字段（加密存储）
    boolean sensitive() default false;
}

审计核心实现

// AuditAspect.java
package com.example.audit.aspect;
import com.alibaba.fastjson.JSON;
import com.example.audit.annotation.AuditOperation;
import com.example.audit.entity.AuditLog;
import com.example.audit.service.AuditService;
import com.example.audit.util.IPUtils;
import com.example.audit.util.UserContext;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
@Slf4j
@Aspect
@Component
public class AuditAspect {
    @Autowired
    private AuditService auditService;
    @Around("@annotation(auditOperation)")
    public Object around(ProceedingJoinPoint joinPoint, AuditOperation auditOperation) throws Throwable {
        long startTime = System.currentTimeMillis();
        AuditLog auditLog = new AuditLog();
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        // 获取基本信息
        auditLog.setOperationType(auditOperation.operationType());
        auditLog.setTableName(auditOperation.tableName());
        auditLog.setMethodName(signature.getMethod().getName());
        auditLog.setOperationTime(LocalDateTime.now());
        auditLog.setStatus(0);
        // 获取用户信息
        try {
            auditLog.setUserId(UserContext.getUserId());
            auditLog.setUsername(UserContext.getUsername());
        } catch (Exception e) {
            auditLog.setUserId(null);
            auditLog.setUsername("anonymous");
        }
        // 获取IP和客户端信息
        try {
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            if (attributes != null) {
                HttpServletRequest request = attributes.getRequest();
                auditLog.setIpAddress(IPUtils.getClientIp(request));
                auditLog.setClientInfo(request.getHeader("User-Agent"));
            }
        } catch (Exception e) {
            log.warn("Failed to get request info", e);
        }
        Object result;
        try {
            // 执行方法
            result = joinPoint.proceed();
            // 记录执行时间
            auditLog.setExecutionTime(System.currentTimeMillis() - startTime);
            // 获取参数数据
            Object[] args = joinPoint.getArgs();
            if (args != null && args.length > 0) {
                // 获取记录ID和新值
                Map<String, Object> dataMap = extractDataFromArgs(args);
                auditLog.setRecordId((Long) dataMap.get("id"));
                if (auditOperation.recordSql() && dataMap.containsKey("newValue")) {
                    auditLog.setNewValue(JSON.toJSONString(dataMap.get("newValue")));
                }
            }
            // 保存审计日志
            auditService.saveAuditLog(auditLog);
        } catch (Throwable throwable) {
            // 记录错误信息
            auditLog.setStatus(1);
            auditLog.setErrorMessage(throwable.getMessage());
            auditLog.setExecutionTime(System.currentTimeMillis() - startTime);
            // 保存失败的审计日志
            auditService.saveAuditLog(auditLog);
            log.error("Audit operation failed", throwable);
            throw throwable;
        }
        return result;
    }
    private Map<String, Object> extractDataFromArgs(Object[] args) {
        Map<String, Object> result = new HashMap<>();
        for (Object arg : args) {
            if (arg == null) continue;
            // 提取ID
            try {
                Field idField = arg.getClass().getDeclaredField("id");
                idField.setAccessible(true);
                Object id = idField.get(arg);
                if (id != null) {
                    result.put("id", Long.valueOf(id.toString()));
                }
            } catch (NoSuchFieldException e) {
                // 忽略没有id字段的对象
            } catch (Exception e) {
                log.warn("Failed to extract id", e);
            }
            // 提取完整对象作为新值
            result.put("newValue", arg);
            break;
        }
        return result;
    }
}

审计服务层

// AuditService.java
package com.example.audit.service;
import com.example.audit.entity.AuditLog;
import com.example.audit.mapper.AuditLogMapper;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
public class AuditService {
    @Autowired
    private AuditLogMapper auditLogMapper;
    @Autowired
    private AuditConfigService auditConfigService;
    /**
     * 异步保存审计日志
     */
    @Async("auditExecutor")
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void saveAuditLog(AuditLog auditLog) {
        try {
            // 检查是否需要审计
            if (!auditConfigService.isAuditEnabled(auditLog.getTableName())) {
                return;
            }
            // 脱敏处理
            String sensitiveData = JSON.toJSONString(auditLog);
            auditLogMapper.insert(auditLog);
            log.debug("Audit log saved: {}", auditLog.getId());
        } catch (DataAccessException e) {
            log.error("Failed to save audit log", e);
            // 审计日志保存失败不影响业务操作
        }
    }
    /**
     * 批量保存审计日志
     */
    @Transactional
    public void saveAuditLogsBatch(java.util.List<AuditLog> auditLogs) {
        if (auditLogs.isEmpty()) return;
        try {
            auditLogMapper.insertBatch(auditLogs);
        } catch (DataAccessException e) {
            log.error("Failed to save audit logs batch", e);
        }
    }
}
// AuditConfigService.java
package com.example.audit.service;
import com.example.audit.mapper.AuditConfigMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.PostConstruct;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
@Slf4j
@Service
public class AuditConfigService {
    @Autowired
    private AuditConfigMapper auditConfigMapper;
    private Map<String, Boolean> auditConfigCache = new ConcurrentHashMap<>();
    @PostConstruct
    public void init() {
        loadAuditConfig();
    }
    public void loadAuditConfig() {
        try {
            auditConfigMapper.findAll().forEach(config -> {
                auditConfigCache.put(config.getTableName(), config.getEnabled() == 1);
            });
            log.info("Loaded audit config: {} entries", auditConfigCache.size());
        } catch (Exception e) {
            log.error("Failed to load audit config", e);
        }
    }
    public boolean isAuditEnabled(String tableName) {
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }
        return auditConfigCache.getOrDefault(tableName, false);
    }
    public void updateConfig(String tableName, boolean enabled) {
        auditConfigCache.put(tableName, enabled);
        auditConfigMapper.updateEnabled(tableName, enabled ? 1 : 0);
    }
}

Mapper层

// AuditLogMapper.java
package com.example.audit.mapper;
import com.example.audit.entity.AuditLog;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
@Mapper
public interface AuditLogMapper {
    @Insert("INSERT INTO audit_log (" +
            "user_id, username, operation_type, table_name, record_id, " +
            "operation_time, ip_address, client_info, old_value, new_value, " +
            "sql_statement, method_name, execution_time, status, error_message" +
            ") VALUES (" +
            "#{userId}, #{username}, #{operationType}, #{tableName}, #{recordId}, " +
            "#{operationTime}, #{ipAddress}, #{clientInfo}, #{oldValue}, #{newValue}, " +
            "#{sqlStatement}, #{methodName}, #{executionTime}, #{status}, #{errorMessage}" +
            ")")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(AuditLog auditLog);
    @Insert("<script>" +
            "INSERT INTO audit_log (" +
            "user_id, username, operation_type, table_name, record_id, " +
            "operation_time, ip_address, client_info, old_value, new_value, " +
            "sql_statement, method_name, execution_time, status, error_message" +
            ") VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.userId}, #{item.username}, #{item.operationType}, #{item.tableName}, #{item.recordId}, " +
            "#{item.operationTime}, #{item.ipAddress}, #{item.clientInfo}, #{item.oldValue}, #{item.newValue}, " +
            "#{item.sqlStatement}, #{item.methodName}, #{item.executionTime}, #{item.status}, #{item.errorMessage})" +
            "</foreach>" +
            "</script>")
    int insertBatch(@Param("list") List<AuditLog> auditLogs);
    @Select("SELECT * FROM audit_log WHERE id = #{id}")
    AuditLog findById(@Param("id") Long id);
    @Select("SELECT * FROM audit_log WHERE table_name = #{tableName} AND record_id = #{recordId} ORDER BY operation_time DESC")
    List<AuditLog> findByTableAndRecordId(@Param("tableName") String tableName, @Param("recordId") Long recordId);
    @Select("SELECT * FROM audit_log WHERE operation_time BETWEEN #{startTime} AND #{endTime} ORDER BY operation_time DESC")
    List<AuditLog> findByTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    @Select("SELECT * FROM audit_log WHERE username = #{username} ORDER BY operation_time DESC LIMIT #{limit}")
    List<AuditLog> findByUsername(@Param("username") String username, @Param("limit") int limit);
    @Delete("DELETE FROM audit_log WHERE operation_time < #{beforeTime}")
    int deleteOlderThan(@Param("beforeTime") LocalDateTime beforeTime);
    @Update("UPDATE audit_log SET status = #{status}, error_message = #{errorMessage} WHERE id = #{id}")
    int updateStatus(@Param("id") Long id, @Param("status") Integer status, @Param("errorMessage") String errorMessage);
}
// AuditConfigMapper.java
package com.example.audit.mapper;
import com.example.audit.entity.AuditConfigEntity;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface AuditConfigMapper {
    @Select("SELECT * FROM audit_config WHERE enabled = 1")
    List<AuditConfigEntity> findAll();
    @Select("SELECT * FROM audit_config WHERE table_name = #{tableName} AND enabled = 1")
    AuditConfigEntity findByTableName(@Param("tableName") String tableName);
    @Update("UPDATE audit_config SET enabled = #{enabled} WHERE table_name = #{tableName}")
    int updateEnabled(@Param("tableName") String tableName, @Param("enabled") int enabled);
    @Insert("INSERT INTO audit_config (table_name, aop_pointcut, enabled) VALUES (#{tableName}, #{aopPointcut}, #{enabled})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(AuditConfigEntity config);
}

工具类

// IPUtils.java
package com.example.audit.util;
import javax.servlet.http.HttpServletRequest;
public class IPUtils {
    public static String getClientIp(HttpServletRequest request) {
        String ip = request.getHeader("X-Forwarded-For");
        if (ip == null || ip.isEmpty() || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("Proxy-Client-IP");
        }
        if (ip == null || ip.isEmpty() || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("WL-Proxy-Client-IP");
        }
        if (ip == null || ip.isEmpty() || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("HTTP_CLIENT_IP");
        }
        if (ip == null || ip.isEmpty() || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getHeader("HTTP_X_FORWARDED_FOR");
        }
        if (ip == null || ip.isEmpty() || "unknown".equalsIgnoreCase(ip)) {
            ip = request.getRemoteAddr();
        }
        // 处理多IP的情况（第一个IP是真实IP）
        if (ip != null && ip.contains(",")) {
            ip = ip.split(",")[0].trim();
        }
        return ip;
    }
}
// UserContext.java
package com.example.audit.util;
public class UserContext {
    private static final ThreadLocal<Long> USER_ID = new ThreadLocal<>();
    private static final ThreadLocal<String> USERNAME = new ThreadLocal<>();
    public static void setUser(Long userId, String username) {
        USER_ID.set(userId);
        USERNAME.set(username);
    }
    public static Long getUserId() {
        return USER_ID.get();
    }
    public static String getUsername() {
        return USERNAME.get();
    }
    public static void clear() {
        USER_ID.remove();
        USERNAME.remove();
    }
}

异步配置

// AsyncConfig.java
package com.example.audit.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import java.util.concurrent.Executor;
import java.util.concurrent.ThreadPoolExecutor;
@Configuration
public class AsyncConfig {
    @Bean("auditExecutor")
    public Executor auditExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(2);
        executor.setMaxPoolSize(5);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("audit-");
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

使用示例

// 业务服务示例
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    @AuditOperation(operationType = "INSERT", tableName = "user")
    public User createUser(User user) {
        // 设置审计用户
        UserContext.setUser(1001L, "admin");
        userMapper.insert(user);
        return user;
    }
    @AuditOperation(operationType = "UPDATE", tableName = "user")
    public User updateUser(User user) {
        // 获取旧值（可选）
        User oldUser = userMapper.findById(user.getId());
        // 业务逻辑
        userMapper.update(user);
        // 可以记录旧值
        // ...
        return user;
    }
    @AuditOperation(operationType = "DELETE", tableName = "user")
    public void deleteUser(Long id) {
        userMapper.deleteById(id);
    }
    @AuditOperation(operationType = "SELECT", tableName = "user")
    public User getUserById(Long id) {
        return userMapper.findById(id);
    }
}
// 控制器示例
@RestController
@RequestMapping("/api/users")
public class UserController {
    @Autowired
    private UserService userService;
    @PostMapping
    public ResponseEntity<?> createUser(@RequestBody User user) {
        return ResponseEntity.ok(userService.createUser(user));
    }
    @PutMapping("/{id}")
    public ResponseEntity<?> updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        return ResponseEntity.ok(userService.updateUser(user));
    }
    @DeleteMapping("/{id}")
    public ResponseEntity<?> deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
        return ResponseEntity.ok().build();
    }
}

查询接口

// AuditController.java
package com.example.audit.controller;
import com.example.audit.entity.AuditLog;
import com.example.audit.service.AuditQueryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.*;
import java.time.LocalDateTime;
import java.util.List;
@RestController
@RequestMapping("/api/audit")
public class AuditController {
    @Autowired
    private AuditQueryService auditQueryService;
    @GetMapping("/query")
    public List<AuditLog> queryAuditLogs(
            @RequestParam(required = false) String username,
            @RequestParam(required = false) String tableName,
            @RequestParam(required = false) String operationType,
            @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime startTime,
            @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime endTime,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "20") int size) {
        return auditQueryService.queryLogs(username, tableName, operationType, startTime, endTime, page, size);
    }
    @GetMapping("/records/{tableName}/{recordId}")
    public List<AuditLog> getRecordAuditLogs(
            @PathVariable String tableName,
            @PathVariable Long recordId) {
        return auditQueryService.getRecordLogs(tableName, recordId);
    }
    @GetMapping("/statistics")
    public Map<String, Object> getStatistics() {
        return auditQueryService.getStatistics();
    }
    @DeleteMapping("/cleanup")
    public ResponseEntity<?> cleanupOldLogs(@RequestParam int days) {
        auditQueryService.cleanupOldLogs(days);
        return ResponseEntity.ok().build();
    }
}

最佳实践建议

数据库审计配置

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC
    username: root
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
audit:
  enabled: true
  # 排除的表
  exclude-tables:
    - audit_log
    - sys_config
  # 敏感字段
  sensitive-fields:
    - password
    - secret_key
    - token
  # 保留天数
  retention-days: 180

核心要点：

异步处理：使用异步线程池保存审计日志，避免影响业务性能
事务管理：审计日志使用独立事务，确保审计不会影响业务事务
数据脱敏：对敏感字段进行加密或脱敏处理
性能优化：使用批量插入、分页查询优化性能
存储策略：定期清理过期日志，避免数据量过大
监控告警：对关键操作进行监控和告警
安全合规：确保符合企业安全合规要求

这个案例提供了完整的数据库审计实现,可以根据实际需求进行调整和扩展。