Sharing database connection with static methods in Java

Question

I'm working on a java web application using Struts 2. Currently, on each page load, I establish a new db connection, and then close this db connection at the end of the request, just before rendering the resulting HTML. So, each request gets its own db connection.

I want to have a bunch of static methods within my model classes, e.g things like User.exists( id ) which would return true if the given user id existed, or false if it didn't. Or other utility methods like User.getEmail(id) or User.disable(id).

My question is, is there a convenient way to share the db connection with these static methods? Having to pass on the connection as a 2nd argument to all these methods, e.g User.exists(id, db) would be ugly, and not too convenient.

What about if I obtain a new db connection in each of these utility methods, and close it before returning the result? Would it have any impact on the perfomance? I could need to call these methods 20-30 times within a request some times (such as when validating user input).

Only if your DB connection is fully thread-safe, which is very unlikely. Use a connection pool. — SLaks, Oct 17 '13 at 20:12
@SLaks I'm using a struts interceptor which is called by struts at the start of each page request. the db connection is made within the `intercept()` method of the interceptor and also closed in the same method, where it knows that the request has finished rendering. So I think I'm safe on the thread side. My concern is about making a new connection within each static utility method. Would that affect performance? — Ali, Oct 17 '13 at 20:23
If you are using a JEE application server use it's connection pooling facilities. Then you can implement an OSIV interceptor, which if using Spring/hibernate will only require an action to establish a transactional session. The transactional session will only be closed when the interceptor unwinds but if no DB access is required then no time is spent on opening an actual connection to the db. — Quaternion, Oct 18 '13 at 00:49
Also the issue of how to transfer the required db object though objects that have no business knowing about the db object is indeed messy and why DI (dependency injection) has made life a lot nicer. — Quaternion, Oct 18 '13 at 00:53

Alfredo Osorio · Accepted Answer · 2013-10-17T20:47:34.470

Yes, it is possible. For this case where you want that each Thread has its own Connection, you need to use ThreadLocal, since each request spawn its own Thread. You just need to make sure to close the connection at the end of the request, this is achieved using a Filter.

The filter should be before any other Filter to ensure that the connection is closed at the end of the request.

DBUtil:

public class DBUtil {

    private static ThreadLocal<Connection> connectionHolder = new ThreadLocal<Connection>();

    public static Connection getConnection() {
        Connection connection = connectionHolder.get();
        if (connection == null) {
            //open the connection (lazy loaded)

            //store it
            connectionHolder.set(connection);

        }
        return connectionHolder.get();
    }

    public static void close() {
        Connection connection = connectionHolder.get();
        if (connection != null) {
            //close the connection

            //remove it from the connection holder
            connectionHolder.remove();
        }
    }

}

DBFilter:

public void doFilter(ServletRequest request, ServletResponse response,
            FilterChain chain) throws IOException, ServletException {       
    try {
        chain.doFilter(request, response);
    } finally {
        DBUtil.close();         
    }
}

It is better if you use a framework for these kind of things, for example Spring Framework already do this by proxying your services and it handles the connection and transaction also gives you a lot of other features.

Interesting, so you're saying I could have a ThreadLocal db connection and then each time I called `Db.getConnection()`, it would return the unique / local db connection for the current request only? If so, can you give a bit more details on how to implement it, e.g in the filters? And then I can accept. — Ali, Oct 17 '13 at 20:27
@ClickUpvote done. Anyway I'd recommend to use something more complete such as Spring which gives you this and Transaction features, so you don't need to made it yourself. — Alfredo Osorio, Oct 17 '13 at 20:43
Thanks. Are you familiar with Struts? Struts 2 has an `ActionContext` whcih appears to be thread safe.. — Ali, Oct 17 '13 at 20:46
@ClickUpvote Exactly, ActionContext uses ThreadLocal too that's why it is thread safe. — Alfredo Osorio, Oct 17 '13 at 20:52
Perhaps you might be able to answer my question here? http://stackoverflow.com/questions/19437095/is-actioncontext-in-struts-2-unique-to-the-current-request — Ali, Oct 17 '13 at 20:55

Sharing database connection with static methods in Java

1 Answers1