Need for a common connection pool

With the introduction of Advanced Reporting which is a data-intensive operation, there was a considerable increase in the number of database connections created for both Ephesoft and reports database. Since the maximum number of connections have an upper limit in the database, there was a need to reduce and reuse the already created connections to reduce the overhead of connection generation. To counter this, an implementation has been made where a connection pool will be maintained for Ephesoft and reports database (one-each) at the tomcat container itself and a JNDI connection can be made either by the Ephesoft application or the components in the reporting module(LogiInfo and ETL).

Made Available in v4.0.3.0 and Higher.

 

Advantages

This approach gave us the following advantages-

  1. A shared connection pool could be shared between the Ephesoft and Advanced Reporting as reporting module needs database connection to Ephesoft database for reading data.
  2. A single point of configuration for reporting and Ephesoft database. Earlier, database connection information for Ephesoft application had to be specified redundantly in the Ephesoft\Application\WEB-INF\classes\META-INF\dcma-reporting\etl-variables.properties file.
  3. Reduce the possibility of exhaustion of database connections since the connections are managed in a controlled way by maintaining a connection pool.
  4. HikariCP – A “state-of-the-art” connection pool implementation has been used considering the performance improvements we are aiming at in the future. It is much faster as compared to the earlier connection pool implementation C3p0.

Figure 1-Charts comparing the performance of HIkariCP with other connection pools

Reference- http://brettwooldridge.github.io/HikariCP/

 

Sample Configuration

Add the following to <Context> tag in the context.xml located at JavaAppServer\conf

<ResourceLink name=”jdbc/ephesoft

global=”jdbc/ephesoft”

type=”javax.sql.DataSource”/>

<ResourceLink name=”jdbc/reports”

global=”jdbc/reports”

type=”javax.sql.DataSource”/>

The data source configuration will be maintained in the server.xml located at JavaAppServer\conf as shown below:

MariaDB

<Resource name=”jdbc/ephesoft” auth=”Container”

factory=” com.zaxxer.hikari.HikariJNDIFactory ”

type=”javax.sql.DataSource”

minimumIdle=”5″

maximumPoolSize=”100″

connectionTimeout=”300000″

driverClassName=”org.mariadb.jdbc.Driver”

jdbcUrl=”jdbc:mysql://localhost:3306/ephesoft”

dataSource.implicitCachingEnabled=”true”

dataSource.user=”root”

dataSource .password=”root”

connectionTestQuery=”Select 1″ />

<Resource name=”jdbc/reports” auth=”Container”

factory=” com.zaxxer.hikari.HikariJNDIFactory ”

type=”javax.sql.DataSource”

minimumIdle=”5″

maximumPoolSize=”100″

connectionTimeout=”300000″

driverClassName=”org.mariadb.jdbc.Driver”

jdbcUrl=”jdbc:mysql://localhost:3306/report”

dataSource.user=”root”

dataSource .password=”root”

dataSource.implicitCachingEnabled=”true”

connectionTestQuery=”Select 1″ />

MSSQL

<Resource name=”jdbc/ephesoft” auth=”Container”

factory=”com.zaxxer.hikari.HikariJNDIFactory”

type=”javax.sql.DataSource”

minimumIdle=”5″

maximumPoolSize=”50″

connectionTimeout=”300000″

driverClassName=”net.sourceforge.jtds.jdbc.Driver”

jdbcUrl=”jdbc:jtds:sqlserver://localhost:1433;databaseName=ephesoft;sendStringParametersAsUnicode=false;prepareSQL=3″

dataSource.implicitCachingEnabled=”true”

dataSource.user=”sa”

dataSource.password=”Passw0rd”

connectionTestQuery=”Select 1″

/>

<Resource name=”jdbc/reports” auth=”Container”

factory=”com.zaxxer.hikari.HikariJNDIFactory”

type=”javax.sql.DataSource”

minimumIdle=”5″

maximumPoolSize=”50″

connectionTimeout=”300000″

driverClassName=”net.sourceforge.jtds.jdbc.Driver”

jdbcUrl=”jdbc:jtds:sqlserver://localhost:1433;databaseName=report;sendStringParametersAsUnicode=false;prepareSQL=3″

dataSource.implicitCachingEnabled=”true”

dataSource.user=”sa”

dataSource.password=”Passw0rd”

connectionTestQuery=”Select 1″/>

 

Password Encryption

To enable password encryption for database connection, change the factory attribute in the Resource tag to “com.zaxxer.hikari.encryption.EncryptedHikariJNDIFactory” and provide the encrypted password in the datasource.password attribute.

Windows Authentication

To enable windows authentication with MSSQL change the jdbcUrl as shown below and prove the suitable domain name-

jdbcUrl=”jdbc:jtds:sqlserver://localhost:1433;databaseName= report11111;domain=xyz”;

set datasource.username=”” and datasource.password=””.

Named Instance

To create a MSSQL connection using a named instance set the jdbcUrl property for both Ephesoft and report database connection configuration as-

jdbcUrl=”jdbc:jtds:sqlserver://localhost:1433;databaseName=ephesoft;instance=instance_name;

Replace the instance_name with the name of the MSSQL service.

Was this article helpful to you?

Engineering

Comments are closed.