3. Oracle Pooling DataSource

3. Oracle Pooling DataSource
Prev	Part II. JDBC Extensions for the Oracle Database	Next

Oracle provides an advanced DataSource implementation that has some unique features. It provides connection pooling and it is required when using "Fast Connection Failover" for RAC.

3.1 Configuration using the traditional <bean> element

We'll start by looking at a very basic DataSource configuration using the traditional <bean> element.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
	   xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"> 
        <property name="URL" value="${jdbc.url}" /> 
        <property name="user" value="${jdbc.username}"/> 
        <property name="password" value="${jdbc.password}"/> 
        <property name="connectionCachingEnabled" value="true"/> 
    </bean>

    <context:property-placeholder location="classpath:jdbc.properties"/> 

</beans>

	Here we specify the `DataSource` implementation class as the `OracleDataSource`.
	We specify the URL using the `URL` property. Note that it is upper case in this implementation while it is lower case in most other `DataSource` implementations.
	The user name is specified using the `user` property.
	The password is specified using the `password` property.
	The connection caching must be enabled explicitly using the `connectionCachingEnabled` property.
	The property place holders will be filled in using this `<context:property-placeholder>` element from the context namespace.

3.2 Using the "orcl" namespace to configure the OracleDataSource

The new "orcl" namespace contains a pooling-data-source element used for easy configuration of the OracleDataSource. We will show several ways this element can be used and we will start with a basic one that can replace the traditional <bean> element configuration used above.

When using the pooling-data-source element connection caching is enabled by default and must explicitly be turned off using the connection-caching-enabled attribute if you don't want to use this pooling support.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl" 
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl 
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource"
        url="${jdbc.url}" username="${jdbc.username}" password="${jdbc.password}"/> 

    <context:property-placeholder location="classpath:jdbc.properties"/> 

</beans>

	Here we specify the reference to the `orcl` schema.
	We also specify the location for the `orcl` schema.
	The properties needed to connect to the database in this example are `url`, `username` and `password`. The `url` property could also be specifiec as `URL` and the `username` property could be specifed as `user`.
	Just as in the previous example, the property place holders will be filled in using this `<context:property-placeholder>` element from the context namespace.

3.3 Using a properties file directly for connection properties

We used a property-placeholder in the previous example to provide connection properties. We can also read the properties directly from a properties file without using placeholders. This is done by using a properties-location attribute specifying the location of the properties file.

	Note
	When you specify properties using a property file there are two basic properties, url and username, where you can use the Oracle property name or the name traditionally used by Spring developers. For url we also accept URL and for username we also accept user.

We will use the following property file named orcl.properties and we will place it at the root of the classpath.

url=jdbc:oracle:thin:@//maui:1521/xe
username=spring
password=spring

Once we have this file in place we can reference it from our pooling-data-source entry and omit the property plceholder declarations for any properties provided in this file.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties"/> 

</beans>

The pooling-datasource with the properties-location specified. The URL, user and password properties will be read from the provided properties file.

You can even remove the properties-location attribute as long as you use the default location and name which is a file named orcl.properties at the root of the classpath.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource"/> 

</beans>

The pooling-datasource without properties or the properties-location specified. We are relying on the default properties file name and location.

3.4 Additional connection and cache properties

It's sometimes necessary to provide additional connection properties to control how the database access is configured. There are several ways you can provide these properties and they are outlined below.

3.4.1 Using the property file for additional connection properties

We can provide additional connection properties by ust adding them to the properties file we used in the example above.

url=jdbc:oracle:thin:@//maui:1521/xe
username=spring
password=spring
processEscapes=false

Any properties specified in addition to the standard URL/url, user/username and password will be used for configuring the OracleDataSource.

We can also use a prefix for the connection properties. This can be useful if the properties file contain other properties like connection cache properties. We will see how these additional properties are used later on.

conn.url=jdbc:oracle:thin:@//maui:1521/xe
conn.username=spring
conn.password=spring
conn.processEscapes=false

The prefix must be specified in the pooling-data-source element configuration. It is specified using the connection-properties-prefix attribute.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn" 
        properties-location="classpath:orcl.properties"/>

</beans>

The connection-properties-prefix is specified here.

3.4.2 Using the property file for additional cache properties

We can also specify connection cache properties in the properties file. We must use a prefix for these connection cache properties to distinguish them from the regular connection properties. In this example we are using "cache" as the prefix.

conn.url=jdbc:oracle:thin:@//maui:1521/xe
conn.username=spring
conn.password=spring
conn.processEscapes=false
cache.InitialLimit=10

The connection cache prefix must be specified using the connection-cache-properties-prefix attribute.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn"
        connection-cache-properties-prefix="cache" 
        properties-location="classpath:orcl.properties"/>

</beans>

The connection-cache-properties-prefix is specified here.

3.4.3 Using "connection-properties" element for additional connection properties

The connection properties can be specified using the connection-properties element.

	Note
	If you specify a `connection-properties` element then any connection properties specified in a property file other than the basic url, username and password will not be used.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:connection-properties>
            processEscapes=false 
        </orcl:connection-properties>
    </orcl:pooling-datasource>

</beans>

The connection properties are specified here.

3.4.4 Using "connection-cache-properties" element for additional cache properties

The connection cache properties can be specified using the connection-cache-properties element.

	Note
	If you specify a `connection-cache-properties` element then any connection cache properties specified in a property file will not be used.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:connection-properties>
            processEscapes=false
        </orcl:connection-properties>
        <orcl:connection-cache-properties>
            InitialLimit=10 
        </orcl:connection-cache-properties>
    </orcl:pooling-datasource>

</beans>

The connection cache properties are specified here.

3.4.5 Using "username-connection-proxy" element for proxy connections

The Oracle JDBC driver provides proxy authentication. This means that you can configure a connection pool using a proxy user account with limited rights. Then during the connection process you would specify the actual username for the end user. This username must be configured to allow a proxy connection through the user proxy ("grant connect through proxyuser"). See ??? for more details on this usage.

Connection proxy authentication is configured using the username-connection-proxy element. You also need to provide a user name provider that implements the ConnectionUsernameProvider interface. This interface has a single method named getUserName that should return the username for the current end user to be connected via the proxy user.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:username-connection-proxy connection-context-provider="usernameProvider"/> 
    </orcl:pooling-datasource>

    <bean id="usernameProvider" class="org.springframework.data.jdbc.test.CurrentUsernameProvider"/>

</beans>

The connection proxy username provider is specified here.

3.5 Summary of configuration options for the "pooling-data-source"

The pooling-data-source element has the following attributes:

Table 3.1. <pooling-data-source> attribute settings

Attribute	Required	Default	Description
`url`	Yes		The url to be used for connecting to the database. Can be provided in a property file. Alternate property name is `URL`
`username`	No		The user name used to connect. Can be provided in a property file. Alternate property name is `user`
`password`	No		The password used to connect. Can be provided in a property file.
`connection-caching-enabled`	No	`true`	Is connection caching enabled?
`fast-connection-failover-enabled`	No	`false`	Is the fast connection failover feature enabled?
`ONS-configuration`	No		The ONS configuration string.
`properties-location`	No		The location of a properties file containing key-value pairs for the connection and connection cache environment using a specific prefix to separate connection cache properties from connection properties (in standard Properties format, namely 'key=value' pairs). If no location is specified a properties file located at `classpath:orcl.properties` will be used if found.
`connection-properties-prefix`	No		The prefix that is used for connection properties provided in the property file.
`connection-cache-properties-prefix`	No		The prefix that is used for connection cache properties provided in the property file.

The pooling-data-source element has the following child elements:

Table 3.2. <pooling-data-source> child elements

Element	Description
`connection-properties`	The newline-separated, key-value pairs for the connection properties (in standard Properties format, namely 'key=value' pairs)
`connection-cache-properties`	The newline-separated, key-value pairs for the connection-cache-properties (in standard Properties format, namely 'key=value' pairs)
`username-connection-proxy`	The configuration of a proxy authentication using a connection context provider

Prev	Up	Next
Overview	Home	4. Fast Connection Failover