using tomcat datasource - how to access datasource to get current db pool status by spring jndi

Question

Current Situation

I am doing stress load tests using Jmeter on web and rest api server, but some of transations' response time delays a lot so I am using Spring Aspect to get method processing time. What I couldn't set up is that some procedure calls take too much time, so trying to check DB process time(get con, release con, pure db process time) by write logs with the certain transaction. JMX is not an option as I can't track transactions using it. I just want to leave DB pool status with ThreadContext marked on so that I can check the slow transaction and DB pool status at the same time.

Using DB datasource from Tomcat is not considered here as not want to go DB settings within project files.

Using datasource within Spring project is not an option I am considering at the moment.

Current Setup

Spring project's transaction manager uses Tomcat DBCP pool with Oracle datasource(oracle.jdbc.OracleDriver with javax.sql.DataSource)

applicationContext.xml - DB Setting

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:/comp/env/jdbc/svc"/>
    <property name="resourceRef" value="true"/>
</bean> 

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="mapperLocations" value="classpath*:../sql/**.xml"/>
    <property name="dataSource"><ref bean="dataSource"/></property>
</bean>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg ref="sqlSessionFactory"/>
</bean>

<bean id="oracleTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" p:dataSource-ref="dataSource" />
<bean id="transactionManager" class="com.xxx.xxx.api.transaction.TransactionManager">
    <property name="transactionManagers">
        <list>
            <ref bean="oracleTransactionManager"/>
        </list>
    </property>
</bean>

Trying to do... logging DB pool status

I am trying to use Spring Aspect to write a log whenever functions in certain dao class is called. The log I want to write is like DB Pool status such as

active connection counts
idle connection counts
max active connection setting
max idle connection setting

and so on.

Question

Is it possible to access Tomcat's db pool from spring project? Which would have methods something like this in below.

getNumIdle()
getWaitCount()
getNumActive()

yaswanth yaswanth · Accepted Answer · 2017-07-20T05:36:15

You can simply create a proxy to the tomcatJdbcPoolDataSource and use it as the spring bean. I have created a proxy for C3P0 pooled data source. I later create a spring bean of my class with the required config and use it as a datasource. I believe you can do something similar.

public class C3PODataSourceProxy extends AbstractComboPooledDataSource {

    public C3PODataSourceProxy() {
        super();
    }

    public C3PODataSourceProxy(boolean autoregister) {
        super(autoregister);
    }

    public C3PODataSourceProxy(String configName) {
        super(configName);
    }

    @Override
    public Connection getConnection() throws SQLException {
        try {
            Connection connection = super.getConnection();
            //You can call the below methods and log it, send it to some other class etc
            getNumIdleConnections();
            getNumBusyConnections();
            return connection;
        } catch (Exception exception) {
            //log the exception
            throw exception;
        }
    }

    public Connection getConnection(String username, String password) throws SQLException {

        try {
            Connection connection = super.getConnection(username, password);
               //You can call the below methods and log it, send it to some other class etc
            getNumIdleConnections(username, password);
            getNumBusyConnections(username, password);
            return connection;
        } catch (Exception exception) {
            //log the exception
            throw exception;
        }
    }

}

using tomcat datasource - how to access datasource to get current db pool status by spring jndi

1 Answers