How to handle connection leaks in JDBC?

In this article, we will learn how to handle connection leaks in JDBC. Handling connection leaks in JDBC involves ensuring that database connections are properly closed after they are no longer needed. The connection leaks occur when a connection is opened but not closed. Due to connection leaks, there is a chance of loss of data, so we need to close the database connection after the use of the database. We have different ways to handle the JDBC connection leaks namely using try-catch. It can handle database leaks. Another one is the finally block, it can be able to close the database connection whether we use it or not. And also, have other approaches also to handle JDBC connection leaks.

• By using try-catch statements in Java to close the database connections, statements and result sets automatically. These resources are closed while any exception is raised.
• By using finally block we can handle the connection leaks in JDBC. This means we need to write a connection close statement in the finally block.
• By using connection pooling means implementing connection pooling mechanisms provided by the JDBC. The Connection pooling manages a pool of pre-initialized database connections.
• Regularly monitoring connection usage means Monitoring our application’s connection usage to identify any leaks.

By following these approaches, we can be able to handle connection leaks in JDBC effectively.

Implementation to handle connection leaks in JDBC

Below, we have provided an example to handle connection leaks in JDBC.

• In this example, we have used try-catch and finally block in Java statement to handle connection leaks in JDBC.
• For this, we have used book table and for understanding this code first we need to connection the database.
• Then creates SQL query to retrieve the data, then print the existing data in the book table.

Syntax:

The try-catch statement with finally block statement to handle connection leaks in JDBC

 try {
     Connection con = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
} 
catch (SQLException e) {
            e.printStackTrace();
}
finally {
     if (con != null) {
         con.close();
      }
}

Program to handle connection leaks in JDBC

Java

package geeksforgeeks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrieveDataExample {

    public static void main(String[] args) {
        
        Connection con = null;
        ResultSet resultSet = null;
        try {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
             con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");

            if (con != null) {
                
                // Retrieve all data
                String selectQuery = "SELECT * FROM book";
                PreparedStatement selectStatement = con.prepareStatement(selectQuery);
                resultSet = selectStatement.executeQuery();

                System.out.println("The Available Data\n");

                while (resultSet.next()) {
                    int id = resultSet.getInt("id");

                    // Check for NULL values
                    String author_name = resultSet.getString("author");
                    if (resultSet.wasNull()) {
                        author_name = "N/A"; // Or any default value you prefer
                    }

                    String book_name = resultSet.getString("name");
                    if (resultSet.wasNull()) {
                        book_name = "N/A"; // Or any default value you prefer
                    }

                    String book_price = resultSet.getString("price");
                    if (resultSet.wasNull()) {
                        book_price = "N/A"; // Or any default value you prefer
                    }

                    System.out.println("ID: " + id + ", Author_Name: " + author_name + ", Book_Name: " + book_name + ", Book_Price " + book_price);
                }
            } else {
                System.out.println("Not Connected...");
            }

        } catch (SQLException e) {
            System.out.println("Exception is " + e.getMessage());
        }
        finally {
            // Close resources in reverse order
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Output:

Below we can refer the output of the available data:

Explanation of the above Program:

• First, we create connection with Database by using My-SQL connector and wrote this entire login between try-catch block statement to handle connection related leaks in JDBC in the form of exceptions.
• For connecting database, we need some properties namely localhost with MySQL properties i.e. Username and Password.
• Then, we write one SQL query for fetch all data from the book table from the database.
• Then hold that entire result in ResultSet object.
• After that by using while loop, we print the available data in the Book Table.
• After that, we print exception in the catch block.
• After this catch block, we defined the finally block also means when exception is handled or not but the finally block is executed.
• In finally block, we check if the connection is closed or not. If connection is null, we closed the database connection in the finally block.
• Finally, it prints the entire data in the table while no leaks in database connection.