Connection Pooling

/**
 * Pooled connection implementation
 * Implements both PooledConnection and XAConnection interfaces
 */
public class MariaDbPoolConnection implements PooledConnection, XAConnection {
  /**
   * Get the underlying database connection
   * @return Connection instance
   * @throws SQLException if connection retrieval fails
   */
  public Connection getConnection() throws SQLException;
  
  /**
   * Close the pooled connection and return it to pool
   * @throws SQLException if closing fails
   */
  public void close() throws SQLException;
  
  /**
   * Add listener for connection events
   * @param listener Event listener for connection lifecycle
   */
  public void addConnectionEventListener(ConnectionEventListener listener);
  
  /**
   * Remove connection event listener
   * @param listener Event listener to remove
   */
  public void removeConnectionEventListener(ConnectionEventListener listener);
  
  /**
   * Add listener for statement events
   * @param listener Statement event listener
   */
  public void addStatementEventListener(StatementEventListener listener);
  
  /**
   * Remove statement event listener
   * @param listener Statement event listener to remove
   */
  public void removeStatementEventListener(StatementEventListener listener);
  
  // XA transaction support
  /**
   * Get XA resource for distributed transactions
   * @return XAResource instance
   * @throws SQLException if XA resource retrieval fails
   */
  public XAResource getXAResource() throws SQLException;
}

/**
 * Extended pooled connection for global transactions
 * Maintains connection affinity for transaction consistency
 */
public class MariaDbPoolPinnedConnection extends MariaDbPoolConnection {
  // Provides connection pinning for global transaction contexts
  // Ensures same physical connection is used throughout transaction
}

// Basic pooled connection usage
MariaDbDataSource dataSource = new MariaDbDataSource();
dataSource.setUrl("jdbc:mariadb://localhost:3306/mydb");
dataSource.setUser("user");
dataSource.setPassword("password");

// Get pooled connection
PooledConnection pooledConn = dataSource.getPooledConnection();

// Add connection event listener
pooledConn.addConnectionEventListener(new ConnectionEventListener() {
    public void connectionClosed(ConnectionEvent event) {
        System.out.println("Pooled connection returned to pool");
    }
    
    public void connectionErrorOccurred(ConnectionEvent event) {
        System.err.println("Pooled connection error: " + event.getSQLException().getMessage());
        // Pool can invalidate this connection
    }
});

// Get and use the actual connection
Connection conn = pooledConn.getConnection();
try (Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
    // Use connection...
} finally {
    conn.close(); // Returns connection to pool, triggers connectionClosed event
}

// Close pooled connection when done
pooledConn.close();

/**
 * Internal connection pool implementation
 */
public class Pool {
  /**
   * Get connection from pool
   * @return Pooled connection
   * @throws SQLException if no connection available
   */
  public Connection getConnection() throws SQLException;
  
  /**
   * Get connection with timeout
   * @param timeout Maximum wait time in milliseconds
   * @return Pooled connection
   * @throws SQLException if timeout exceeded or error occurs
   */
  public Connection getConnection(long timeout) throws SQLException;
  
  /**
   * Close connection pool and all connections
   * @throws SQLException if closing fails
   */
  public void close() throws SQLException;
  
  /**
   * Get current pool statistics
   * @return Pool statistics object
   */
  public PoolStats getStats();
  
  // Internal pool management methods
  void addConnection();
  void removeConnection(Connection conn);
  void validateConnections();
}

/**
 * Pool management and factory
 */
public class Pools {
  /**
   * Get or create named pool
   * @param poolName Pool identifier
   * @param config Pool configuration
   * @return Pool instance
   */
  public static Pool getPool(String poolName, Configuration config);
  
  /**
   * Remove and close named pool
   * @param poolName Pool to remove
   */
  public static void remove(String poolName);
  
  /**
   * Close all pools
   */
  public static void close();
  
  /**
   * Get all active pool names
   * @return Set of pool names
   */
  public static Set<String> getPoolNames();
}

// Using pool directly (advanced usage)
Configuration config = Configuration.parse(
    "jdbc:mariadb://localhost:3306/mydb?pool=true&maxPoolSize=20&minPoolSize=5",
    new Properties()
);

Pool pool = Pools.getPool("MyApplicationPool", config);

// Get connection from pool
Connection conn = pool.getConnection();
try {
    // Use connection
    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
    stmt.setInt(1, 123);
    ResultSet rs = stmt.executeQuery();
    // Process results...
} finally {
    conn.close(); // Returns to pool
}

// Pool statistics
PoolStats stats = pool.getStats();
System.out.println("Active connections: " + stats.getActiveConnections());
System.out.println("Idle connections: " + stats.getIdleConnections());

// Close pool when application shuts down
pool.close();

/**
 * JMX monitoring interface for connection pools
 */
public interface PoolMBean {
  /**
   * Get number of active (in-use) connections
   * @return Active connection count
   */
  int getActiveConnections();
  
  /**
   * Get number of idle (available) connections
   * @return Idle connection count
   */
  int getIdleConnections();
  
  /**
   * Get total number of connections in pool
   * @return Total connection count
   */
  int getTotalConnections();
  
  /**
   * Get maximum pool size
   * @return Maximum connections allowed
   */
  int getMaxPoolSize();
  
  /**
   * Get minimum pool size
   * @return Minimum connections maintained
   */
  int getMinPoolSize();
  
  /**
   * Get number of connection requests served
   * @return Total requests served
   */
  long getConnectionsServed();
  
  /**
   * Get number of failed connection attempts
   * @return Failed connection count
   */
  long getConnectionFailures();
  
  /**
   * Get average connection acquisition time
   * @return Average time in milliseconds
   */
  double getAverageAcquisitionTime();
  
  /**
   * Get pool creation timestamp
   * @return Pool creation time
   */
  long getPoolCreationTime();
  
  /**
   * Force pool validation (check all idle connections)
   */
  void validatePool();
  
  /**
   * Reset pool statistics
   */
  void resetStatistics();
}

// Enable JMX monitoring for connection pool
String jmxUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +
    "registerJmxPool=true&" +
    "poolName=ProductionPool&" +
    "maxPoolSize=25&" +
    "minPoolSize=5";

MariaDbPoolDataSource poolDataSource = new MariaDbPoolDataSource();
poolDataSource.setUrl(jmxUrl);
poolDataSource.setUser("user");
poolDataSource.setPassword("password");

// Pool will be registered as JMX MBean at:
// org.mariadb.jdbc.pool:type=Pool,name=ProductionPool

// Access via JMX programmatically
MBeanServer server = ManagementFactory.getPlatformMBeanServer();
ObjectName poolName = new ObjectName("org.mariadb.jdbc.pool:type=Pool,name=ProductionPool");

// Get pool statistics
Integer activeConnections = (Integer) server.getAttribute(poolName, "ActiveConnections");
Integer idleConnections = (Integer) server.getAttribute(poolName, "IdleConnections");
Long connectionsServed = (Long) server.getAttribute(poolName, "ConnectionsServed");

System.out.println("Active: " + activeConnections);
System.out.println("Idle: " + idleConnections);
System.out.println("Total served: " + connectionsServed);

// Invoke pool operations
server.invoke(poolName, "validatePool", null, null);
server.invoke(poolName, "resetStatistics", null, null);

// Enable connection pooling with basic settings
String basicPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +                    // Enable pooling
    "maxPoolSize=20&" +               // Maximum 20 connections
    "minPoolSize=5";                  // Minimum 5 connections

// Pool with connection validation
String validatedPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +
    "maxPoolSize=15&" +
    "minPoolSize=3&" +
    "maxIdleTime=600&" +              // Close idle connections after 10 minutes
    "poolValidMinDelay=1000";         // Minimum 1 second between validations

// Production-ready pool configuration
String productionPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    // Pool sizing
    "pool=true&" +
    "maxPoolSize=50&" +               // High concurrency support
    "minPoolSize=10&" +               // Always maintain 10 connections
    
    // Connection lifecycle
    "maxIdleTime=300&" +              // 5 minute idle timeout
    "poolValidMinDelay=5000&" +       // Validate every 5 seconds minimum
    "useResetConnection=true&" +      // Use RESET CONNECTION for cleanup
    
    // Monitoring and naming
    "registerJmxPool=true&" +         // Enable JMX monitoring
    "poolName=ProductionAppPool&" +   // Named pool for identification
    
    // Connection optimization
    "connectTimeout=5000&" +          // 5 second connect timeout
    "socketTimeout=30000&" +          // 30 second socket timeout
    "tcpKeepAlive=true&" +            // Keep connections alive
    
    // Performance tuning
    "cachePrepStmts=true&" +          // Cache prepared statements
    "prepStmtCacheSize=250&" +        // Statement cache size
    "useServerPrepStmts=true&" +      // Use server-side prepared statements
    "useCompression=true";            // Enable protocol compression

// Proper pool lifecycle management
public class DatabaseManager {
    private MariaDbPoolDataSource poolDataSource;
    private final String poolUrl;
    
    public DatabaseManager() {
        this.poolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
            "pool=true&maxPoolSize=20&minPoolSize=5&registerJmxPool=true&poolName=AppPool";
    }
    
    public void initialize() throws SQLException {
        poolDataSource = new MariaDbPoolDataSource();
        poolDataSource.setUrl(poolUrl);
        poolDataSource.setUser(System.getenv("DB_USER"));
        poolDataSource.setPassword(System.getenv("DB_PASSWORD"));
        
        // Test pool connectivity
        try (Connection testConn = poolDataSource.getConnection()) {
            try (Statement stmt = testConn.createStatement()) {
                stmt.executeQuery("SELECT 1");
            }
        }
        
        System.out.println("Database pool initialized successfully");
    }
    
    public Connection getConnection() throws SQLException {
        if (poolDataSource == null) {
            throw new SQLException("Pool not initialized");
        }
        return poolDataSource.getConnection();
    }
    
    public void shutdown() {
        if (poolDataSource != null) {
            try {
                poolDataSource.close();
                System.out.println("Database pool closed");
            } catch (SQLException e) {
                System.err.println("Error closing pool: " + e.getMessage());
            }
        }
    }
    
    // JVM shutdown hook
    static {
        Runtime.getRuntime().addShutdownHook(new Thread(() -> {
            // Close all pools on JVM shutdown
            Pools.close();
        }));
    }
}

// Usage in application
DatabaseManager dbManager = new DatabaseManager();
dbManager.initialize();

// Use connections throughout application
try (Connection conn = dbManager.getConnection()) {
    // Database operations
}

// Shutdown gracefully
dbManager.shutdown();

// Replication with connection pooling
String replicationPoolUrl = "jdbc:mariadb:replication://primary:3306,replica1:3306,replica2:3306/mydb?" +
    // High availability
    "retriesAllDown=3&" +
    "transactionReplay=true&" +
    
    // Connection pooling
    "pool=true&" +
    "maxPoolSize=30&" +               // Higher pool size for HA
    "minPoolSize=10&" +
    "maxIdleTime=300&" +
    
    // Pool monitoring
    "registerJmxPool=true&" +
    "poolName=ReplicationPool&" +
    
    // Connection optimization for HA
    "connectTimeout=3000&" +          // Faster failover detection
    "socketTimeout=15000";            // Shorter socket timeout

// Load balancing with pooling
String loadBalancePoolUrl = "jdbc:mariadb:loadbalance://host1:3306,host2:3306,host3:3306/mydb?" +
    "pool=true&" +
    "maxPoolSize=40&" +               // Distribute across multiple hosts
    "minPoolSize=12&" +               // 4 connections per host minimum
    "registerJmxPool=true&" +
    "poolName=LoadBalancePool";

// Health check implementation for pooled connections
public class PoolHealthMonitor {
    private final PoolMBean poolMBean;
    private final ScheduledExecutorService scheduler;
    
    public PoolHealthMonitor(String poolName) throws Exception {
        MBeanServer server = ManagementFactory.getPlatformMBeanServer();
        ObjectName objectName = new ObjectName("org.mariadb.jdbc.pool:type=Pool,name=" + poolName);
        this.poolMBean = JMX.newMBeanProxy(server, objectName, PoolMBean.class);
        this.scheduler = Executors.newScheduledThreadPool(1);
    }
    
    public void startMonitoring() {
        scheduler.scheduleAtFixedRate(this::checkPoolHealth, 0, 30, TimeUnit.SECONDS);
    }
    
    private void checkPoolHealth() {
        try {
            int active = poolMBean.getActiveConnections();
            int idle = poolMBean.getIdleConnections();
            int total = poolMBean.getTotalConnections();
            int maxSize = poolMBean.getMaxPoolSize();
            long failures = poolMBean.getConnectionFailures();
            
            System.out.printf("Pool Health - Active: %d, Idle: %d, Total: %d/%d, Failures: %d%n",
                active, idle, total, maxSize, failures);
            
            // Alert if pool is near capacity
            if (total > maxSize * 0.9) {
                System.err.println("WARNING: Pool near capacity!");
            }
            
            // Alert if too many failures
            if (failures > 10) {
                System.err.println("WARNING: High connection failure rate!");
                poolMBean.resetStatistics(); // Reset after alerting
            }
            
            // Validate pool periodically
            if (System.currentTimeMillis() % 300000 < 30000) { // Every 5 minutes
                poolMBean.validatePool();
            }
            
        } catch (Exception e) {
            System.err.println("Error monitoring pool: " + e.getMessage());
        }
    }
    
    public void shutdown() {
        scheduler.shutdown();
    }
}

// Usage
PoolHealthMonitor monitor = new PoolHealthMonitor("ProductionPool");
monitor.startMonitoring();

// Shutdown monitor when application closes
Runtime.getRuntime().addShutdownHook(new Thread(monitor::shutdown));

// Performance tuning guidelines for different scenarios

// High throughput OLTP applications
String oltpPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +
    "maxPoolSize=100&" +              // Large pool for high concurrency
    "minPoolSize=25&" +               // Always ready connections
    "maxIdleTime=60&" +               // Quick idle timeout
    "poolValidMinDelay=500&" +        // Frequent validation
    "useServerPrepStmts=true&" +      // Server-side prepared statements
    "cachePrepStmts=true&" +
    "prepStmtCacheSize=500";          // Large statement cache

// Batch processing applications  
String batchPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +
    "maxPoolSize=10&" +               // Fewer connections for batch
    "minPoolSize=5&" +
    "maxIdleTime=1800&" +             // Longer idle timeout
    "useBulkStmts=true&" +            // Bulk operations
    "useCompression=true&" +          // Compression for large data
    "socketTimeout=120000";           // Longer socket timeout

// Web application with variable load
String webPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&" +
    "maxPoolSize=30&" +               // Handle peak load
    "minPoolSize=5&" +                // Low baseline
    "maxIdleTime=300&" +              // 5 minute idle timeout
    "poolValidMinDelay=10000&" +      // Infrequent validation
    "registerJmxPool=true&" +         // Monitor web app pools
    "poolName=WebAppPool";

// Pool sizing formula considerations:
// maxPoolSize = (available_memory_for_connections / memory_per_connection) 
//               * utilization_factor
// 
// Typical sizing:
// - CPU-bound: pool_size ≈ CPU_cores + 1
// - I/O-bound: pool_size ≈ CPU_cores * 2 to 4
// - Mixed workload: Start with CPU_cores * 2, tune based on monitoring

// Example sizing for different server types
String smallServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&maxPoolSize=10&minPoolSize=2";  // 2-4 CPU cores

String mediumServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&maxPoolSize=25&minPoolSize=5";  // 8-16 CPU cores

String largeServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
    "pool=true&maxPoolSize=50&minPoolSize=10"; // 16+ CPU cores

// Robust error handling with connection pools
public class RobustDatabaseAccess {
    private final MariaDbPoolDataSource poolDataSource;
    private final int maxRetries = 3;
    
    public <T> T executeWithRetry(DatabaseOperation<T> operation) throws SQLException {
        SQLException lastException = null;
        
        for (int attempt = 1; attempt <= maxRetries; attempt++) {
            try (Connection conn = poolDataSource.getConnection()) {
                return operation.execute(conn);
                
            } catch (SQLException e) {
                lastException = e;
                
                // Don't retry for certain error types
                if (isNonRetryableError(e)) {
                    throw e;
                }
                
                // Log retry attempt
                System.err.printf("Database operation failed (attempt %d/%d): %s%n",
                    attempt, maxRetries, e.getMessage());
                
                // Wait before retry (exponential backoff)
                if (attempt < maxRetries) {
                    try {
                        Thread.sleep(1000 * attempt);
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new SQLException("Interrupted during retry", ie);
                    }
                }
            }
        }
        
        throw new SQLException("Max retries exceeded", lastException);
    }
    
    private boolean isNonRetryableError(SQLException e) {
        // Don't retry for syntax errors, constraint violations, etc.
        String sqlState = e.getSQLState();
        return sqlState != null && (
            sqlState.startsWith("42") ||  // Syntax error
            sqlState.startsWith("23")     // Constraint violation
        );
    }
    
    @FunctionalInterface
    public interface DatabaseOperation<T> {
        T execute(Connection conn) throws SQLException;
    }
}

// Usage
RobustDatabaseAccess db = new RobustDatabaseAccess(poolDataSource);

// Automatically retries on connection failures
List<User> users = db.executeWithRetry(conn -> {
    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE active = 1");
    ResultSet rs = stmt.executeQuery();
    return mapResultsToUsers(rs);
});