How to use try-with-resources with JDBC?

Whenever, we instantiate and use certain objects/resources we should close them explicitly else there is a chance of Resource leak.

Generally, we used close resources using the finally block as −

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
//Registering the Driver
try {
   con = DriverManager.getConnection(mysqlUrl, "root", "password");
   stmt = con.createStatement();
} catch (SQLException e) {
   e.printStackTrace();
}   finally {
   try {
      rs.close();
      stmt.close();
      con.close();
   } catch(SQLException e) {
      e.printStackTrace();
   }
}

From JSE7 onwards the try-with-resources statement is introduced. In this we declare one or more resources in the try block and these will be closed automatically after the use. (at the end of the try block)

The resources we declare in the try block should extend the java.lang.AutoCloseable class.

In JDBC we can use java.sql.CallableStatement, Connection, PreparedStatement, Statement, ResultSet, and RowSet in try-with-resources statement.

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program demonstrates how usage of try-with-resources statement in JDBC −

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TryWithResources_Example {
   public static void main(String args[]) {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      System.out.println("Connection established......");
      //Registering the Driver
      try(Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      Statement stmt = con.createStatement(); ) {
         try(ResultSet rs = stmt.executeQuery("select * from MyPlayers");){
            //Retrieving the data
            while(rs.next()) {
               System.out.print(rs.getInt("ID")+", ");
               System.out.print(rs.getString("First_Name")+", ");
               System.out.print(rs.getString("Last_Name")+", ");
               System.out.print(rs.getDate("Date_Of_Birth")+", ");
               System.out.print(rs.getString("Place_Of_Birth")+", ");
               System.out.print(rs.getString("Country"));
               System.out.println();
            }
         } catch (SQLException e) {
            e.printStackTrace();
         }
      } catch (SQLException e) {
            e.printStackTrace();
      }
   }
}

Output

Connection established......
1, Shikhar, Dhawan, 1981-12-05, Delhi, India
2, Jonathan, Trott, 1981-04-22, CapeTown, SouthAfrica
3, Kumara, Sangakkara, 1977-10-27, Matale, Srilanka
4, Virat, Kohli, 1988-11-05, Mumbai, India
5, Rohit, Sharma, 1987-04-30, Nagpur, India
6, Ravindra, Jadeja, 1988-12-06, Nagpur, India
7, James, Anderson, 1982-06-30, Burnely, England
8, Ryan, McLaren, 1983-02-09, Kumberly, null

Smita Kapse

Updated on 30-Jul-2019 22:30:26

• Related Questions & Answers
• Try-with-resources in Kotlin
• How to declare multiple resources in a try-with-resources statement in Java 9?
• Effectively final variables in try-with-resources in Java 9?
• What are the improvements for try-with-resources in Java 9?
• How to establish connection with JDBC?
• How to use Try/catch blocks in C#?
• How to process SQL statements with JDBC explain with an example?
• How to escape backslashes in MySQL with JDBC?
• How to use Boto3 to add tags in AWS Glue Resources
• How to use Boto3 to remove tags from AWS Glue Resources
• What is try-with-resource in java?
• How to handle Null values while working with JDBC?
• How to handle Exceptions while working with JDBC applications?
• How to use Boto3 to get tags from an AWS Glue Resources
• How to create a table with decimal values using JDBC?