How should I use try-with-resources with JDBC?
There's no need for the outer try in your example, so you can at least go down from 3 to 2, and also you don't need closing ;
at the end of the resource list. The advantage of using two try blocks is that all of your code is present up front so you don't have to refer to a separate method:
public List<User> getUser(int userId) {
String sql = "SELECT id, username FROM users WHERE id = ?";
List<User> users = new ArrayList<>();
try (Connection con = DriverManager.getConnection(myConnectionURL);
PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, userId);
try (ResultSet rs = ps.executeQuery()) {
while(rs.next()) {
users.add(new User(rs.getInt("id"), rs.getString("name")));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
JDBC connection try with resources use
Your code as shown will only close the prepared statement, and leak the connection. If you want to close both, you need to use a statement per resource in the resources block:
try (Connection connection = ds.getConnection();
PreparedStatement prepare = connection.prepareStatement(statement)) {
// your code here
}
JDBC with try with resources
Java 7
When you use try-with-resources, variables pointing to Closeable
resources must be declared inside try-with-resources
block.
Moreover, returning rs
is a bad idea, it would be closed after method is done. So you might get an SQLException
outside your method (something like "ResultSet is closed"). You should parse rs
inside try-with-resources
block and return SQL agnostic object from your method:
public ResultSet retrieveSQLQuery(String sqlQuery) {
try (Connection conn = DriverManager.getConnection(dbUrl, user, password);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sqlQuery)) {
MyResult result = ...; // parse rs here
return myResult;
} catch (SQLException e) {
logger.info(e.getMessage());
// return something (empty MyResult or null) from here or rethrow the exception
// I'd recommend to get rid of this catch block and declare the SQLException on method signature
}
}
You're getting compile-time error on incorrect try-with-resources
syntax, that's it.
Update
Java 9
Java 9 provides more flexible syntax for try-with-resources
. You can declare Closeable
resource outside the try (...)
block:
public ResultSet retrieveSQLQuery(String sqlQuery) {
Connection conn = DriverManager.getConnection(dbUrl, user, password);
try (conn; ResultSet rs = conn.createStatement().executeQuery(sqlQuery)) {
MyResult result = ...; // parse rs here
return myResult;
} catch (SQLException e) {
// handle error
}
}
try with resources - parameterized PreparedStatements
Using inner try with resource, after the external try with resource, init the statement and then use the inner try with resource for the resultSet.
public UserBean getUserDevices(final String emailAddress, final String uuid) throws SQLException {
UserBean userBean = null;
String query = "SELECT user.id, user.emailAddress, device.uuid, device.active, device.user_id FROM user " +
"LEFT JOIN device ON user.id = device.user_id AND device.uuid = ? " +
"WHERE user.emailAddress = ?";
try (Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(query);
) {
preparedStatement.setString(1, uuid);
preparedStatement.setString(2, emailAddress);
try(ResultSet resultSet = preparedStatement.executeQuery();) {
while(resultSet.next()) {
if(userBean == null) { // initialize user once while iterating for devices
userBean = new UserBean(resultSet.getInt("user.id"), resultSet.getString("user.emailAddress"), null);
}
if(resultSet.getString("device.uuid") != null) { // if device exists (it's a LEFT JOIN) then add
userBean.getDevices().add(new DeviceBean(0, resultSet.getString("device.uuid"), resultSet.getBoolean("device.active"), resultSet.getInt("device.user_id")));
}
}
}
}
return userBean;
}
Try-with-resources - Does it automatically close the connection? Java
No, the try with resources does not close the Connection
that is used inside the PreparedStatement
.
Only the PreparedStatement
and its ResultSet
are closed.
When a Statement object is closed, its current ResultSet object, if one exists, is also closed.
It is possible to reuse a connection
to execute many PreparedStatements
. Each of which is closed after usage. When the connection
is no longer needed it can be closed as well.
You could perhaps check it like this:
public void query(String queryString, List<String> queryParams, Consumer<ResultSet> sqlConsumer)
{
Connection connection;
try (PreparedStatement preparedStatement = this.configureStatement(queryString, queryParams))
{
connection=preparedStatement.getConnection();
sqlConsumer.accept(preparedStatement.executeQuery());
} catch(SQLException exception)
{
exception.printStackTrace();
}
if(connection!=null){
System.out.println("Is Connection closed:"+connection.isClosed());
}
}
private PreparedStatement configureStatement(String query, List<String> queryParams) throws SQLException
{
PreparedStatement preparedStatement = this.getConnection().prepareStatement(query);
for (int i = 0; i < queryParams.size(); ++i)
preparedStatement.setString(i, queryParams.get(i));
return preparedStatement;
}
A refactoring that closes connections by using the try-with-resources with multiple statements:
public void query(String queryString, List<String> queryParams, Consumer<ResultSet> sqlConsumer)
{
try ( Connection connection=this.getConnection();
PreparedStatement preparedStatement = this.configureStatement(connection, queryString, queryParams);)
{
sqlConsumer.accept(preparedStatement.executeQuery());
} catch(SQLException exception)
{
exception.printStackTrace();
}
if(connection!=null){
connection.close();
}
}
private PreparedStatement configureStatement( Connection connection,String query, List<String> queryParams) throws SQLException
{
PreparedStatement preparedStatement = connection.prepareStatement(query);
for (int i = 0; i < queryParams.size(); ++i)
preparedStatement.setString(i, queryParams.get(i));
return preparedStatement;
}
How to properly use try-with-resources in Database connection?
If you are fine to go for an additional method then it can be possible with only one try-resources
Instead of Statement statement = conn.createStatement();
Statement statement = createStatement(conn, maxRows);
Inside that new method, create Statement object and set the maxRows and return the statement obj.
Try with resources Statement for JDBC in java
The idea behind try-with-ressource is to close an AutoCloseable
class.
So every usage of a class which should be closed after using it (a Ressource) can be used with try-with-ressource (like Connection for example). You don't have to take care of closing it manually (in an finally block for example).
So yes, your idea is right:
- try/catch for
Class.forName("org.apache.hive.jdbc.HiveDriver");
- because this is not AutoCloseable - try-with-ressource for
Connection con = DriverManager.getConnection(connectionUri, userName, password);
- because
Statement stmt = con.createStatement();Connection
andStatement
implement AutoCloseable
Reference:
https://docs.oracle.com/javase/7/docs/api/java/lang/AutoCloseable.html
Using try with resources for resources created without any reference
Only the ResultSet
will be closed. If you want multiple resources to be closed, you need to declare them separately:
try (Connection conn = DriverManager.getConnection("jdbc:...", "user", "pass");
Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery(sql)) {
// do stuff...
}
Related Topics
Does Use of Final Keyword in Java Improve the Performance
How to Set the Default Locale in the Jvm
What Is the Easiest/Best/Most Correct Way to Iterate Through the Characters of a String in Java
How to Search Google Programmatically Java API
How to Determine an Object's Class
Differencebetween the Hashmap and Map Objects in Java
Spring Data JPA - Zoneddatetime Format for JSON Serialization
Why Is Java's Simpledateformat Not Thread-Safe
Display Indeterminate Jprogressbar While Batch File Runs
Should I Initialize Variable Within Constructor or Outside Constructor
Why Is There No Multiple Inheritance in Java, But Implementing Multiple Interfaces Is Allowed