jdbc: when can i close what

currently i have jdbc code with the following basic stucture:

get Connection

(do the next 4 lines several times, never closing statement)
get statement
get result set
process result set
close result set

close connection

It occurred to me after writing this code that i need to close the statement.
1 what are the effects of not closing the statement.
2 will the following work, this si will closing the statement prevent me from processing the result set as normal?

get Connection

(do the next 5 lines several times)
get statement
get result set
close statement
process result set
close result set

close connection

Asked by: Kellan852 | Posted: 21-01-2022

Answer 1

The close behavior is specified in the JDBC Specification. Closing the Connection releases all JDBC resources associated with the connection and will implicitly close all Statements, ResultSets, etc. Closing the statement will close the ResultSets.

Example 2 is not guaranteed to work. The statement should be closed AFTER the ResultSet has been used. The spec says that you should get an SQLException if you try to use that ResultSet (some JDBC drivers don't follow the spec strictly--and MS is not biggest offender).

If you forget to close a ResultSet or Statement, the worse case that happens is you consume database and JVM resources for longer than necessary. On a resource constrained or high load system, this could cause memory/connection/resource errors or reduced performance.

Answered by: Julian935 | Posted: 22-02-2022

Answer 2

The answer depends on your JDBC driver unfortunately. What you wrote there might work.

However, the general rule is that you close your statement only when you are done with the corresponding resultset.

EDIT: I realize that you had a second question where you asked about the effects of not closing the statements/Resultsets and so on. The effects also depend on your JDBC driver but, it could lead to significant resource leaks.

Answered by: Carlos247 | Posted: 22-02-2022

Answer 3

If you're using Oracle and forget to close the statements you'll get

ORA-01000: maximum open cursors exceeded

after a while.

Answered by: Arthur482 | Posted: 22-02-2022

Similar questions

java - DB2 JDBC: "Same" query gives different results

I've been assigned to develop a small Java application to process some data from a DB2 database(used for logging business transactions), and I know a little about the internal settings of the datbase. Currently, I am struggling to find out why a query executed on my application(through JDBC) gives a different result set from a direct query executed on my Quest Central for DB2 client. String query = "SEL...

java - JDBC: Query results and add batch into another table

I have some mySQL tables: "books", "task" and "task_items". I'm building an REST API (Java) that allow users to create tasks (basically edit fields) with some books. When user creates a new task, I'm creating a new row in task table, getting the last ID, selecting some books and add all of it into task_items table. Something like this: Table books: ID TITLE AUTHOR 1 Book A John 2 Book B ...

Still can't find your answer? Check out these amazing Java communities for help...

Java Reddit Community | Java Help Reddit Community | Dev.to Java Community | Java Discord | Java Programmers (Facebook) | Java developers (Facebook)