Improve JDBC Oracle database transaction performance in Apache-Tomcat

Method 1 – Using Only Datasource

 

The standard way to configure JDBC connections in Tomcat is to define a datasource resource in the “context.xml” file as  follows

<Resource  name="jdbc/DBASE" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@hostname:port:sid " username="apps"  password="password" maxActive="20" maxIdle="10" maxWait="-1"/>

              

The above resource can then be accessed in the Java code to get a Oracle Connection as follows:

import java.sql.Connection; import javax.sql.DataSource; import javax.naming.InitialContext; import oracle.jdbc.OracleConnection;       /* Only connection related code snippet mentioned below */   // Initialize the variables Connection conn = null; OracleConnection oracleconn = null;   // Invoke the database InitialContext jndiEnc = new InitialContext(); DataSource ds = (DataSource) jndiEnc.lookup("java:comp/env/jdbc/DBASE"); conn = ds.getConnection(); oracleconn = conn.unwrap(OracleConnection.class);

 

This is the way that you will find described in the Tomcat documentation and on most of the websites on internet.

The problem with the above approach is that it does not initialize the database connections when the server is started (or when the web application is deployed). The connection to the database is made only when the above code is executed. Due to this the performance is slow, which may still be okay for most of the cases. But if you need better performance then the next method is recommended.

 

Method 2 – Using Datasource with Factory

 

Using this method, the tomcat on startup creates database connections which are then readily available to be used in the program. This substantially improves the performance compared to the previous method. The resource configuration in “context.xml” is to be done as follows. The main difference compared to the earlier configuration is to add the “factory” parameter.

 

<Resource   name="jdbc/ETTB_DS"   auth="Container"   type="javax.sql.DataSource"   driverClassName="oracle.jdbc.OracleDriver"   factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"   url="jdbc:oracle:thin:@hostname:port:sid"   username="apps" password="password"   initialSize="5"   maxTotal="15" />

              

The above resource can then be accessed in the Java code to get a Oracle Connection as follows:

import java.sql.Connection; import javax.sql.DataSource; import javax.sql.PooledConnection; import javax.naming.InitialContext; import oracle.jdbc.OracleConnection;     /* Only connection related code snippet mentioned below */   // Initialize the variables Connection conn = null; OracleConnection oracleconn = null;   /* Invoke the database */ InitialContext jndiEnc = new InitialContext(); DataSource ds = (DataSource) jndiEnc.lookup("java:comp/env/jdbc/DBASE"); conn = ds.getConnection();                          /* The Connection object received is an instance of PooledConnection.    Following line converts PooledConnection to OracleConnection. */ oracleconn = (OracleConnection) (Connection) ((PooledConnection) conn).getConnection();

 

 

Conclusion

Use the Method 2 to improve you application performance  while connecting to Oracle Database on Apache-Tomcat.