[FIXED] How to handle a connection properly with jooq?

Issue

I am getting a lot of inactive sessions in the database which not only take up all the resources but also cause the database to crash occasionally requiring me to restart it.

I am using jooq with kotlin, and this is how I establish a connection.

@Component
class EstDBConnection(private val cfg: DatabaseConfig, private val jooqExecuteListener: PromJooqExecuteListener) {

    init {
        cfg.migrateFlyway()
    }

    fun <T> acquire(f: (DSLContext) -> T): T {
        return DSL.using(DriverManager.getConnection(cfg.url, cfg.username, cfg.password), SQLDialect.ORACLE10G).use {
            jooqExecuteListener.attach(it)
            f(it)
        }
    }
}

Solution

You're never closing the connections that you're creating. Please use a connection pool (e.g. HikariCP) to manage your connections. Unless your writing a simple batch script, or some proof of concept, you should never resort to using DriverManager.getConnection directly

Answered By - Lukas Eder

Answer Checked By - Katrina (PHPFixing Volunteer)