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