jOOQ fetch vs fetchResultSet and close connection in 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<String> {
        DSL.using(datasource, SQLDialect.POSTGRES_10)
            .use { ctx ->
                ctx.select(countries.CO_NAME)
                    .from(countries)
                    .orderBy(countries.CO_NAME)
                    .fetch()
                return emptyList()
            }
    }
}

whereas if I use fetchResultSet(), the connection is never closed and the pool dries out:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<String> {
        DSL.using(datasource, SQLDialect.POSTGRES_10)
            .use { ctx ->
                ctx.select(countries.CO_NAME)
                    .from(countries)
                    .orderBy(countries.CO_NAME)
                    .fetchResultSet()
                return emptyList()
            }
    }
}

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<String> {
        val conn = datasource.connection
        conn.use {
            DSL.using(it, SQLDialect.POSTGRES_10)
                .select(countries.CO_NAME)
                .from(countries)
                .orderBy(countries.CO_NAME)
                .fetchResultSet()
            return emptyList()
        }
    }
}

Is this last approach the one I should be using?

Answer 1

It works exactly as specified in the Javadoc:

This is the same as calling fetchLazy().resultSet() and will return a ResultSet wrapping the JDBC driver's ResultSet. Closing this ResultSet may close the producing Statement or PreparedStatement, depending on your setting for keepStatement(boolean).

The point of this method is that you want to consume a JDBC result set rather than having jOOQ consume it for you. So, you're responsible for the resource management.

Given your example code, you should definitely not call this method but call fetch() instead. For example:

class CountriesService(private val datasource: DataSource) {

    private val countries = Countries()

    fun getCountries(): List<String> {
        return
        DSL.using(datasource, SQLDialect.POSTGRES_10)
           .select(countries.CO_NAME)
           .from(countries)
           .orderBy(countries.CO_NAME)
           .fetch(countries.CO_NAME)
    }
}

Notice, you don't need to call that use() method on your DSLContext. While DSLContext extends AutoCloseable, this is only needed when your DSLContext manages the underlying JDBC connection (i.e. when it creates it). In your case, when you pass a data source to DSL.using(), then you don't have to close the DSLContext.