It would be nice to support Oracle UCP pooling. The reason for it is that Oracle is in very widespread use in enterprise environments. UCP is the preferred way to pooling with Oracle (the built-in pooling in the driver is deprecated). UCP adds additional enterprise-grade capabilities, such as support for ONS (Oracle Notification Service), FCF (Fast Connection Failover), enhanced JMX-introspection, RAC support etc.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjucp/intro.html#GUID-8CF6555B-E7EA-462C-88C8-78678E43BD17
But given the comments in: https://github.com/spring-projects/spring-boot/issues/8700 this might not be acceptable, as I guess access to the Oracle Maven repo would be required (if not dealing with it all through reflection, which is not ideal).
WDYT?
If not directly in spring boot due to the above challenge with repos, would https://projects.spring.io/spring-data-jdbc-ext/ be a better place? But I do not see any specific boot support in spring-data - and also the project does not seem very active with releases etc.
Boot is used a lot in enterprise envs, and supporting enterprise requirements and best-practice patterns in the framework seems sane to me.
Comment From: wilkinsona
I guess access to the Oracle Maven repo would be required
Yes, I think it would and that's not something that we want Spring Boot's build to rely upon. Spring Data JDBC extensions would certainly be a better place than Spring Boot for this functionality, although I'm not sure that it's the right place. Perhaps @trisberg can offer some guidance?
Comment From: fabio-grassi-gbs
Oracle JDBC drivers have been added to Maven Central as described in this blog.
I kindly suggest to consider reopening this issue and implement it.
Comment From: wilkinsona
Good point, @fabio-grassi-gbs. No promises at this point, but I'm going to re-open this so that we can take another look at what it would entail now that the Maven Central issue is no longer a problem.
Comment From: wilkinsona
UCP's license is unusual (Oracle Free Use Terms and Conditions (FUTC)). It's unclear to me whether open source usage is permitted and whether or not it's compatible with Apache V2. We can't proceed here until this has been clarified.
Comment From: wilkinsona
Legal have looked at the licence and are happy for us to proceed.
Comment From: fabio-grassi-gbs
Great, thanks. I'll be glad to test it as soon as available.
Comment From: davidkarlsen
Anything we can do to help out here @wilkinsona ?
Comment From: wilkinsona
@davidkarlsen Thanks for the offer. If you have experience with using and configuring UCP, then I'm almost certain that you can help. Perhaps you could share how you typically configure UCP in a Spring Boot application and we can then see how that might fit with our existing DataSource auto-configuration and configuration properties?
Comment From: davidkarlsen
See below code for typical config. I also have an implementation of DataSourcePoolMetadata for UCP which I could offer.
package com.org.somepackage
import java.sql.SQLException
import javax.annotation.PreDestroy
import javax.sql.DataSource
import oracle.jdbc.pool.OracleDataSource
import oracle.ucp.UniversalConnectionPoolException
import oracle.ucp.admin.UniversalConnectionPoolManagerImpl
import oracle.ucp.jdbc.PoolDataSourceFactory
import org.springframework.boot.context.properties.ConfigurationProperties
import org.springframework.boot.context.properties.EnableConfigurationProperties
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.context.annotation.Primary
import org.springframework.jdbc.datasource.DataSourceTransactionManager
import org.springframework.transaction.PlatformTransactionManager
@Configuration
@EnableConfigurationProperties(DataSourceConfigProperties::class)
class DataSourceConfig {
// Do *NOT* use SQLForValidateConnection - bad for performance, Oracle has build in mech instead:
// https://docs.oracle.com/database/121/JJUCP/connect.htm#JJUCP8136
@PreDestroy
@Throws(UniversalConnectionPoolException::class)
fun shutdown() {
for (poolName in UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager()
.connectionPoolNames) {
UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager()
.destroyConnectionPool(poolName)
}
}
@Bean
@Throws(SQLException::class, UniversalConnectionPoolException::class)
fun dataSource(dataSourceConfigProperties: DataSourceConfigProperties): DataSource {
shutdown() // needed after 12.0.2 - bug or feature - nevertheless...
val poolDataSource = PoolDataSourceFactory.getPoolDataSource()
poolDataSource.connectionPoolName = "SOMEUNIQUEPOOLNAME"
poolDataSource.connectionFactoryClassName = OracleDataSource::class.java.name
poolDataSource.minPoolSize = dataSourceConfigProperties.minPoolSize
poolDataSource.maxPoolSize = dataSourceConfigProperties.maxPoolSize
poolDataSource.initialPoolSize = dataSourceConfigProperties.initialPoolSize
poolDataSource.validateConnectionOnBorrow = dataSourceConfigProperties.validateConnectionOnBorrow
poolDataSource.maxConnectionReuseTime = dataSourceConfigProperties.maxConnectionReuseTimeSec
poolDataSource.inactiveConnectionTimeout = dataSourceConfigProperties.inactiveConnectionTimeoutSec
poolDataSource.abandonedConnectionTimeout = dataSourceConfigProperties.abandonedConnectionTimeout
poolDataSource.timeToLiveConnectionTimeout = dataSourceConfigProperties.timeToLiveConnectionTimeout
poolDataSource.connectionWaitTimeout = dataSourceConfigProperties.connectionWaitTimeout
poolDataSource.onsConfiguration = dataSourceConfigProperties.onsConfiguration
poolDataSource.fastConnectionFailoverEnabled = dataSourceConfigProperties.fastConnectionFailoverEnabled
poolDataSource.maxStatements = dataSourceConfigProperties.maxStatements
poolDataSource.url = dataSourceConfigProperties.url
poolDataSource.user = dataSourceConfigProperties.user
poolDataSource.password = dataSourceConfigProperties.password
poolDataSource.loginTimeout = dataSourceConfigProperties.loginTimeout
poolDataSource.connectionProperties = dataSourceConfigProperties.connectionProperties
return poolDataSource
}
@Bean
@Primary
@ConfigurationProperties("transaction-manager")
fun transactionManager(dataSource: DataSource): PlatformTransactionManager {
return DataSourceTransactionManager(dataSource)
}
}
Comment From: wschlich
If not directly in spring boot due to the above challenge with repos, would https://projects.spring.io/spring-data-jdbc-ext/ be a better place? But I do not see any specific boot support in spring-data - and also the project does not seem very active with releases etc.
It seems spring-data-jdbc-ext is dead. Citing https://github.com/spring-projects/spring-data-jdbc-ext
NOTICE: This project has been archived as of March 27th, 2019. There will be no maintenance or new development going forward.
Does anyone know whether the functionality implemented by the RacRetryOperationsInterceptor + RacFailoverRetryPolicy is implemented elsewhere now?
https://github.com/spring-projects/spring-data-jdbc-ext/tree/master/spring-data-oracle/src/main/java/org/springframework/data/jdbc/retry/oracle
We'd like to use Oracle UCP + RAC Fast Connection Failover (FCF) with Spring Data JPA (not with Spring Boot, I have to admit -- I just found this issue while researching).
Comment From: davidkarlsen
@wilkinsona any comments?
Comment From: wilkinsona
@davidkarlsen Thanks for your patience and for the configuration example. The configuration looks like quite a good fit for Spring Boot's existing spring.datasource.* properties for things like the URL, username, and password. We'd then have UCP-specific properties (spring.datasource.ucp or perhaps spring.datasource.oracleucp) that are bound to the PooledDataSource instance.
Comment From: snicoll
Closing in favour of PR #23403