Java – Managing multiple JDBC connection

javajdbc

I have an application that connects to databases based on a json string that contains connection information. The json is sent just when the application starts.

How do you manage such connection pools? Because I can get multiple configurations that point to the same database with the same user. I know I cannot create new connection every time since it's an expensive operation so I use connection pools (Apache DBCP2).

But when two pools connect to the same database I sometimes get "too many connections" error since the pool size of two pools is greater than the max allowed connections.

Is holding the connection pools in a HashMap where the connection string is used as the key a bad idea?

Config looks like this (very simplified json):

{
   dbconfig: [
      { database 1 ... },
      { database 2 ... },
      { database 3 ... }
   ],
   otherconfig: { ... }
}

The database 1 and 2 config point to the same database, or the same server but a different database

So my application spawns three threads that receive data on a predefined port. I don't know what database connections I will get and how many worker threads will I spawn. And my question is if there is some kind of pooling framework at which I can throw the configs and it will take care of the sharing of connections and I can just request the connection from it.

Currently with my design I would create 3 pools from which 2 pools are accessing the same server and sometimes the same database and that's why I get the "too many connections".

Best Answer

First off, you should be closing connections once you are done using them. That will help take care of the "too many connections" errors you're seeing. At an application level, you need to be releasing any shared resources you have as soon as you're done using them.

Second, it sounds like you're trying to duplicate the work that the underlying connection pool manager can provide for you. Specifically, at the application level, don't worry about the cost involved with opening / closing connections - that's what the pool manager handles for you. Because you haven't been closing off the connections once done, you're making it harder for the pool manager to be of use to you.

It's not clear if you have multiple, separate applications using separate pool managers or separate applications using the same pool manager. Regardless, using a HashMap with the connection string is a bad idea. At a minimum, you'll create inter-application communication issues. In the worst case, you're going to have to try and coordinate resources (connections) across applications in a web-server which is going to be problematic at best.

Finally, if you need to have multiple pools connecting to the same database server and the total number of connections across those pools is exceeding what the DB server can handle then you need to change that configuration on the DB server. If you truly need 11 connections and the DB server is only giving you 10, then you need to change the DB server.

To Summarize:

  • Close the connections already. You're creating potentially huge headaches in the name of perceived performance savings.

  • Change the DB server settings if you're still running out of connections.

Related Topic