How to release a connection automatically when exiting any method of a class?

Question

So, here is some background info: I'm currently working at a company providing SaaS and my work involves writing methods using JDBC to retrieve and process data on a database. Here is the problem, most of the methods comes with certain pattern to manage connection:

public Object someMethod(Object... parameters) throws MyCompanyException{
    try{
        Connection con = ConnectionPool.getConnection();
        con.setAutoCommit(false);

        // do something here

        con.commit();
        con.setAutoCommit(true);
    }
    catch(SomeException1 e){
        con.rollback();
        throw new MyCompanyException(e);
    } 
    catch(SomeException2 e){
        con.rollback();
        throw new MyCompanyException(e);
    }
    // repeat until all exception are catched and handled
    finally {
        ConnectionPool.freeConnection(con);
    }
    // return something if the method is not void
}

It had been already taken as a company standard to do all methods like this, so that the method would rollback all changes it had made in case of any exception is caught, and the connection will also be freed asap. However, from time to time some of us may forget to do some certain routine things when coding, like releasing connection or rollback when error occurs, and such mistake is not quite easily detectable until our customers complaint about it. So I've decided to make these routine things be done automatically even it is not declared in the method. For connection initiation and set up, it can be done by using the constructor easily.

public abstract SomeAbstractClass {
    protected Connection con;
    public SomeAbstractClass() {
        con = CoolectionPool.getConnection();
        con.setAutoCommit(false);
    }
}

But the real problem is to make connection to be released automatically immediately after finishing the method. I've considered using finalize() to do so, but this is not what I'm looking for as finalize() is called by GC and that means it might not finalize my object when the method is finished, and even when the object will never be referenced. finalize() is only called when JVM really run out of memory to go on.

Is there anyway to free my connection automatically and immediately when the method finishes its job?

If you're using JDK 7 or later, then your answer consists in using [try with resources](http://docs.oracle.com/javase/7/docs/technotes/guides/language/try-with-resources.html). You can find an example in this [other answer](https://stackoverflow.com/a/25448694/697630). — Edwin Dalorzo, Jul 11 '17 at 16:13
many thanks everyone. i guess i would just go with try-with-resource as this is probably the easiest way to avoid unclosed connection. — user1907570, Jul 11 '17 at 17:13

score 3 · Accepted Answer · answered Jul 11 '17 at 16:32

Use "try with resources". It is a programming pattern such that you write a typical looking try - catch block, and if anything goes wrong or you exit it, the resources are closed.

try (Connection con = ConnectionPool.getConnection()) {
   con.doStuff(...);
}
// at here Connection con is closed.

It works by Connection extending Closeable, and if any class within the "resource acquisition" portion of the try statement implements Closeable then the object's close() method will be called before control is passed out of the try / catch block.

This prevents the need to use finally { ... } for many scenarios, and is actually safer than most hand-written finally { ... } blocks as it also accommodates exceptions throw in the catch { ... } and finally { ... } blocks while still closing the resource.

this is really neat. It does not require much changes in the class structure and i don't have to explicitly close the connection in this way. and i guess it wouldn't be to hard to write a new method to `ConnectionPool` to get connection initiated by default. many thanks mate. — user1907570, Jul 11 '17 at 16:54

score 1 · Answer 2 · answered Jul 11 '17 at 16:24

1

One of the standard ways to do this is using AOP. You can look at Spring Framework on how it handles JDBC tansactions and connections and manages them using MethodInterceptor. My advice is to use Spring in your project and not reinvent the wheel.

The idea behind MethodInterceptor is that you add a code that creates and opens connection before JDBC related method is called, puts the connection into the thread local so that your method can get the connection to make SQL calls, and then closes it after the method is executed.

answered Jul 11 '17 at 16:24

tsolakp

5,858
1
22
28

Idea of using interceptors to handle database connections is good deal. Java 7 on wards we have better solutions for this. – Acewin Jul 11 '17 at 16:27
Java7 only gives you try with resource support. You still need to duplicate the same try blocks everywhere you need to deal with connection that OP is trying to avoid. AOP will free you from doing that boilerplate code. – tsolakp Jul 11 '17 at 16:30
Dont argue with that. Even though this is best solution, using AOP based interceptor also means adding Spring to your project. If the person is not using Spring then try with resources is the best approach to handle this. – Acewin Jul 11 '17 at 16:36
@Acewin AOP doesn't fix the problem, the code that's injected by AOP that fixes the problem. It takes a lot of effort to really write a correct `try / catch / finally` block when one cares about exceptions within `catch` and `finally` and possible calls to `return` embedded in them both. AOP injected code rarely handles those scenarios. With "try using resources" the compiler writes that code for you, in a way that doesn't leave corner case scenarios to forget. – Edwin Buck Jul 11 '17 at 16:39
If you go by the logic "bad code or writing a bad code" is an issue, any solution will be bad. And I am not saying anything differently. Using MethodInterceptor is easiest and most suitable solution. Only thing I added was "If one is not using spring" – Acewin Jul 11 '17 at 16:44
Just FYI, you dont need to use Spring in order to use AOP. You can directly use Java Proxy or AspectJ and implement your own MethodInterceptor that manages the connection. – tsolakp Jul 11 '17 at 16:55
using Spring would be great, but that also requires a lot of work on rewriting old codes to just make things work. though we had already been using spring framework on some new projects, i guess it is not likely to use Spring in this one. thanks anyway. – user1907570 Jul 11 '17 at 17:01
@Acewin "Easiest" - a judgement call. "Most suitable" - again a judgement call. `try with resources` is actually easier than both, if you count lines of code requiring writing. And you cannot write a mistake into the "try with resources" when it relates to closing something, because you don't write the code, the compiler does. So, it's not the same thing. You are advocating writing code to do what a compiler will do for you. That's hardly "easier" and certainly more error-prone. To make the code writing easier, you're also adding in tools left and right. This much "easy" is hard. – Edwin Buck Jul 11 '17 at 18:15

score 0 · Answer 3 · answered Jul 11 '17 at 16:15

You could add a method to your ConnectionPool class for example:

public <T> T execute(Function<Connection, T> query,
                      T defaultValue,
                      Object... parameters) {
  try {
    Connection con = ConnectionPool.getConnection();
    con.setAutoCommit(false);

    Object result = query.apply(conn);
    con.commit();
    con.setAutoCommit(true);
    return result;
  } catch(SomeException1 e) {
    con.rollback();
    throw new MyCompanyException(e);
  }
  //etc.
  finally {
    ConnectionPool.freeConnection(con);
  }
  return defaultValue;
}

And you call it from the rest of your code with:

public Object someMethod(Object... parameters) throws MyCompanyException {
  return ConnectionPool.execute(
    con -> { ... }, //use the connection and return something
    null, //default value
    parameters
  );
}

i'm not so sure if this is really a good way to do it. it seems like codes written in this way are even less maintainable than what i'm currently doing. it could be useful if it is used with some `Reflection` tho. thanks anyway. — user1907570, Jul 11 '17 at 17:10

How to release a connection automatically when exiting any method of a class?

3 Answers3