Search code examples
springsingletonrefreshjavabeansapache-commons-dbcp

Spring MVC refresh database beans in application context

I am developping a Spring MVC web application that use the dbcp database connection pool.

<bean id="datasourceAR_XXX" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton">
        <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
        <property name="url"><value>jdbc:oracle:thin:@XXX.XXX.com:1500:SERVICE</value></property>
        <property name="maxActive"><value>100</value></property>
        <property name="maxIdle"><value>10</value></property>
        <property name="username"><value>XXX</value></property>        
        <property name="password"><value>XXX</value></property>
</bean>

I recently moved the scope of those beans to singleton because the amount of connection per session started to be a bit too much.

The problem is :

Our database is shutting down every sunday and the spring application seems to act strangely by keeping the socket open and does not refresh the connection as I thought it would do.

Is there a way to refresh the beans scoped as singleton in a way that will refresh the connection everyday and not be obliged to relaunch the application every monday?

Solution

  • What you want to do is to configure validation for your connections. When a connection is borrowed from the pool you want to make sure that that connection is valid. For this you can specify the validationQuery property on your datasource.

    <bean id="datasourceAR_XXX" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc:oracle:thin:@XXX.XXX.com:1500:SERVICE</value></property>
    <property name="maxActive"><value>100</value></property>
    <property name="maxIdle"><value>10</value></property>
    <property name="username"><value>XXX</value></property>        
    <property name="password"><value>XXX</value></property>
    <property name="validationQuery" value="select 1 from dual" />
</bean>

    See DBCP - validationQuery for different Databases for a list of possible validation queries for different databases.

    There are some issues with Commons DBCP and it is pretty old (although there is a DBCP 2.x now). I would suggest moving to a different datasource like HikariCP this datasource is also a JDBC 4.x based datasource which allows for easier connection validation (it is part of the JDBC 4 spec).

    <bean id="datasourceAR_XXX" class="com.zaxxer.hikari.HikariDataSource">
    <property name="datasourceClassName" value="oracle.jdbc.pool.OracleDataSource"/>
    <property name="maximumPoolSize" value="20" />
    <property name="username" value="XXX" />
    <property name="password" value="XXX" />
    <property name="datasourceProperties">
        <props>
            <prop key="serverName">XXX.XXX.com</prop>
            <prop key="port">1500</prop>
            <prop key="databaseName">SERVICE</prop>             
        </props>
    </property>
</bean>

    If your oracle driver is new enough you don't need a validation query anymore as validation is provided by the driver instead of needing to be done with a query. Next to that you probably have better results with this pool.

    Also you might have a bit of a large pool size, nice article/presentation about pool sizing can be found here.