How to pass around JDBC connection without using Spring/JPA/Hibernate

Question

We have a Java J2EE application that was using individual web service calls for each database row insert/update. That turned out to be WAY too slow. They have brought me in to "quickly" fix it. I plan to convert all the web service calls to plain JDBC. To do that, I need to get a JDBC connection from the pool and then use it in multiple different methods. I need to use the same JDBC connection in multiple DAOs to string it all together into a single database transaction. I can explicitly pass around the JDBC connection to each DAO that needs it, but that would require me to change a LOT of method signatures, plus a LOT of unit tests (which goes against the "quickly" part).

I am trying to come up with a good way put the JDBC connection somewhere and then just grab it in the methods that need it without having to explicitly pass it around everywhere. We can't use Spring, JPA, or Hibernate on this project because the support team won't support those technologies. I can put the JDBC connection into an EJB, but I am not sure how reliable that would be. I could create a custom Singleton to manage database connections for each user (session?), but I would have to be careful about thread safety. If anyone has tried to do something like this before, I would appreciate some advice.

Answer 1

You could use a ThreadLocal. Have the entry point set it up and than the DAOs

class ConnectionUtil {
    public static final ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
}

public Return method(Args arg) {
    ConnectionUtil.connection.set(newConnection());
    try {
        ...
    } finally {
        ConnectionUtil.connection.remove();
    }
}

Pretty ugly, but that seems to be what your boss wants.

Answer 2

Use Apache Commons DBCP. It's the Connection Pool project from Apache, and what's used internally in many engines.