Top community members
All Wiki Articles Create Wiki Article

Many years me and my friends we were volunteers on my university doing programming classes for young people.

I got main conclusion: Young people are awesome, especially students, because they always bring new and fresh ideas :)
* I loved to do it but my time is limited

  I decided to create something that will help millions of students

Now I am here

co-founder

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

0 contributions
9 points

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).

0 contributions

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey 👋
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more