Servlet重复语句最佳实践：初始化阶段复用连接与预编译语句是否可行？

Great question—this is a super common tradeoff when optimizing high-concurrency Tomcat servlets, so let’s break this down clearly to help you decide.

Short answer: Yes, but only if you pair it with a proper connection pool and fail-safe mechanisms. Your core idea is sound—avoiding per-request connection creation and SQL compilation cuts down on overhead that kills performance in high-traffic scenarios. Let’s tackle your concerns one by one.

Addressing your biggest worries #

1. Connection drops & recovery complexity #

Holding a single long-lived connection is the root of your recovery fear, so here’s how to fix that:

• Ditch manual connection management for a connection pool (like HikariCP, Apache DBCP2, or Tomcat’s built-in pool). These libraries handle connection health automatically: they run validation queries to detect dead connections, recycle them, and create new ones as needed. You won’t have to write custom retry/recovery code.
• If you must precompile SQL statements, bind them to connections in the pool. When a connection is recycled, recompile the statements for the new connection. Most pools let you hook into connection initialization events (e.g., HikariCP’s connectionInitSql) to run precompilation logic.
• Add a quick connection check in your request handlers as a safety net: if the connection is closed, fetch a new one from the pool before proceeding.

2. Connection exhaustion crashing the server #

This is only a risk if you’re creating fixed, unmanaged connections in init(). Fix it with:

• Strict connection pool limits: Configure a maximumPoolSize that’s lower than your database’s max connection limit (most DBs default to 100-200). This prevents your app from overwhelming the database.
• Enforce connection cleanup: Never let the servlet hold onto a connection permanently. Each request should borrow a connection from the pool, use it, and return it immediately (use try-with-resources blocks to avoid leaks):

  try (Connection conn = dataSource.getConnection()) {
      // Execute precompiled statement
  } catch (SQLException e) {
      // Handle error
  }
  

• Set reasonable timeouts: Configure connectionTimeout (how long a request waits for a connection) and idleTimeout (how long idle connections are kept alive) to prevent resource stagnation.

3. Increased system complexity #

You can minimize this by leaning on battle-tested tools instead of building custom logic:

• Initialize the connection pool once (not per servlet) using a ServletContextListener. This lets all servlets share the same pool, reducing redundant code.
• Keep DB logic encapsulated in a separate service class, not directly in the servlet. The servlet only handles HTTP requests, while the service manages connection fetching and precompiled statements.
• Use destroy() in your servlet or listener to shut down the connection pool gracefully when the app stops, avoiding resource leaks.

If managing init-phase resources feels too risky, these options still boost performance without the overhead:

• Use a framework like Spring MVC or Jakarta EE, which handles connection pooling and resource lifecycle out of the box.
• Add a caching layer (e.g., Redis) for frequent queries to reduce DB hits entirely, lowering connection demand.
• Use statement caching built into your JDBC driver or connection pool—many drivers automatically cache precompiled statements without you needing to handle them in init().

Your initial idea is valid and can deliver meaningful performance gains for high-concurrency servlets. The key is to avoid manual connection management and rely on a robust connection pool to handle the heavy lifting of health checks, recovery, and resource limits. With the right setup, you’ll get the performance boost without the worst-case risks you’re worried about.

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