Problems with Tomcat8 using connection pooling to OracleDB

Question

We have an application provided by a 3rd party vendor that runs on Tomcat 8 and JDK 8 to an Oracle 12 DB with ojdbc7.jar & xdb6.jar driver. The application works, but is slower than expected. When investigating it appears the application is configured to use connection pooling, but it appears the application is creating new connections per query, and not using any of the initially created connections to the Database.

Unfortunately, I don't have access to the code of the 3rd party app. But, hoping for an idea on what I am missing in the Tomcat setting to have pooling work.

I've tried going through Apache's documentation for the older Oracle connections, and trying other options found on the web.

<Resource name="jdbc/DataSource" auth="Container"
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@localhost:1521:XE"
        username="myProxyUser" password="myPassword"
        initialSize="5" maxTotal="100" maxIdle="-1"
        maxWaitMillis="30000"
        validationQuery="select 1 from dual"
        testOnBorrow="true"
        accessToUnderlyingConnectionAllowed = "true"
        connectionProperties="defaultRowPrefetch=100"
        removeAbandoned = "true"
        removeAbandonedTimeout = "30"/>

Are you sure the application uses 'jdbc/DataSource' for JNDI rosolution? Check the web.xml and context.xml. Refer: https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html — Ironluca, Aug 19 '19 at 15:15
In the web.xml i did see the excerpt below. (*modified a couple field values ) 3rdParyCo Datasource jdbc/DataSource javax.sql.DataSource Container — rainmakers99, Aug 19 '19 at 18:23
The resource name declared in tomcat context.xml file needs to be the same as the one your 3rd party is using to resolve the data source from JNDI, like 'DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres" );' have you checked the 3rd party documentation/web.xml/context.xml if the name is 'jdbc/DataSource'? You have to use the same name in your configuration as the application expects. — Ironluca, Aug 20 '19 at 08:33

score -1 · Answer 1 · answered Aug 19 '19 at 13:39

You can check tomcat docs, mainly use factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

example on how to configure a resource for JNDI lookups

<Resource name="jdbc/TestDB"
      auth="Container"
      type="javax.sql.DataSource"
      factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
      testWhileIdle="true"
      testOnBorrow="true"
      testOnReturn="false"
      validationQuery="SELECT 1"
      validationInterval="30000"
      timeBetweenEvictionRunsMillis="30000"
      maxActive="100"
      minIdle="10"
      maxWait="10000"
      initialSize="10"
      removeAbandonedTimeout="60"
      removeAbandoned="true"
      logAbandoned="true"
      minEvictableIdleTimeMillis="30000"
      jmxEnabled="true"
      jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
        org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
      username="root"
      password="password"
      driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/mysql"/>

I tried your suggestion, but with the edits for an Oracle connection vs MySql. But no dice. — rainmakers99, Aug 19 '19 at 18:18

score -1 · Answer 2 · answered Jul 15 '20 at 14:26

-1

you can specify pooling by defining type and factory

type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

answered Jul 15 '20 at 14:26

Smalcat

231
3
18

Problems with Tomcat8 using connection pooling to OracleDB

2 Answers2