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.

sblundy · Accepted Answer · 2010-12-09 16:53:11Z

2

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.

answered Dec 9, 2010 at 16:53

sblundy

61.5k22 gold badges124 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shane Over a year ago

This might work really well for us. Are there any major performance issues doing it this way?

sblundy Over a year ago

ThreadLocal's have been a target of performance improvements from Sun for a while. Compared to DB operations, it'll be a rounding error.

Valentin Rocher · Accepted Answer · 2010-12-09 16:55:16Z

2

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

answered Dec 9, 2010 at 16:55

Valentin Rocher

11.7k48 silver badges59 bronze badges

1 Comment

Shane Over a year ago

I understand that Apache Commons DBCP helps maintain a pool of connections, but I need each individual DAO insert/update/delete method to be able to grab the SAME connection. Can DBCP help with that? Is there an example somewhere? I tried several Google searched and couldn't find anything.

Christian · Accepted Answer · 2010-12-09 16:49:55Z

1

We have done that before (5 years ago or so on an IBM WebSphere). We wrote an own pool and stored the jdbc-connections in a hashtable with the sessionID. The only pitfall was to close the connection on sessionend and return it to the pool (we did that with a sessionlistener). If one user session connects only to one jdbc-connection, the thread safety is inherited. So the singleton approach does definitly work. Our performance gain was awful.

answered Dec 9, 2010 at 16:49

Christian

4,1743 gold badges29 silver badges50 bronze badges

Comments

jzd · Accepted Answer · 2010-12-09 17:50:33Z

0

Additionally what I have done for DAOs in this is not to pass the connection in the method signature but in the Constructor instead. Then I hold the object that originally creates the connection responsible to close it.

answered Dec 9, 2010 at 17:50

jzd

23.6k9 gold badges58 silver badges76 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related