Skip to content

Replace long-lived connection with connection-per-operation for pooling support #528

Description

@nipunsharma12

Problem

The current JDBCBaseAdapter holds a single Connection for the entire lifetime of the adapter instance (assigned in the constructor and never released until close() is called). This causes several issues:

  1. No connection pooling — Even when a pooled DataSource (e.g., HikariCP, Apache DBCP) is provided, only one connection is ever borrowed from the pool, defeating the purpose of pooling.
  2. Not thread-safe — The shared mutable conn field is accessed from all adapter methods without synchronization, leading to race conditions in concurrent environments.
  3. Stale/broken connections — If the connection drops (network timeout, DB restart), the adapter becomes unusable until retry() happens to replace it. The retry mechanism itself is fragile since it just overwrites the field.
  4. Resource leakssetAutoCommit(true) in finally blocks can fail on a broken connection, and the single connection ties up a DB slot for the full application lifetime.

Proposed Solution

Replace the persistent Connection conn field with connection-per-operation semantics using try-with-resources:

  • Each public method (loadPolicy, savePolicy, addPolicies, removePolicy, etc.) obtains a fresh connection from dataSource.getConnection() and releases it at the end of the operation.
  • Transactional operations (removePolicies, updatePolicy, savePolicy) share a single connection within the method scope for atomicity.
  • The retry() helper and conn field are removed entirely — Failsafe retry naturally works by getting a new connection on each attempt.
  • close() becomes a no-op (kept for backward API compatibility with @Deprecated).

Benefits

  • Enables connection pooling — Users can supply a HikariCP/DBCP/Tomcat JDBC DataSource and connections are properly borrowed and returned.
  • Thread-safe — No shared mutable connection state across operations.
  • No stale connections — Each operation gets a fresh (or pool-validated) connection.
  • Proper resource cleanup — try-with-resources guarantees connection release even on exceptions.
  • Backward compatible — The DataSource interface contract is unchanged; users who pass JDBCDataSource (raw DriverManager) still work, just without pooling benefits.

Files Changed (I can't currently make the PR)

  • JDBCBaseAdapter.java — Core refactor (remove conn field, add try-with-resources to all methods, extract removePolicyWithConnection for transactional use)
  • JDBCAdapter.java — Update loadFilteredPolicyFile to use connection-per-operation

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions