How to properly use try-with-resources in Database connection?

Question

I have looked around, but can't seem to find the answer to my question.

Here is the context : I have to connect to a Database in my Java program and execute a SQL request that I have no control over and don't know in advance. To do that I use the code below.

public Collection<HashMap<String, String>> runQuery(String request, int maxRows) {

    List<HashMap<String, String>> resultList = new ArrayList<>();

    DataSource datasource = null;

    try {

        Context initContext = new InitialContext();
        datasource = (DataSource) initContext.lookup("java:jboss/datasources/xxxxDS"); 

    } catch (NamingException ex) {

        // throw something.
    }

    try (Connection conn = datasource.getConnection();
         Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(request); ) {

        while (rs.next()) 
        {
            HashMap<String, String> map = new HashMap<>();

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                map.put(rs.getMetaData().getColumnName(i).toUpperCase(), rs.getString(i));
            }

            resultList.add(map);             
        }

    } catch (SQLException ex) {

        // throw something.
    }

    return resultList;       
}

The issue I am facing is : As you can see there is another parameter maxRows that I don't use. I need to specify this to the statement but can't do it in the try-with-resources.

I would like to avoid increasing cognitive complexity of this method by nesting another try-with-resources inside the first one in order to specify the max number of rows (like in this sample of code).

try (Connection conn = datasource.getConnection();
 Statement statement = conn.createStatement(); ) {

    statement.setMaxRows(maxRows);

    try (ResultSet rs = statement.executeQuery(request); ) {

        while (rs.next()) 
        {
            HashMap<String, String> map = new HashMap<>();

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                map.put(rs.getMetaData().getColumnName(i).toUpperCase(), rs.getString(i));
            }

            resultList.add(map);             
        }
    }

} catch (SQLException ex) {

    // throw something.
}

Is there any way to do it with only one try-with-resources?

You can create a separate method that will create a statement and `setMaxRows`. Something similar to the approach in [this](https://stackoverflow.com/a/12256423/6486622) answer — Denis Zavedeev, Jan 08 '20 at 13:29
You don’t have to close the ResultSet. Closing a Statement will automatically close its ResultSet. From [the documentation](https://docs.oracle.com/en/java/javase/13/docs/api/java.sql/java/sql/Statement.html#close%28%29): “When a `Statement` object is closed, its current `ResultSet` object, if one exists, is also closed.” — VGR, Jan 08 '20 at 15:03
@VGR Oh I didn't see the note when I went through the doc, thanks for pointing that out ! Note that, Sonar is not aware of the documentation and will point that you should use `try-with-resources` if you remove it from the `try`. — Philippe B., Jan 08 '20 at 15:39
Which is why neither Sonar nor any other code analysis tool should be considered the final word on good practices. — VGR, Jan 08 '20 at 15:45

score 3 · Accepted Answer · answered Jan 08 '20 at 13:29

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.

score 1 · Answer 2 · answered Jan 08 '20 at 13:34

You can add an helper method to do it in a single try-with-resources block:

    private static <T, E extends Exception> T configured(T resource, ThrowingConsumer<? super T, E> configuration) throws E {
        configuration.accept(resource);
        return resource;
    }

    private interface ThrowingConsumer<T, E extends Exception> {

        void accept(T value) throws E;
    }

And use it like this:

        try (Connection conn = null
              ; Statement statement = configured(conn.createStatement(), stmt -> stmt.setMaxRows(maxRows))
              ; ResultSet rs = statement.executeQuery(request)) {

        }

How to properly use try-with-resources in Database connection?

2 Answers2

Linked