How to Use Try-With-Resources with Jdbc

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);
    Statement stmt = con.createStatement();
    - because Connection and Statement 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



Leave a reply



Submit