Database connections

Hello,

I've a Java service in which I listen to incoming IBM MQ messages on a configured channel/queue. I've four type of messages,based on the business logic. For each of these types, there is mapping of MS Sql database connection string maintained in a config table(say T) in another database.

So, for every message receieved, depending upon the type of message received:

I the retrieve the corresponding database connectionn string from this table T above.
Connect to the db and execute a stored proc(MySP), using the following code:
Connection dbConn = null; try {    dbConn = DriverManager.getConnection(dblUrl, "testusername", "testpassword");    CallableStatement cstmt = con.prepareCall("{call MySP()}");    ResultSet rs = cstmt.executeQuery(); } catch (Exception ex) {    //Handle and log exceptions here. } finally {    if (dbConn != null) {        dbConn.close();    } } My question here is: Is this approach of dynamically connecting to db a correct way in Java w.r.t. performance/memory usage, especially when the volume of messages is large enough causing a lot of opening and closing db operations? Or there is a better way to do this in Java?

Usually you would some kind of connection pool solution, where rather than opening and closing connections to the database, a number of database connections are kept open, and a sharing among different parts of your application (or maybe a group of worker threads in a single part of your application).

Depending on the framework/middleware that you are using, support for connection pooling may already be built-in, and just require configuration to take advantage of it.

Here's a couple of write-ups which might help:
Baeldung: A Simple Guide to Connection Pooling in Java
Progress: JDBC Connection Pooling Tutorial

To add on a little to the above answer which I agree with, not all connection pools are equal.

If you're really concerned with performance and you're willing to pull in a dependency, allegedly Hikari is the best around for doing this. I'll let their GitHub page speak for itself defending that claim.

https://github.com/brettwooldridge/HikariCP

If you have a webapp, then a Connection Pool should definitely be used and the best way to use one is to define it to the webapp server as part of the webapp's Deployment Descriptor. Then you can use JNDI to obtain a reference to the Connection Pool and that reference can be safely stored globally in the webapp so you don't have to look it up each time you need access to the pool. While the webapp server will come with a standard Connection Pool implementation built in, it's usually plug-replaceable if you find a different Connection Pool implementation that's more suited for your needs.

Similar options may be available for other frameworks, but you can also write stand-alone Java apps that create and use their own Connection Pools. Please note that DriverManager.getConnection() is NOT connection pool-based, though. It's what connection pools invoke to build their pools and it has a LOT more overhead itself than simply getting a Connection from a Connection Pool does.

Some other important things to note is that in order to use a Connection Pool most efficiently, you should not obtain the Connection until you actually need it and to close() the Connection as soon as you are done with it. And absolutely never cache and hold a Connection between requests in a webapp!

Please avoid abbreviations like, “w.r.t.,” which may confuse non‑native‑English speakers, and certainly confuses Google Translate.

Campbell Ritchie wrote:Please avoid abbreviations like, “w.r.t.,” which may confuse non‑native‑English speakers, and certainly confuses Google Translate.

I don't even think it's that obvious to Americans. More of a British/South Asia phrase.

A better usage here would be to use the simple word "regarding".