Why are Queries in JDBI closeable?

Question

I have a Java application which uses JDBI for my database code. I have many DAOs which use roughly the following construction for queries:

return handle.createQuery(sql)
             .bind("variable", variable)
             .mapToBean(Bean.class)
             .list();

Where handle is a JDBI Handle object. I am closing the handle elsewhere in the application, at the end of the transaction.

I scanned my code with SonarQube and it is complaining that I am opening JDBI Query objects, which implement AutoCloseable, without closing them. SonarQube suggests closing them in a try-with-resources, like so:

try(Query query = handle.createQuery(sql)){
    return query.bind("variable", variable)
                .mapToBean(Bean.class)
                .list();
}

I am concerned that this may close the underlying handle or other resources I need for other queries happening inside the transaction. I am also skeptical that they need to be closed. Why does JDBI make these Query objects closeable? I have not found anything in the documentation indicating what resources they open. Is SonarQube correct or can this be safely ignored?

Thanks.

I dont know if this is help but here is the source of where its calling clean: https://github.com/jdbi/jdbi/blob/16857abec877f7e79ecb9c89f3fc25942babcb65/core/src/main/java/org/jdbi/v3/core/statement/StatementContext.java#L553 As long as nothing is being added to `cleanable` then it should be fine to have it in the `try-with-resource` — locus2k, Sep 28 '20 at 17:21
In case it helps: Handles can be [used in callbacks](https://jdbi.org/#_handle), in which case they are automatically closed when the callback completes. This is how I typically handle them (pun regretted). — andrewJames, Sep 28 '20 at 17:41
Yes, I'm aware of the callback method--it's very nice--but unfortunately due to the way the application needs to be structured to work with a framework I'm using, that method isn't really feasible. — Peter, Sep 28 '20 at 17:45

Nathan Hughes · Accepted Answer · 2020-09-28T18:25:29.263

A JDBI query is a wrapper around a JDBC Statement and a ResultSet, where the ResultSet in turn wraps a database cursor. Closing the query closes the Statement and the ResultSet, which de-allocates the cursor, freeing up database resources. There is a limit on how many cursors a database can have open at a time. Closing the query won’t close the database connection.

I would be surprised if Sonarqube knew anything specific about JDBI, it seems likely it is just following a rule saying anything AutoCloseable should get closed. Even so, that is not a bad rule to follow for most things. I would listen to Sonarqube on this one and close the queries.

Closing the connection should clean up all the related cursors for you, but it would be better to let them go as soon as you are done with them, because that lets the database server work more efficiently.

Why are Queries in JDBI closeable?

1 Answers1