Connection Pooling

val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://db.example.com:5432/pesanan"
    username = "appuser"
    password = System.getenv("DB_PASSWORD")
    maximumPoolSize = 10
    minimumIdle = 5
    idleTimeout = 60_000
    connectionTimeout = 30_000
    maxLifetime = 1_800_000
    poolName = "AppPool"
}
val ds = HikariDataSource(config)

ds.connection.use { conn ->
    conn.createStatement().use { stmt ->
        stmt.executeQuery("SELECT 1").use { rs ->
            rs.next(); println(rs.getInt(1))
        }
    }
}

// connection_pooling.kt
// Mendemonstrasikan connection pooling: HikariCP config, pool size, idle
// timeout, dan connection borrowing. Memakai SQLite via JDBC dengan
// simulasi pool ringkas (mirip HikariCP) untuk demo tanpa HikariCP dependency.

import java.sql.Connection
import java.sql.DriverManager
import java.util.concurrent.atomic.AtomicInteger

// Mock anotasi HikariCP config
data class HikariConfig(
    val jdbcUrl: String,
    val username: String? = null,
    val password: String? = null,
    val maximumPoolSize: Int = 10,
    val minimumIdle: Int = 5,
    val idleTimeoutMs: Long = 60_000,
    val connectionTimeoutMs: Long = 30_000,
    val maxLifetimeMs: Long = 1_800_000,
    val poolName: String = "HikariPool-1"
)

// Connection pool sederhana: menyimulasikan HikariPool
class SimpleConnectionPool(private val config: HikariConfig) : AutoCloseable {
    private val idle = ArrayDeque<Connection>()
    private val active = mutableSetOf<Connection>()
    private val created = AtomicInteger(0)
    private val keeper: Connection = DriverManager.getConnection(config.jdbcUrl)

    @Synchronized
    fun borrow(timeoutMs: Long = config.connectionTimeoutMs): Connection {
        val startMs = System.currentTimeMillis()
        while (true) {
            if (idle.isNotEmpty()) {
                val conn = idle.removeFirst()
                active.add(conn)
                return conn
            }
            if (created.get() < config.maximumPoolSize) {
                val conn = DriverManager.getConnection(config.jdbcUrl)
                created.incrementAndGet()
                active.add(conn)
                return conn
            }
            if (System.currentTimeMillis() - startMs > timeoutMs) {
                throw IllegalStateException("Pool habis: ${config.maximumPoolSize} koneksi aktif")
            }
            Thread.sleep(10)
        }
    }

    @Synchronized
    fun release(conn: Connection) {
        if (!active.remove(conn)) return
        if (idle.size < config.minimumIdle) {
            idle.addLast(conn)
        } else {
            conn.close()
            created.decrementAndGet()
        }
    }

    fun stats(): String = "active=${active.size}, idle=${idle.size}, totalCreated=${created.get()}"
    override fun close() {
        idle.forEach { it.takeIf { c -> !c.isClosed }?.close() }
        active.forEach { it.takeIf { c -> !c.isClosed }?.close() }
        keeper.close()
    }
}

fun main() {
    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite:file::memory:?cache=shared"

    println("=== HikariCP config (typical production) ===")
    val config = HikariConfig(
        jdbcUrl = "jdbc:postgresql://db.example.com:5432/pesanan",
        username = "appuser",
        password = "PASS",
        maximumPoolSize = 10,
        minimumIdle = 5,
        idleTimeoutMs = 60_000,
        connectionTimeoutMs = 30_000,
        maxLifetimeMs = 1_800_000
    )
    println("jdbcUrl: ${config.jdbcUrl}")
    println("maxPoolSize: ${config.maximumPoolSize}")
    println("minIdle: ${config.minimumIdle}")
    println("idleTimeout: ${config.idleTimeoutMs}ms")
    println("connectionTimeout: ${config.connectionTimeoutMs}ms")
    println("maxLifetime: ${config.maxLifetimeMs}ms (30 menit, paksa refresh)")

    println("\n=== Demo pool dengan SQLite ===")
    val poolConfig = HikariConfig(
        jdbcUrl = url,
        maximumPoolSize = 5,
        minimumIdle = 2,
        poolName = "DemoPool"
    )
    val pool = SimpleConnectionPool(poolConfig)

    // Initialize schema (release kembali ke pool)
    val initConn = pool.borrow()
    initConn.createStatement().use { it.execute("CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, msg TEXT)") }
    pool.release(initConn)

    println("\n=== Borrow pertama (pool baru saja dipakai init) ===")
    val c1 = pool.borrow()
    println("Borrowed 1 -> ${pool.stats()}")

    println("\n=== Borrow kedua dan ketiga ===")
    val c2 = pool.borrow()
    val c3 = pool.borrow()
    println("Borrowed 3 -> ${pool.stats()}")

    println("\n=== Borrow keempat dan kelima (mencapai batas max) ===")
    val c4 = pool.borrow()
    val c5 = pool.borrow()
    println("Borrowed 5 -> ${pool.stats()}")

    println("\n=== Borrow keenam (akan menunggu atau timeout) ===")
    try {
        pool.borrow(timeoutMs = 100)
    } catch (e: IllegalStateException) {
        println("Timeout: ${e.message}")
    }

    println("\n=== Release c1 -> kembali ke idle ===")
    pool.release(c1)
    println("Released 1 -> ${pool.stats()}")

    println("\n=== Borrow keenam lagi (langsung ambil dari idle) ===")
    val c6 = pool.borrow()
    println("Borrowed 6 -> ${pool.stats()}")

    println("\n=== Release semua ===")
    pool.release(c2); pool.release(c3); pool.release(c4); pool.release(c5); pool.release(c6)
    println("Released all -> ${pool.stats()}")

    println("\n=== Pool sizing rules ===")
    println("| Parameter          | Default  | Saran                                       |")
    println("|--------------------|----------|---------------------------------------------|")
    println("| maximumPoolSize    | 10       | (core_count * 2) + disk_spindle_count      |")
    println("| minimumIdle        | = max    | Sama dengan max untuk produksi               |")
    println("| idleTimeoutMs      | 60000    | 10 menit (lebih conservative)              |")
    println("| connectionTimeoutMs| 30000    | 30 detik; turunkan bila SLA ketat           |")
    println("| maxLifetimeMs      | 1800000  | 30 menit; lebih kecil dari DB timeout       |")

    println("\n=== Kenapa pooling? ===")
    println("- Tanpa pool: setiap DriverManager.getConnection() buka koneksi TCP + auth = 50-200ms")
    println("- Dengan pool: borrow() = ambil dari list = < 1ms")
    println("- Pool menjaga koneksi tetap aktif (lewat keepAlive)")
    println("- Pool membatasi total koneksi agar DB tidak kewalahan")

    pool.close()
}

=== HikariCP config (typical production) ===
jdbcUrl: jdbc:postgresql://db.example.com:5432/pesanan
maxPoolSize: 10
minIdle: 5
idleTimeout: 60000ms
connectionTimeout: 30000ms
maxLifetime: 1800000ms (30 menit, paksa refresh)

=== Demo pool dengan SQLite ===

=== Borrow pertama (pool baru saja dipakai init) ===
Borrowed 1 -> active=1, idle=0, totalCreated=1

=== Borrow kedua dan ketiga ===
Borrowed 3 -> active=3, idle=0, totalCreated=3

=== Borrow keempat dan kelima (mencapai batas max) ===
Borrowed 5 -> active=5, idle=0, totalCreated=5

=== Borrow keenam (akan menunggu atau timeout) ===
Timeout: Pool habis: 5 koneksi aktif

=== Release c1 -> kembali ke idle ===
Released 1 -> active=4, idle=1, totalCreated=5

=== Borrow keenam lagi (langsung ambil dari idle) ===
Borrowed 6 -> active=5, idle=0, totalCreated=5

=== Release semua ===
Released all -> active=0, idle=2, totalCreated=2

=== Pool sizing rules ===
| Parameter          | Default  | Saran                                       |
|--------------------|----------|---------------------------------------------|
| maximumPoolSize    | 10       | (core_count * 2) + disk_spindle_count      |
| minimumIdle        | = max    | Sama dengan max untuk produksi               |
| idleTimeoutMs      | 60000    | 10 menit (lebih conservative)              |
| connectionTimeoutMs| 30000    | 30 detik; turunkan bila SLA ketat           |
| maxLifetimeMs      | 1800000  | 30 menit; lebih kecil dari DB timeout       |

=== Kenapa pooling? ===
- Tanpa pool: setiap DriverManager.getConnection() buka koneksi TCP + auth = 50-200ms
- Dengan pool: borrow() = ambil dari list = < 1ms
- Pool menjaga koneksi tetap aktif (lewat keepAlive)
- Pool membatasi total koneksi agar DB tidak kewalahan

ds.connection.use { conn ->
    conn.prepareStatement("SELECT * FROM pengguna WHERE id = ?").use { ps ->
        ps.setInt(1, 42)
        ps.executeQuery().use { rs ->
            if (rs.next()) println(rs.getString("nama"))
        }
    }
}  // Koneksi kembali ke pool di sini, TIDAK di-close secara fisik