14 February 2008

Database Connection pooling with Oracle and Hibernate

We were getting a lot of ORA-00020: maximum number of processes (%s) exceeded, and we were also using apache commons dbcp for connection pooling. I can't say that the oracle error message is at all related to dbcp, but after a little hunting I found that the Oracle jdbc driver natively supports pooling - you don't need a 3rd-party pooling provider. This is how we have our Hibernate datasource set up now (in our Spring application context) -


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
   <property name="dataSource">
        <bean class="oracle.jdbc.pool.OracleDataSource">
            <property name="URL" value="${datasource.url}"/>
            <property name="user" value="${datasource.user}"/>
            <property name="password" value="${datasource.password}"/>
            <property name="connectionCachingEnabled" value="true" />
            <property name="connectionCacheName" value="my_connection_pool" />
            <property name="connectionCacheProperties">
                <value>
                    MinLimit: 5
                    MaxLimit: 25
                    InitialLimit: 5
                </value>
            </property>
        </bean>
    </property>

  ... other stuff ...

</bean>

"oracle.jdbc.pool.OracleDataSource" is shipped with the Oracle driver (ojdbc14.jar). Our friend ORA-00020 hasn't appeared again in the last couple of days, so we might be in luck.

You might argue that the project now has a hard-coded dependency on Oracle - but, honestly, the Universe will die its heat death long before this project switches database.

On a completely separate note, the ${substitutions} are replaced by a Spring PropertyPlaceholderConfigurer at runtime, and we are using a jasypt implementation so we can even encrypt the database password. Nice stuff ...

No comments:

Post a Comment