如何用JSP统计Web应用中HTML、CSS、JS等文件的加载次数？

Hey there! Let's tackle this file access count problem for your JSP web app running on Tomcat with MySQL. Here's a practical, step-by-step solution that covers all your target file types:

We'll use a Servlet Filter to intercept every incoming request to your app. For each request, we'll identify the file type (HTML, CSS, JS, JSP, Servlet), then update a MySQL table to track access counts and timestamps. This approach works for both static resources (like CSS/JS) and dynamic components (JSP/Servlet).

1. Database Table Design #

First, create a table to store the statistics. This table will track unique file paths, their types, access counts, and last access time:

CREATE TABLE file_access_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL UNIQUE,
    file_type VARCHAR(50) NOT NULL,
    access_count INT DEFAULT 0,
    last_access_time DATETIME
);

2. Build the Access Count Filter #

Create a Filter class to intercept requests and update the database. We'll use a database connection pool (Tomcat's built-in one) for better performance and thread safety:

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

@WebFilter("/*") // Intercept all incoming requests
public class AccessCountFilter implements Filter {
    private DataSource dataSource;

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        // Initialize connection pool via JNDI (configured in Tomcat)
        try {
            InitialContext initCtx = new InitialContext();
            dataSource = (DataSource) initCtx.lookup("java:/comp/env/jdbc/YourAppDB");
        } catch (NamingException e) {
            throw new ServletException("Failed to connect to database pool", e);
        }
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        HttpServletRequest req = (HttpServletRequest) request;
        String uri = req.getRequestURI();
        String relativePath = uri.substring(req.getContextPath().length()); // Get path relative to app root

        // Skip non-target resources (e.g., images, fonts)
        String fileType = determineFileType(relativePath, req);
        if (fileType == null) {
            chain.doFilter(request, response);
            return;
        }

        // Update stats in database (use try-with-resources to auto-close connections)
        try (Connection conn = dataSource.getConnection()) {
            updateAccessRecord(relativePath, fileType, conn);
        } catch (SQLException e) {
            // Replace with proper logging (e.g., SLF4J/Logback) in production
            e.printStackTrace();
        }

        // Let the request proceed to its target
        chain.doFilter(request, response);
    }

    private String determineFileType(String path, HttpServletRequest req) {
        // Identify static file types by extension
        if (path.endsWith(".html") || path.endsWith(".htm")) return "HTML";
        if (path.endsWith(".css")) return "CSS";
        if (path.endsWith(".js")) return "JS";
        if (path.endsWith(".jsp")) return "JSP";

        // Identify Servlets (exclude JSP's auto-generated Servlet)
        try {
            String servletName = req.getServletPath();
            Servlet servlet = req.getServletContext().getServlet(servletName);
            if (servlet != null && !(servlet instanceof org.apache.jasper.servlet.JspServlet)) {
                return "Servlet";
            }
        } catch (ServletException e) {
            // Not a valid Servlet, skip
        }

        // Return null for resources we don't want to track
        return null;
    }

    private void updateAccessRecord(String filePath, String fileType, Connection conn) throws SQLException {
        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        // Try to update existing record first
        String updateSql = "UPDATE file_access_stats SET access_count = access_count + 1, last_access_time = ? WHERE file_path = ?";
        try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
            updateStmt.setString(1, now);
            updateStmt.setString(2, filePath);
            int affectedRows = updateStmt.executeUpdate();

            // If no record exists, insert a new one
            if (affectedRows == 0) {
                String insertSql = "INSERT INTO file_access_stats (file_path, file_type, access_count, last_access_time) VALUES (?, ?, 1, ?)";
                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.setString(1, filePath);
                    insertStmt.setString(2, fileType);
                    insertStmt.setString(3, now);
                    insertStmt.executeUpdate();
                }
            }
        }
    }

    @Override
    public void destroy() {
        // No cleanup needed for DataSource (managed by Tomcat)
    }
}

3. Configure Tomcat Connection Pool #

Add a database resource to Tomcat's conf/context.xml to set up the connection pool:

<Resource name="jdbc/YourAppDB" auth="Container" type="javax.sql.DataSource"
           maxTotal="100" maxIdle="30" maxWaitMillis="10000"
           username="your_db_user" password="your_db_password"
           driverClassName="com.mysql.cj.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/your_db_name?useSSL=false&amp;serverTimezone=UTC"/>

Make sure the MySQL driver JAR is placed in Tomcat's lib directory.

4. Optional: Build a Stats Dashboard #

Create a JSP page to display the collected statistics:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*, javax.naming.*, javax.sql.DataSource" %>
<html>
<head>
    <title>File Access Statistics</title>
    <style>table {border-collapse: collapse;} th, td {border:1px solid #ddd; padding:8px;}</style>
</head>
<body>
<h1>App File Access Statistics</h1>
<table>
    <tr>
        <th>File Path</th>
        <th>Type</th>
        <th>Access Count</th>
        <th>Last Accessed</th>
    </tr>
    <%
        InitialContext initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/comp/env/jdbc/YourAppDB");
        try (Connection conn = ds.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM file_access_stats ORDER BY access_count DESC")) {

            while (rs.next()) {
    %>
    <tr>
        <td><%= rs.getString("file_path") %></td>
        <td><%= rs.getString("file_type") %></td>
        <td><%= rs.getInt("access_count") %></td>
        <td><%= rs.getString("last_access_time") %></td>
    </tr>
    <%
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    %>
</table>
</body>
</html>

• Thread Safety: Using a connection pool and try-with-resources ensures each request gets its own database connection, avoiding race conditions.
• Performance: For high-traffic apps, add an in-memory cache (like Caffeine) to accumulate counts in memory, then sync to MySQL periodically (e.g., every 5 minutes) to reduce database load.
• Browser Caching: Static resources like CSS/JS may be cached by browsers, so counts might not reflect every user view. For more accuracy, adjust cache headers or add frontend tracking.
• Logging: Replace e.printStackTrace() with a proper logging framework (SLF4J + Logback) for production debugging.

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