How do I set Oracle specific JDBC connection properties in bean def?

[nweddle]

I am using DBCP and Oracle, and I need to set some Oracle specific properties on the conneciton. I have had no luck finding any documentation on this. BasicDataSource has a method called addConnectionProperty.


Any help would be appreciated! Thanks!!
Nathan

[TrevorIngs]

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
		<property name="url" ref="jdbc:oracle:thin:@host:port:env"/>
		<property name="username"><value>xxx</value></property>
		<property name="password"><value>xxx</value></property>
	</bean>

[nweddle]

Thanks for the reply. I already have my DBCP properteis set up, similar to the code you provided. (I was not able to post my code snippet)

My datasource is working properly, I just need to know how to set a custom Oracle property. For example:

in Java:
Properties props = new Properties();
props.set("oracle.jdbc.someproperty", "true");
conn.setConnectionProperties(props);

How can I do this in the bean definition?
Thanks!

[lumpynose]

It's not pretty, but many of the jdbc drivers allow you to add properties on the connection url. Separated by question marks, ampersands or whatever. So check the Oracle driver's docs.

[TrevorIngs]

Check documentation for org.springframework.jdbc.datasource.AbstractDriver BasedDataSource, it would imply that you can set custom properties on your datasource via

<property name="connectionProperties">
   <props>
       <prop key="com.oracle.xxxx" value="xxxx"/>
   </props>
</property>

[majoux]

Hi Nathan,
I have the same issue. Were you able to find out a solution???
I tried in vain to use a solution like:
<property name="connectionProperties">
<props>
<prop key="v$session.program" value="xxxx"/>
</props>
</property>
Thanks.
Maj

[cthuang]

I switched to c3p0 which allows you to add a hook when creating a connection.

  <bean
      id="dataSource"
      class="com.mchange.v2.c3p0.ComboPooledDataSource"
      destroy-method="close">
    <property name="driverClass" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="preferredTestQuery" value="${jdbc.validationQuery}"/>
    <property
        name="connectionCustomizerClassName"
        value="com.example.OracleConnectionCustomizer"/>
  </bean>

The onAcquire method is called after each connection is created.

public class OracleConnectionCustomizer extends AbstractConnectionCustomizer {
    
    @Override
    public void onAcquire(
            Connection connection, String parentDataSourceIdentityToken)
        throws Exception
    {
        // Follow synonyms when retrieving metadata.
        ((OracleConnection) connection).setIncludeSynonyms(true);
    }
}