Closing Database Connections in Java

Closing database connections in Java

When you are done with using your Connection, you need to explicitly close it by calling its close() method in order to release any other database resources (cursors, handles, etc.) the connection may be holding on to.

Actually, the safe pattern in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them. Something like this:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
// Do stuff
...

} catch (SQLException ex) {
// Exception handling stuff
...
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) { /* Ignored */}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) { /* Ignored */}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) { /* Ignored */}
}
}

The finally block can be slightly improved into (to avoid the null check):

} finally {
try { rs.close(); } catch (Exception e) { /* Ignored */ }
try { ps.close(); } catch (Exception e) { /* Ignored */ }
try { conn.close(); } catch (Exception e) { /* Ignored */ }
}

But, still, this is extremely verbose so you generally end up using an helper class to close the objects in null-safe helper methods and the finally block becomes something like this:

} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(conn);
}

And, actually, the Apache Commons DbUtils has a DbUtils class which is precisely doing that, so there isn't any need to write your own.

How can I close the database connection in this approach?

You can change your code to something like:

public class DatabaseConnection {

static final String URL = "jdbc:mysql://localhost:3306/rubyrail?useSSL=false";
static final String Username = "root";
static final String Password = "root";

private static Connection createConnection() {
return DriverManager.getConnection(URL, Username, Password);
}

public static void create() {
try (Connection connection = createConnection()) {
// do something with the connection
} catch (SQLException e) {
e.printStackTrace();
// or something else to handle the error
}
}

// same for the rest of your methods

public static void main (String args[]) {
int choice= 0;
while (choice < 6) {
System.out.println("\n1. Create");
System.out.println("\n2. Read");
System.out.println("\n3. Update");
System.out.println("\n4. Delete");
System.out.println("\n5. Close");

choice = scanner.nextInt();
switch(choice) {
case 1:
create();
break;
// other cases
}
}
}
}

This will create a connection for each method invocation, which may be less efficient, but will simplify resource management. If performance is of real importance, you should consider using a data source that provides connection pooling (eg HikariCP, Apache DBCP, etc). Using a connection pool will allow reuse of connections without your code having to worry about it beyond setting up the data source configuration.

Alternatively, create the connection once in your main, and pass it to each method you want to call:

public static void create(Connection connection) {
try {
// do something with connection
} catch (SQLException e) {
e.printStackTrace();
// or something else to handle the error
}
}
public static void main (String args[]) {
try (Connection connection = createConnection()) {
int choice= 0;
while (choice < 6) {
System.out.println("\n1. Create");
System.out.println("\n2. Read");
System.out.println("\n3. Update");
System.out.println("\n4. Delete");
System.out.println("\n5. Close");

choice = scanner.nextInt();
switch(choice) {
case 1:
create(connection);
break;
// other cases
}
}
} catch (SQLException e) {
e.printStackTrace();
// or something else to handle the error
}
}

What is the suitable way to close the database connection in Java?

Those methods only close the ResultSet. You still have to close all Statement and Connection instances. I recommend doing so in a finally block. Something like,

Connection conn = null;
Statement stmt = null'
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.prepareStatement(sql);
stmt.setString(1, "Hello");
rs = stmt.executeQuery();
while (rs.next()) {
// ...
}
} catch (SQLException se) {
se.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

If you use my Close utility the finally block could be,

} finally {
Close.close(rs, stmt, conn);
}

Closing MySQL Database Connections in java

The code for closing connection should be written in finally block, so that if there is some error while performing database operations, then also the connection gets closed. You can do as follows :

public ArrayList categoriesQuery() {
//your code
Connection connect=null;
try {
connect = databaseConnection.connection();
if(connect!=null){
//your code for database operations
}
} catch (SQLException ex) {
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
finally{
if(connect!=null){
connect.close();
}
}
return categories;
}

JDBC Connection close

You need to close the connection after you have finished using it. There are many ways to deal with that, but here's what I suggest you do:

public class DBConnection implements AutoCloseable {
private String url = ...
private Connection con;
private Statement statement;

public DBConnection () throws SQLException {
try {
con = DriverManager.getConnection(url);
statement = con.createStatement();
} finally {
// Avoid leak if an exception was thrown in createStatement
if (statement == null) {
con.close();
}
}
}

public void addSubject(String subject) throws SQLException {
statement.executeUpdate("INSERT INTO `Subject` VALUES ('" +
subject + "')" );
}

public void close() throws SQLException {
con.close();
}
}

Then use it like this:

try (DBConnection connection = new DBConnection()) {
connection.addSubject("English");
}

Explanation / commentary:

  1. The close() method is the way that the application tells the DBConnection class "I have finished".
  2. Declaring DBConnection as `AutoCloaseable means that we can use try-with-resources to manage the closure ... which is simpler and more robust.
  3. The instance variables are private as per good OO design principles.
  4. The class name is corrected per the Java conventions.
  5. We still needed to be careful to ensure that we don't leak a connection if an exception occurs in the constructor itself.
  6. We allow SQLException to propagate to the caller. Those exceptions cannot be handled properly in DBConnection class itself.

The other approach is to do away with the DBConnection class entirely, and have the calling code take care of the connection object and the statements for itself. Certainly, in this small example the DBConnection abstraction adds minimal value.

Closing Database Connections in Java Swing

If you are using Java 7 and above, I would recommend using try with resources.

The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

In your case:

        try (PreparedStatement pst = conn.prepareStatement(sql))//use try with resources
{
pst.setString(1, loginField.getText());
pst.setString(2, passwordField.getText());
ResultSet rs = pst.executeQuery();
int countUsr = 0;
while(rs.next()){
countUsr++;
}
if(countUsr == 1){
loginFrame.dispose();
AdminFrame adminFrame = new AdminFrame();
adminFrame.setVisible(true);
}else if(countUsr > 1){
JOptionPane.showMessageDialog(null, "ERR");
}else{
JOptionPane.showMessageDialog(null, "ERR");
passwordField.setText("");
}
//removed rst closing, no need to close if your PreparedStatement is being closed.
//No need to explicitly close our PreparedStatement since we are using try with resources
}catch(Exception e){
JOptionPane.showMessageDialog(null, "ERR: "+e.getMessage());
}
}

You should also note that you don't need to close your ResultSet if you are closing your PreparedStatement. (See this answer)

Closing database connection on JFrame close

You can do this.

JFrame frame = new JFrame();
frame.addWindowListener(new WindowAdapter()
{
@Override
public void windowClosing(WindowEvent e)
{
super.windowClosing(e);
// Do your disconnect from the DB here.
}
});

Am I closing the DB connection correctly? JDBC - DBCP

You asked:

Will closing the preparedStatement also close and return the connection to the connection pool?

Start with the documentation:

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

Calling the method close on a Statement object that is already closed has no effect.

Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

No mention of closing the connection.

Try intuition: Do we ever run more than one statement in SQL? Yes, obviously. So logically the connection needs to survive across multiple statements to be useful.

Lastly: Try it yourself, an empirical test. Call Connection#isOpen after calling Statement#close.

➥ No, closing the statement does not close the connection.

For the simplest code, learn to use try-with-resources syntax to auto-close your database resources such as result set, statement, and connection. You’ll find many examples of such code on this site, including some written by me.

As for connection pools, yes, calling close on a connection retrieved from a pool causes the connection object to be be returned to the pool. The pool may choose to re-use the connection, or the pool may choose to close the connection. (Not our concern.)

The only point to a connection pool is speed. If opening a connection to the database takes a significant amount of time, we can save that time by re-using existing connection. Generating and re-using connections is the job of a connection pool.

If a connection pool is showing the slowest results in your testing, then here is something seriously wrong with either your pool or your tests. You did not reveal to us your tests, so we cannot help there. Note: As Marmite Bomber commented, be sure your tests do not include the time needed to establish the connection pool.

Frankly, I have found in my experience that opening a database connection does not take a significant amount of time. Furthermore, the details involved in properly implementing a connection pool are complex and treacherous as evidenced by the list of failed and abandoned connection pool implementation projects. That, combined with the inherent risks such as a transaction being left open on a retrieved connection, led me to avoiding the use of connection pools. I would posit that using a connection pool before collecting proof of an actual problem is a case of premature optimization.

I suggest using an implementation of the interface DataSource as a way to mask from the rest of your code whether you are using a pool and to hide which pool implementation you are currently using. Using DataSource gives you the flexibility to to change between using or not using a connection pool, and the flexibility to change between pools. Those changes become deployment choices, with no need to change your app programming.

Closing a database connection in Java

In my past experience(No document or anything), I would understand the "ResultSet" like a pointer in C. I bet it would cache some rows from database when you execute your query..

Therefore, if you close connection and then try to use resultset, all those cached resultset does not have proper information and getting next cache etc. As a result, it would throw out of memory exception..

Anyway proper way to use those in java..

Connection con...
try{
create connection
execute query
use your resultset completely..
}catch(...){
}finally{
close connection;
}

Hope it would help



Related Topics



Leave a reply



Submit