3 Replies Latest reply: Mar 12, 2007 4:38 AM by StephanvanHoof RSS

    Tomcat Datasource

    StephanvanHoof
      Hi,

      I managed to set up a JNDI-datasource on Tomcat5.5 by means of adding to GlobalNamingResources in server.xml:

      <!-- Global JNDI resources -->
      <GlobalNamingResources>

      <Resource
      auth="Container"
      name="jdbc/Geometra"
      type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.OracleDriver"
      url="jdbc:oracle:thin:@X:1521:Y"
      username="X"
      password="Y"
      maxActive="20"
      maxIdle="-1"
      maxWait="-1"
      removeAbandoned="true"
      removeAbandonedTimeout="30"
      />

      The above setup works and seems to use Apache DBCP as connection pool (Tomcat does this automatically for us).

      Now I would like to use the 'Implicit Connection Caching' offered by the 10g Oracle Driver (class: oracle.jdbc.pool.OracleDataSource). Does anyone know how to setup this one on Tomcat?

      Thanks,

      regards
      Stephan
        • 1. Re: Tomcat Datasource
          StephanvanHoof
          FYI: Found it:

          <Resource name="jdbc/orapool" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
          debug="0"/>
          <ResourceParams name="jdbc/orapool">
          <parameter>
          <name>factory</name>
          <value>oracle.jdbc.pool.OracleDataSourceFactory</value>
          </parameter>
          <parameter>
          <name>url</name>
          <value>jdbc:oracle:thin:@oracle-database-host:1521:ORCL</value>
          </parameter>
          <parameter>
          <name>connectionCachingEnabled</name>
          <value>true</value>
          </parameter>
          <parameter>
          <name>connectionCacheName</name>
          <value>dummy1</value>
          </parameter>
          <parameter>
          <name>connectionCacheProperties</name>
          <value>{MinLimit=0, MaxLimit=2, InitialLimit=0}</value>
          </parameter>
          <parameter>
          <name>user</name>
          <value>scott</value>
          </parameter>
          <parameter>
          <name>password</name>
          <value>tiger</value>
          </parameter>
          </ResourceParams>
          • 2. Re: Tomcat Datasource
            559728
            DBCP DataSource does NOT work.

            It will register your DataSource ("jdbc/orapool") using Oracle Connection Pool properties successfully. But it does NOT utilize the underlying connection pooling properly. Do a load test and ask your DBA for sessions from sys.aud$. You will see frequent login/logoff.

            I believe the reason is DBCP doesn't set the connectionCachingEnabled=true properly.

            You have to write a small wrapper around oracle.jdbc.pool.OracleDataSource and pass the required properties.

            I use Spring to hide the DBCP datasource and switch to Oracle DataSource. Remeber both implement javax.sq.DataSource.

            You should leverage the Spring's capability to inject javax.sq.DataSource pointing to MyOracleDataSourceWrapper which extends OracleDataSource.

            public class MyOracleDataSourceWrapper extends OracleDataSource {
                 private static Logger logger = Logger.getLogger(MyOracleDataSourceWrapper.class);

            /**
            * @throws SQLException
            */
            public OracleDataSourceWrapper() throws SQLException {
            super();
            }

            /**
            * Returns a <code>Connection</code>.
            * <code>OracleDataSource.getConnection()</code> will return null
            * if connection cache property "ConnectionWaitTimeout" is exceeded.
            * This overide will throw an exception if the timeout is exceeded.
            *
            * @exception SQLException Thrown if unable to obtain a <code>Connection</code>.
            */
            public Connection getConnection() throws SQLException {  
                 long beginTime = System.currentTimeMillis();
            // If getConnection() returns null, throw a SQLException.
            // super.getConnection() Returns null if blocked on exhausted pool for specified time in configuration.
            Connection connection = super.getConnection();
            if (connection == null) {
            throw new SQLException("No Database Connection after waiting " + (System.currentTimeMillis() - beginTime) + " millis");
            }
            return connection;
            }


            /**
            * Just logs information about the cache properties being set, and calls the super.
            * @see oracle.jdbc.pool.OracleDataSource#setConnectionCacheProperties(java.util.Properties)
            */
            public synchronized void setConnectionCacheProperties(Properties props) throws SQLException {
            if (props == null) {
            throw new IllegalArgumentException("Properties are null");
            }
            logger.info("Enabling connection cache");
            // This method must be called before you set the properties, or the connection pool will use defaults.
            super.setConnectionCachingEnabled(true);
            logger.info("Cache properties:" + props);
            super.setConnectionCacheProperties(props);
            }

            /**
            * Does minimal test of datasource.
            * Gets a connection and the logs the database driver version.
            */
            public void afterPropertiesSet() throws Exception {
            Connection connection = null;
            try {
            connection = getConnection();
            DatabaseMetaData meta = connection.getMetaData();
            logger.info("afterPropertiesSet: ("+ getURL()+"): JDBC driver version is " + meta.getDriverVersion());
            } finally {
            if (connection != null) {
            connection.close();
            }
            }
            }

            }

            applicationContext.xml
            ==================
            <!-- Tomcats JNDI DataSource for Connection Pool (not working for OracleDataSource.It doesn't apply connectionCaching properly. Fix me.-->
            <!--<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">-->
            <!-- <property name="jndiName" value="jdbc/orapool" />-->
            <!-- <property name="resourceRef" value="true" />-->
            <!--</bean>-->

            <!-- Oracle Implicit Connection Pool DataSource -->
            <bean id="dataSource" class="MyOracleDataSourceWrapper" destroy-method="close" init-method="afterPropertiesSet">
            <description> Oracle connection cache based datasource. </description>
            <property name="connectionCachingEnabled" value="true"/>
            <property name="connectionCacheName" value="MyConnectionCache"/>
            <property name="URL"><value>????</value></property>
            <property name="user"><value>????</value></property>
            <property name="password"><value>?????</value></property>
            <property name="connectionCacheProperties">
            <props>
            <prop key="MinLimit">2</prop>
            <prop key="MaxLimit">10</prop>
            <prop key="InitialLimit">2</prop>
            <prop key="InactivityTimeout">300</prop>
            <prop key="ConnectionWaitTimeout">120</prop>
            </props>
            </property>
            </bean>
            • 3. Re: Tomcat Datasource
              StephanvanHoof
              Thanks,

              I am right to conclude that you do not use JNDI but in stead only use Spring for the 'set-up' and control of data access (inject driver and set properties)? So, all DAO's use the Spring functionality to access and get Connections (and NOT JNDI lookup)?

              Thanks
              Regards
              Stephan