Database Schema

Table Creation

CREATE TABLE `request_audit_log` (
  `log_id` BIGINT NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NOT NULL COMMENT 'Request timestamp',
  `client_ip` VARCHAR(30) NOT NULL COMMENT 'Client IP address',
  `api_category` VARCHAR(50) NOT NULL DEFAULT 'General' COMMENT 'API category',
  `endpoint_url` VARCHAR(100) NOT NULL COMMENT 'Request URL',
  `http_method` VARCHAR(10) NOT NULL COMMENT 'HTTP method',
  `os_name` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Operating system',
  `browser_name` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Browser name',
  `isp_provider` VARCHAR(15) NULL DEFAULT NULL COMMENT 'Internet Service Provider',
  `country_name` VARCHAR(15) NULL DEFAULT NULL COMMENT 'Country',
  `region_name` VARCHAR(15) NULL DEFAULT NULL COMMENT 'Province/State',
  `city_name` VARCHAR(15) NULL DEFAULT NULL COMMENT 'City',
  PRIMARY KEY (`log_id`)
) COMMENT='Request audit log table';

Backend Implementation

Entity Class

@Data
@TableName(value = "request_audit_log")
public class RequestAuditLog implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId(type = IdType.AUTO)
    private Long logId;

    private Date createdAt;
    private String clientIp;
    private String apiCategory;
    private String endpointUrl;
    private String httpMethod;
    private String osName;
    private String browserName;
    private String ispProvider;
    private String countryName;
    private String regionName;
    private String cityName;
}

Data Access Layer

@Repository
public interface AuditLogMapper extends BaseMapper<RequestAuditLog> {
    List<BrowserStatsVO> aggregateByBrowser();
    List<IspStatsVO> aggregateByIsp();
    List<RegionStatsVO> aggregateByRegion();
    List<DailyTrafficVO> aggregateByDay();
}

Custom Annotation

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AuditEndpoint {
    String category() default "General";
}

Usage example:

@AuditEndpoint(category = "Article Management")
@RestController
@RequestMapping("/api/articles")
public class ArticleController {
    // Controller methods
}

Interceptor Logic

@Slf4j
@Component
public class GlobalAuditInterceptor implements HandlerInterceptor {

    @Autowired
    private AuditLogMapper auditLogMapper;

    @Override
    public boolean preHandle(HttpServletRequest req, HttpServletResponse res, Object handler) throws Exception {
        if (!(handler instanceof HandlerMethod)) {
            return true;
        }
        
        try {
            String clientIp = IpUtil.extractClientIp(req);
            String userAgentHeader = req.getHeader("User-Agent");
            UserAgent ua = UserAgentUtil.parse(userAgentHeader);
            
            HandlerMethod handlerMethod = (HandlerMethod) handler;
            AuditEndpoint auditAnnotation = handlerMethod.getMethod().getDeclaringClass().getAnnotation(AuditEndpoint.class);

            RequestAuditLog auditLog = new RequestAuditLog();
            auditLog.setClientIp(clientIp);
            auditLog.setCreatedAt(new Date());
            
            if (auditAnnotation != null) {
                auditLog.setApiCategory(auditAnnotation.category());
            }
            
            auditLog.setEndpointUrl(req.getRequestURI());
            auditLog.setHttpMethod(req.getMethod());
            auditLog.setOsName(ua.getOs().getName());
            auditLog.setBrowserName(ua.getBrowser().getName());

            try {
                // Geo-location parsing (preferably handled asynchronously in production)
                String geoResponse = HttpUtil.get("https://api.example.com/geo?ip=" + clientIp);
                JSONObject geoData = JSONUtil.parseObj(geoResponse);
                if (geoData.getBool("success")) {
                    JSONObject locationInfo = geoData.getJSONObject("data");
                    auditLog.setIspProvider(locationInfo.getStr("isp"));
                    auditLog.setCountryName(locationInfo.getStr("country"));
                    auditLog.setRegionName(locationInfo.getStr("region"));
                    auditLog.setCityName(locationInfo.getStr("city"));
                }
            } catch (Exception ex) {
                auditLog.setIspProvider("Unknown");
                auditLog.setCountryName("Unknown");
                auditLog.setRegionName("Unknown");
                auditLog.setCityName("Unknown");
            }
            
            auditLogMapper.insert(auditLog);
        } catch (Exception e) {
            log.error("Audit logging failed", e);
        }
        return true;
    }
}

Interceptor Registration

@Configuration
public class WebInterceptorConfig implements WebMvcConfigurer {

    @Autowired
    private GlobalAuditInterceptor globalAuditInterceptor;

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(globalAuditInterceptor)
                .addPathPatterns("/**")
                .excludePathPatterns("/static/**", "/error");
    }
}

Data Visualization with ECharts

View Objects (VOs)

Browser Statistics

@Data
public class BrowserStatsVO {
    private String browserName;
    private Integer totalHits;
}

ISP Distribution

@Data
public class IspStatsVO {
    private String ispProvider;
    private Integer totalHits;
}

Regional Access

@Data
public class RegionStatsVO {
    private String regionName;
    private Integer totalHits;
}

Daily Traffic

@Data
public class DailyTrafficVO {
    private String recordDate;
    private Integer totalHits;
}

Mapper XML Queries

Browser Aggregation

<select id="aggregateByBrowser" resultType="com.example.vo.BrowserStatsVO">
  SELECT browser_name AS browserName, COUNT(*) AS totalHits FROM request_audit_log GROUP BY browser_name
</select>

ISP Aggregation

<select id="aggregateByIsp" resultType="com.example.vo.IspStatsVO">
  SELECT isp_provider AS ispProvider, COUNT(*) AS totalHits FROM request_audit_log GROUP BY isp_provider
</select>

Region Aggregation

<select id="aggregateByRegion" resultType="com.example.vo.RegionStatsVO">
  SELECT region_name AS regionName, COUNT(*) AS totalHits FROM request_audit_log GROUP BY region_name
</select>

Daily Traffic Aggregation

<select id="aggregateByDay" resultType="com.example.vo.DailyTrafficVO">
  SELECT DATE(created_at) AS recordDate, COUNT(*) AS totalHits FROM request_audit_log GROUP BY DATE(created_at)
</select>