Setting up an oracle connection pool using spring in Mule ee 3.2.2

Question

Mule is really neato but the rigid shared xsd stuff with spring never seems to play nice.

The horrible documentation offered up by Mulesoft is continuing to be...Horrible.

I am trying to get a connection pool setup since each query that runs for my service simple takes too long to instantiate. I am forced to fire up 9 queries per service call and the lag from not having a pool affects performance in a bad way. For the dataSource I have tried many different oracle v6 classes but I always seem to get the "cannot write param due to missing setter" error. It's as if the spring property editor impl was forced out of the mulesoft xml schema.

If anyone has been able to make an oracle connection pool work with oracle classes and not c3p0 or dbcp stuff please hook a brother up.

Here is my xml soup.

<spring:beans>
    <context:property-placeholder xmlns:context="http://www.springframework.org/schema/context" location="classpath:somepropfile.properties"></context:property-placeholder>
<spring:bean id="jdbcDataSource" name="jdbcConnectionInfo" class="oracle.jdbc.pool.OraclePooledConnection" doc:name="Bean">
        <spring:property name="url" value="${JDBC.URL}"/>
        <spring:property name="username" value="${JDBC.user}"/>
        <spring:property name="password" value="${JDBC.password}"/>
        <spring:property name="connectionCacheProperties" ref="cacheProperties"/>
    </spring:bean>
<spring:bean id="cacheProperties" name="cacheProps" class="com.erac.obr_mule.appsec.PoolCacheProperties" doc:name="Bean">
        <spring:property name="validateConnection" value="true"/>
        <spring:property name="maxLimit" value="5"/>
        <spring:property name="inactivityTimeout" value="180"/>
        <spring:property name="connectionWaitTimeout" value="120"/>
        <spring:property name="minLimit" value="1"/>
        <spring:property name="initialLimit" value="1"/>
    </spring:bean>   

</spring:beans>

<jdbc:connector name="JDBC" dataSource-ref="jdbcDataSource" queryTimeout="-1" pollingFrequency="0" doc:name="JDBC">
    <jdbc:query key="getMuhDatasHooker" value=" BUNCH O SQL " />
</jdbc:connector>

score 2 · Answer 1 · edited May 23 '17 at 12:27

According to this answer, you're after something like this:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:XE"/>
  <property name="user" value="username"/>
  <property name="password" value="secret"/>
  <property name="minPoolSize" value="5"/>
  <property name="maxPoolSize" value="20"/>
  <property name="acquireIncrement" value="1"/>
  <property name="idleConnectionTestPeriod" value="100"/>
  <property name="maxStatements" value="0"/>
  <property name="checkoutTimeout" value="60000"/>
</bean>

com.mchange.v2.c3p0.ComboPooledDataSource is not a part of the question parameters. Thanks for the alt solution though — branchgabriel, Jun 18 '12 at 19:27

score 1 · Accepted Answer · answered May 04 '12 at 19:17

I went with the dbcp solution. Turned out I was able to find it in our blessed treasure trove of antiquities and use it.

Maybe in 3.3 they will have an oracle only solution

 <spring:bean id="jdbcDataSource" name="jdbcConnectionInfo" class="org.apache.commons.dbcp.BasicDataSource" doc:name="Bean">
        <spring:property name="driverClassName" value="oracle.jdbc.pool.OracleDataSource"/>
        <spring:property name="url" value="${JDBC.URL}"/>
        <spring:property name="username" value="${JDBC.user}"/>
        <spring:property name="password" value="${JDBC.password}"/>
        <spring:property name="initialSize" value="1"/>
        <spring:property name="maxActive" value="9"/>
        <spring:property name="maxIdle" value="180"/>

When the new technologies of the future make this possible I promise to build a time machine and come back and post the oracle only solution. Then I will accept the answer. That is unless someone else beats me. — branchgabriel, May 04 '12 at 19:18

score 0 · Answer 3 · answered Nov 22 '12 at 07:51

use following for oracle connection pool

<bean id="connectionPool1" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="connectionCachingEnabled" value="true" />
<property name="URL">
         <value>ORACLE URL</value>
    </property>
<property name="user">
         <value>user id</value>
    </property>
<property name="password">
         <value>user password</value>
    </property>
<property name="connectionCacheProperties">
      <value>
        MinLimit:1
        MaxLimit:5
        InitialLimit:1
        ConnectionWaitTimeout:120
        InactivityTimeout:180
        ValidateConnection:true
      </value>
   </property>
</bean>

Setting up an oracle connection pool using spring in Mule ee 3.2.2

3 Answers3

Linked