Java - how to close resources correctly / objects during MySQL queries with JDBC?

In this article proper way how to release resources working with JDBC has been presented.

1. close() method example

package com.dirask.examples;

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

public class Program {

	private static final String DB_NAME = "test";
	private static final String DB_HOST = "127.0.0.1"; // 'localhost'
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "root";
	
	private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" 
		+ DB_NAME + "?serverTimezone=UTC";
	
	public static void main(String[] args) throws ClassNotFoundException {
		
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;
		
		try {
			String sql = "SELECT * FROM `users`";
			
			connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			statement = connection.createStatement();
			result = statement.executeQuery(sql);
					
			System.out.print("[id]\t[name]\t[role]\n");
			
			while (result.next()) {
				int id = result.getInt("id");
				String name = result.getString("name");
				String role = result.getString("role");
				
				System.out.print(id + "\t" + name + "\t" + role + "\n");
			}
		} catch ( SQLException e ) {
			e.printStackTrace();
		} finally {
			// it is good practice to close object in reversed order to creation
			closeObject(result); 
			closeObject(statement);
			closeObject(connection);
		}
	}
	
	private static boolean closeObject(AutoCloseable object) {
		if (object == null) {
			return false;
		}
		
		try {
			// do it in try-catch block 
			// to let close other resources if exception occurred
			object.close();
			
			return true;
		} catch (Exception e) {
			return false;
		}
	}
}

Result:

[id]	[name]	[role]
1	John	admin
2	Chris	moderator
3	Kate	user
4	Denis	moderator
5	Matt	moderator

Note: data preparation sql can be found here (at the end of article).

2. try-with-resources example 

Contruction try (Type variable = ...;) { ... } takes care of releasing of all object references that were places inside round brackets (for variable will be called close() method after curly bracket instructions will be finished). To use object with try-with-resources it is necessary to implement AutoCloseable interface by them.

package com.dirask.examples;

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

public class Program {

	private static final String DB_NAME = "test";
	private static final String DB_HOST = "127.0.0.1"; // 'localhost'
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "root";
	
	private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" 
		+ DB_NAME + "?serverTimezone=UTC";
	
	public static void main(String[] args) throws ClassNotFoundException {

		String sql = "SELECT * FROM `users`";
			
		try (
			Connection connection = DriverManager.getConnection(DB_URL,
				DB_USER, DB_PASSWORD);

			Statement statement = connection.createStatement();
		) {
			try (ResultSet result = statement.executeQuery(sql)) {
				System.out.print("[id]\t[name]\t[role]\n");
				
				while (result.next()) {
					int id = result.getInt("id");
					String name = result.getString("name");
					String role = result.getString("role");
					
					System.out.print(id + "\t" + name + "\t" + role + "\n");
				}
			}
		} catch ( SQLException e ) {
			e.printStackTrace();
		}
	}
}

Result:

[id]	[name]	[role]
1	John	admin
2	Chris	moderator
3	Kate	user
4	Denis	moderator
5	Matt	moderator

Note: data preparation sql can be found here (at the end of article).