EN
Java - how to close resources correctly / objects during MySQL queries with JDBC?
9 points
In this article proper way how to release resources working with JDBC has been presented.
xxxxxxxxxx1
package com.dirask.examples;2
3
import java.sql.Connection;4
import java.sql.DriverManager;5
import java.sql.ResultSet;6
import java.sql.SQLException;7
import java.sql.Statement;8
9
public class Program {10
11
private static final String DB_NAME = "test";12
private static final String DB_HOST = "127.0.0.1"; // 'localhost'13
private static final String DB_USER = "root";14
private static final String DB_PASSWORD = "root";15
16
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" 17
+ DB_NAME + "?serverTimezone=UTC";18
19
public static void main(String[] args) throws ClassNotFoundException {20
21
Connection connection = null;22
Statement statement = null;23
ResultSet result = null;24
25
try {26
String sql = "SELECT * FROM `users`";27
28
connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);29
statement = connection.createStatement();30
result = statement.executeQuery(sql);31
32
System.out.print("[id]\t[name]\t[role]\n");33
34
while (result.next()) {35
int id = result.getInt("id");36
String name = result.getString("name");37
String role = result.getString("role");38
39
System.out.print(id + "\t" + name + "\t" + role + "\n");40
}41
} catch ( SQLException e ) {42
e.printStackTrace();43
} finally {44
// it is good practice to close object in reversed order to creation45
closeObject(result); 46
closeObject(statement);47
closeObject(connection);48
}49
}50
51
private static boolean closeObject(AutoCloseable object) {52
if (object == null) {53
return false;54
}55
56
try {57
// do it in try-catch block 58
// to let close other resources if exception occurred59
object.close();60
61
return true;62
} catch (Exception e) {63
return false;64
}65
}66
}Result:
xxxxxxxxxx1
[id] [name] [role]2
1 John admin3
2 Chris moderator4
3 Kate user5
4 Denis moderator6
5 Matt moderatorNote: data preparation sql can be found here (at the end of article).
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.
xxxxxxxxxx1
package com.dirask.examples;2
3
import java.sql.Connection;4
import java.sql.DriverManager;5
import java.sql.ResultSet;6
import java.sql.SQLException;7
import java.sql.Statement;8
9
public class Program {10
11
private static final String DB_NAME = "test";12
private static final String DB_HOST = "127.0.0.1"; // 'localhost'13
private static final String DB_USER = "root";14
private static final String DB_PASSWORD = "root";15
16
private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" 17
+ DB_NAME + "?serverTimezone=UTC";18
19
public static void main(String[] args) throws ClassNotFoundException {20
21
String sql = "SELECT * FROM `users`";22
23
try (24
Connection connection = DriverManager.getConnection(DB_URL,25
DB_USER, DB_PASSWORD);26
27
Statement statement = connection.createStatement();28
) {29
try (ResultSet result = statement.executeQuery(sql)) {30
System.out.print("[id]\t[name]\t[role]\n");31
32
while (result.next()) {33
int id = result.getInt("id");34
String name = result.getString("name");35
String role = result.getString("role");36
37
System.out.print(id + "\t" + name + "\t" + role + "\n");38
}39
}40
} catch ( SQLException e ) {41
e.printStackTrace();42
}43
}44
}Result:
xxxxxxxxxx1
[id] [name] [role]2
1 John admin3
2 Chris moderator4
3 Kate user5
4 Denis moderator6
5 Matt moderatorNote: data preparation sql can be found here (at the end of article).
