CODEWITHSUNDEEP
javadesign-patterns
Sathya
Sathya

Reputation: 82

Used and Unused db connection objects

I encountered a question where in we have a set of say DB connections and multiple users simultaneously request for them.

I must be able to provide them the Conn object from a pool of existing connectios, and once they are done using it, put it back into the same pool of available connections.

The approach I could think of was to have 2 sets for each used and unused connections and keep moving objects back and forth.

I am somehow not convinced it's the best way to do this.

Can anyone pls suggest a nicer approach? I was wondering if we could mark the connection obj as in use or something so that we can do away with these two sets?

Upvotes: 0

Views: 73

Answers (2)

Boris the Spider
Boris the Spider

Reputation: 61128

TL;DR: Use a connection pool

There are thousands of implementations to choose from:

  • Hikari CP
  • Bone CP
  • C3P0
  • Commons DBCP
  • and others

These pools are tested, working, optimised code. Not whatever your homebrew solution is.

The problem you encountered is just one you will encounter on the way to writing a resilient connection pool. The solution you propose is naive at best. There are many other issues, from the misuse of the pool to connection dropout that you have to handle. And not only that but the code must be threadsafe.

For general scarce resource pooling, not JDBC connections, use Commons Pool. Again, this is tested, working code.

Do not reinvent the square wheel.

Upvotes: 1

Dici
Dici

Reputation: 25950

Don't reinvent the wheel. There are existing solutions for this, one of them being Apache Commons pools : https://commons.apache.org/proper/commons-pool/. All you have to do is provide a factory to initialize your connections at the beginning, then Apache will provide you with a GenericObjectPool (see doc) on which you can call borrowObject before accessing a connection in the pool and then release it using returnObject.

Upvotes: 0

Related Questions