jOOQ fetch vs fetchResultSet并在Kotlin中关闭连接 [英] jOOQ fetch vs fetchResultSet and close connection in Kotlin

问题描述

我正在将Kotlin与HikariCP和jOOQ一起用于查询我的数据库.我已经意识到该代码可以按预期工作，然后获取行并随后关闭连接:

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()
            }
    }
}

而如果我使用fetchResultSet()，则连接永远不会关闭，并且池会变干:

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()
            }
    }
}

我已经看到AbstractResultQuery#fetchResultSet()委托给fetchLazy()方法，所以不确定是否与此有关.

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

如果我自己获得连接，而不是将其委托给DSLContext，那么它将起作用:

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?

推荐答案

它完全按照

这与调用fetchLazy().resultSet()相同，并且将返回ResultSet，其中包裹了JDBC驱动程序的ResultSet.关闭此ResultSet可能会关闭生成的Statement或PreparedStatement，具体取决于您对keepStatement(boolean)的设置.

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).

此方法的重点是您要使用JDBC结果集，而不是让jOOQ为您使用它.因此，您负责资源管理.

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.

给出示例代码，您绝对不应该调用此方法，而应调用fetch().例如:

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)
    }
}

注意，您不需要在DSLContext上调用该use()方法.当DSLContext扩展AutoCloseable时，仅当DSLContext管理基础JDBC连接(即创建它)时才需要.就您而言，将数据源传递到DSL.using()时，不必关闭DSLContext.

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.

这篇关于jOOQ fetch vs fetchResultSet并在Kotlin中关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！