Database Connection Pooling is the fundamental building block of any software application. A datasource allows an application written in any language to connect to a database. In many enterprise, it is most likely Oracle database.
A connection pooling is a standard practice in connecting to a database. However, when a connection pool does not behave as expected, then the application may encounter many different issues.
As long as, your code closes a connection that is opened, the application shall behave normally. However, if the application is high frequency or high volume application, then it will connect/dis-connet with the database many times. In such case, there is a risk of filling up system audit table that audits connections. It is the standard practice to use connection pooling to avoid abnormal database behavior due to the high frequency of read and writes.
There are many good connection pool libraries such as Apache DBCP. Also, all Java application servers has connection pooling. Thus, in an application server, all you have to do it to configure your data source correctly and this about it.
However, if you are creating your own datasource that has connection pooling then BEWARE and validate that you are doing it correct.
Following is an example of Oracle Data Source configuration example.
oracleDataSource = new OracleDataSource();
oracleDataSource.setUser(dbUserName);
oracleDataSource.setURL(driverURL);
oracleDataSource.setPassword(somepwd);
Properties cacheProperties = new Properties();
cacheProperties.setProperty("MinLimit", "2");
cacheProperties.setProperty("MaxLimit", "2");
cacheProperties.setProperty("InitialLimit", "2");
cacheProperties.setProperty("TimeToLiveTimeout", "900");
cacheProperties.setProperty("ValidateConnection", "true");
cacheProperties.setProperty("ClosestConnectionMatch", "true");
cacheProperties.setProperty("ConnectionWaitTimeout", "0");
cacheProperties.setProperty("AbandonedConnectionTimeout", "900");
cacheProperties.setProperty("InactivityTimeout", "900");
cacheProperties.setProperty("connection_type", "testing");
cacheProperties.setProperty("MaxStatementsLimit", "900")
// Following three code sequence MUST in this order for the data source to work correctly to do connection pooling.
oracleDataSource.setConnectionCachingEnabled(true);
oracleDataSource.setConnectionCacheName(CACHE_NAME);
oracleDataSource.setConnectionCacheProperties(cacheProperties);