How to declare connection properties with ComboPooledDataSource or BasicDataSource

Question

How to declare useFetchSizeWithLongColumn=true with Hibernate? either under com.mchange.v2.c3p0.ComboPooledDataSource or org.apache.commons.dbcp.BasicDataSource in a properties bean configuration file?

I already tried:

<bean id="oracle_prop" lazy-init="false"
      class="org.springframework.util.StringUtils"
      factory-method="collectionToDelimitedString">
    <constructor-arg index="0">
        <list>
            <value>oracle.jdbc.useFetchSizeWithLongColumn=true</value>
        </list>
    </constructor-arg>
    <constructor-arg value=";" index="1" type="java.lang.String"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${database.driver}"/>
    <property name="url" value="${database.url}"/>
    <property name="username" value="${database.username}"/>
    <property name="password" value="${database.password}"/>
    <property name="initialSize" value="100"/>
    <property name="maxIdle" value="100"/>
    <property name="maxActive" value="1000"/>
    <property name="connectionProperties" ref="oracle_prop"/>
</bean>

and

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="properties" ref="dataSourceProperties"></property>
    <property name="driverClass" value="${database.driver}"/>
    <property name="jdbcUrl" value="${database.url}"/>
    <property name="user" value="${database.username}"/>
    <property name="password" value="${database.password}"/>
    <property name="initialPoolSize" value="100"/>
    <property name="minPoolSize" value="100"/>
    <property name="maxPoolSize" value="1000"/>
    <property name="maxIdleTime" value="1800"/> 
    <property name="maxStatements" value="50"/> 
</bean>

<bean id="dataSourceProperties" class="java.util.Properties">
    <constructor-arg>
        <props>
            <prop key="oracle.jdbc.useFetchSizeWithLongColumn">true</prop>
        </props>
    </constructor-arg>
</bean>

and

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${database.driver}"/>
    <property name="jdbcUrl" value="${database.url}"/>
    <property name="properties">
        <props>
            <prop key="user">${database.username}</prop>
            <prop key="password">${database.password}</prop>     
            <prop key="useFetchSizeWithLongColumn">true</prop>                  
        </props>
    </property>
    <property name="initialPoolSize" value="100"/>
    <property name="minPoolSize" value="100"/>
    <property name="maxPoolSize" value="1000"/>
    <property name="maxIdleTime" value="1800"/> 
    <property name="maxStatements" value="50"/> 
</bean>

Dobermaxx99 · Accepted Answer · 2020-10-16T01:51:55.747

0

Workaround

Look for all the long data types and change each manually.

Select * from user_tab_columns c where c.DATA_TYPE = 'LONG';

Sad to say useFetchSizeWithLongColumn will not work everytime. So if you want to use this property, make sure that the LONG columns you are retrieving are not too big or you may run out of memory.

For JPA Hibernate, you could also add @Lob Annotation.

edited Oct 16 '20 at 01:51

answered Apr 01 '15 at 06:52

Dobermaxx99

318
5
16

score 0 · Answer 2 · edited Mar 16 '17 at 21:16

0

<property name="connectionProperties" ref="oracle_prop"/>

This will not work as it will try to bind "object" or "property" type object to connectionProperties where as setConnectionPropeties(String args) expect a string argument. Try changing ref with value and it should inject property as String data type. Else we can also pass properties delimited with ";".

edited Mar 16 '17 at 21:16

Igor Jerosimić

13,621
6
44
53

answered Mar 16 '17 at 20:52

Ravi Sinha

39
1
9

How to declare connection properties with ComboPooledDataSource or BasicDataSource

2 Answers2