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.

Answer 1

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.