Spring datasource connection manager with jmx to expose real time connections count

Question

Using java/Spring/Ibatis sqlserver is the database and datasource is org.apache.commons.dbcp.BasicDataSource following is the data source object i want to expose real time connection pool count like how many are right now in use and how many in idle and i want to monitor using jmx any quick idea how to implement

<bean id="wssModelDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="net.sourceforge.jtds.jdbcx.JtdsDataSource"/>
    <property name="url" value="com.wss.jdbc.ConnectionUrl=jdbc:jtds:sqlserver://x-x2/x_control_QA;appName=wss;sendStringParametersAsUnicode=false;loginTimeout=20;socketTimeout=180"/>
    <property name="username" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="maxActive" value="10"/>
    <property name="maxWait" value="10000"/>
  </bean>

Answer 1

Yes we can do it, simply enable jmx for your dao class which have jdbcTemplate injected and being used by dao methods and use create public getter methods returning value of following methods and here you go jmx can monitor for you.

http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html

getMinIdle() 
          Returns the minimum number of idle connections in the pool.
 int    getNumActive() 
          [Read Only] The current number of active connections that have been allocated from this data source.
 int    getNumIdle() 

Answer 2

The accepted answer doesn't really tell you how to do this. If you use Spring, a possible solution is to use the MethodNameBasedMBeanInfoAssembler and list the methods on BasicDataSource you require to be exposed. Assuming you have a bean configured with id dataSource add this to your Spring XML config:

<bean id="mbeanServer" class="org.springframework.jmx.support.MBeanServerFactoryBean">
    <property name="locateExistingServerIfPossible" value="true" />
</bean>
<bean id="mbeanExporter" class="org.springframework.jmx.export.MBeanExporter">
    <property name="assembler">
      <bean class="org.springframework.jmx.export.assembler.MethodNameBasedMBeanInfoAssembler">
        <property name="managedMethods">
        <list>
          <value>getNumActive</value>
          <value>getMaxActive</value>
          <value>getNumIdle</value>
          <value>getMaxIdle</value>
          <value>getMaxWait</value>
          <value>getInitialSize</value>
          </list>
        </property>
      </bean>
    </property>
    <property name="beans">
        <map>                   
        <entry key="dataSource:name=DataSource" value-ref="dataSource"/>    
    </map>
    </property>
    <property name="server" ref="mbeanServer" />
    </bean>
</beans>