When to close Connection, Statement, PreparedStatement and ResultSet in JDBC

by

Personally I’d use a pool as this will take care of all of the resource management for you. If your connection requirements change then you can easily modify the pool configuration. With a pool in place you can open/close connections and prepared statements according to best-practice and leave the resource management to the pool.

Typically, when using a pool:

  • closing a connection will actually just return it to the pool
  • the act of preparing a statement will either retrieve a previously prepared statement from the Connection’s statement cache, or if one is not available, create a new statement and cache it for later use.
  • the act of closing a PreparedStatement will actually just return it to the connection’s statement cache.

Furthermore – depending on the pool implementation – it may be able to notify you when there are resource leaks making it easier to identify these sorts of problems in your code.

Take a look at the source of an example implementation like DBCP – it’s quite interesting to see how they work.

More Related Contents:

  1. Is it safe to use a static java.sql.Connection instance in a multithreaded system?
  2. How do I get the size of a java.sql.ResultSet?
  3. java.sql.SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)
  4. Getting java.sql.SQLException: Operation not allowed after ResultSet closed
  5. JDBC driver throws “ResultSet Closed” exception on empty ResultSet
  6. JDBC ResultSet get columns with table alias
  7. Execute anonymous pl/sql block and get resultset in java
  8. ResultSet: Retrieving column values by index versus retrieving by label
  9. ResultSet.getString(1) throws java.sql.SQLException: Invalid operation at current cursor position
  10. How to get row count using ResultSet in Java?
  11. How to get the number of columns from a JDBC ResultSet?
  12. How do I get the row count in JDBC?
  13. Convert a Result Set from SQL Array to Array of Strings
  14. MySQL query to get Records in Result set
  15. having an exception when saving oracle type object in some entity
  16. How to connect with Web Api Spotify (in Java)?
  17. PreparedStatement IN clause alternatives?
  18. Fastest way to write huge data in text file Java
  19. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  20. Reusing a PreparedStatement multiple times
  21. Java JDBC MySQL exception: “Operation not allowed after ResultSet closed”
  22. Is asynchronous jdbc call possible?
  23. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  24. JPA or JDBC, how are they different?
  25. How to write / update Oracle blob in a reliable way?
  26. NamedParameterJdbcTemplate vs JdbcTemplate
  27. Access denied for user ‘root’@’localhost’
  28. How to use prepared statement for select query in Java?
  29. How to connect to a remote MySQL database with Java?
  30. How to get jdbc connection from hibernate session? [duplicate]

Leave a Comment