In some scenarios we should setAutoCommit(false) before query, see here https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor and When does the PostgreSQL JDBC driver fetch rows after executing a query? .
But none of these topics mentioned how to do after query, when ResultSet and Statement is closed but Connection is not (may be recycled by ConnectionPool or DataSource).
I have these choices:
autoCommit = false for next query)autoCommit = trueWhich one is the best practice?
Even queries are executed in a transaction. If you started a transaction (which implicitly happened when you executed the query), then you should also end it. Generally, doing nothing would - with a well-behaved connection pool - result in a rollback when your connection is returned to the pool. However, it is best not the rely on such implicit behaviour, because not all connection pools or drivers will adhere to it. For example the Oracle JDBC driver will commit on connection close (or at least, it did so in the past, I'm not sure if it still does), and it might not be the correct behaviour for your program. Explicitly calling commit() or rollback() will clearly document the boundaries and expectations of your program.
Though committing or rolling back a transaction that only executed a query (and thus did not modify the database), will have the same end result, I would recommend using commit() rather than rollback(), to clearly indicate that the result was successful. For some databases, committing might be cheaper than rollback (or vice versa), but such systems usually have heuristics that will convert a commit to rollback (or vice versa, whatever is 'cheaper'), if the result would be equivalent.
You generally don't need to switch auto-commit mode when you're done. A well-behaved connection pool should do that for you (though not all do, or sometimes you need to explicitly configure this). Double check the behaviour and options of your connection pool to be sure.
If you want to continue using a connection yourself (without returning to the pool), then switching back to auto-commit mode is sufficient: calling setAutoCommit(true) with an active transaction will automatically commit that transaction.