Is it okay not to close javax.sql.DataSource in every method?

Question

I have a spring application where I use javax.sql.DataSource to connect to Oracle database. I have configured it as a bean. I use it in multiple methods. But I never call .close () method on it except inside the @PreDestroy method.

I use it in multiple methods like this:

private void method1(String query){
    Connection conn = dataSource.getConnection();
    try (PreparedStatement st = conn.prepareStatement(query);
        ResultSet rs = st.executeQuery()) {
        //do some task
    }
}

private void method2(String query){
    Connection conn = dataSource.getConnection();
    try (PreparedStatement st = conn.prepareStatement(query);
        ResultSet rs = st.executeQuery()) {
        //do some task
    }
}

Both PreparedStatement and ResultSet are inside try-with-resources block which will close both of them automatically. I did NOT put Connection inside try-with-resources because I want to use the same connection in multiple places.

Is it wise? I just gave 2 examples. I am in fact using it this way in 25 methods.

Answer 1

Best practice regarding connection is to use single connection for one logical transaction (this might be one simple select or maybe combination of multiple insert/update/select statements which should be executed at once and depend on each other), otherwise if you have bunch of unrelated queries it would be best to close connection.

Keep in mind that database has its own connection pooling so you can have multiple connections open at the same time if that is the reason why you wanted to use single connection.