jOOQ fetchResultSet not closing connection with Kotlin

Question

I'm using Kotlin with HikariCP and jOOQ to query my database. I've come to realize that this code works as expected, fetching the rows and closing the connection afterwards:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<Country> {
        DSL.using(datasource, SQLDialect.POSTGRES_10)
            .use { ctx ->
                return ctx.select(...)
                    .from(...)
                    .orderBy(...)
                    .fetch(Country.mapper) // val mapper: (CountriesRecord) -> Country = {...}
            }
    }
}

This mapper was no longer suitable after adding a many-to-many relationship to Country, so I wanted to get a ResultSet and with SimpleFlatMapper produce objects with this relationship (as explained on this link), but with fetchResultSet() the connection is never closed and the pool dries out:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<Country> {
        DSL.using(datasource, SQLDialect.POSTGRES_10)
            .use { ctx ->
                val rs = ctx.select(...)
                    .from(...)
                    .orderBy(...)
                    .fetchResultSet()
                return Country.mapper.stream(rs).toList() // val mapper = JdbcMapperFactory.newInstance()...
            }
    }
}

I've seen that AbstractResultQuery#fetchResultSet() is delegating to a fetchLazy() method, so not sure if it has something to do with that.

If I get the connection myself instead of delegating it to the DSLContext, then it works:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<Country> {
        val conn = datasource.connection
        conn.use {
            val rs = DSL.using(it, SQLDialect.POSTGRES_10)
                .select(...)
                .from(...)
                .orderBy(...)
                .fetchResultSet()
            return Country.mapper.stream(rs).toList() // val mapper = JdbcMapperFactory.newInstance()...
        }
    }
}

Is this last approach the one I should be using?

Answer 1

The code that produces a resource is always responsible for closing it. And that's you. And the resource is the ResultSet. Your code should look like this instead:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<Country> {
        val ctx = DSL.using(datasource, SQLDialect.POSTGRES_10)

        return
        ctx.select(...)
           .from(...)
           .orderBy(...)
           .fetchResultSet()
           .use {
                return Country.mapper.stream(it).toList()
            }
    }
}

About calling DSLContext.use

Notice, just like in your other question, I recommend you do not call use on jOOQ's DSLContext type, as you don't need it. In your case, DSLContext is not resourceful as you pass it a datasource

About calling ResultSet.use

Instead, you should call use on ResultSet, which guarantees that it is closed after consumption. In this example, I'm assuming that your call to toList() will eagerly consume the entire stream that wraps the result set.

The important thing to remember here is that you produce the resource by calling jOOQ's ResultQuery.fetchResultSet(), and even if you pass it to another library, that other library is not required to close it. But you are.