[spring-data][r2dbc] How to connect to Oracle DB with r2dbc from Application.yml?

Question

application.yml
spring:
   r2dbc:
        url: r2dbc:oracle:thin//{host}:{port}/{service-name}
        username: {username}
        password: {password}

This does not initialise the DB Config.

Overriding also does not work. Config

@Configuration
@EnableR2dbcRepositories(basePackages = {package})
public class DbConfig extends AbstractR2dbcConfiguration
{
@Override
    @Bean
    public ConnectionFactory connectionFactory() {
        return ConnectionFactories.get(ConnectionFactoryOptions.parse(
                {url})
                .mutate()
                .option(ConnectionFactoryOptions.USER, {username})
                .option(ConnectionFactoryOptions.PASSWORD, {password})
                .build());
    }
}

Added these dependencies in build.gradle

implementation group: 'com.oracle.database.r2dbc', name: 'oracle-r2dbc', version: '0.4.0'
implementation group: 'org.springframework.data', name: 'spring-data-r2dbc', version: '1.4.2'

implementation group: 'io.r2dbc', name: 'r2dbc-spi', version: '0.9.1.RELEASE'

This too does not create a connection with R2dbc.

r2dbc-spi is added eventhough it is implicitly present in springframework.data which does not take the latest version which caused a property missing error.

did you include the [oracle r2dbc driver](https://mvnrepository.com/artifact/com.oracle.database.r2dbc/oracle-r2dbc/0.4.0) into your dependencies ? — Olivier Boissé, Mar 08 '22 at 08:51
You can add this annotation `@EnableR2dbcRepositories(basePackages = {package})` in your main application where you put `@SpringBootApplication` and get rid of `DbConfig`. It should work. — nicholasnet, Mar 09 '22 at 04:07
I'm confused. Do you try to use JPA over r2dbc? The two are mutually exclusive. — a better oliver, Mar 09 '22 at 18:33
@nicholasnet I tried with application.yml and the DatabaseClient which I got an error like no Bean found for DatabaseClient even with annotating Autowired. So I had to manually create this connection using annotation of Bean which also does not create the bean in the container. Currently I am using a static method which has the connection and using it in the repository — Joyal Joseph, Mar 10 '22 at 07:01
@abetteroliver sorry added a wrong tag. it is spring-data. Trying to use spring-data and connecting with r2dbc ( using ReactiveCrudRepository & few other ways like R2dbc Fluent Api ) — Joyal Joseph, Mar 10 '22 at 07:03

score 0 · Answer 1 · answered Mar 16 '22 at 07:12

I have not tested the following in Oracle DB, but on MySQL. Because both application configurations and related Java metadata are for general purpose, it should work for Oracle DB too.

Just like JDBC, R2DBC needs to have:

a property class
a connection class that loads properties.

@Configuration
@EnableR2dbcRepositories
public class R2dbcConfig extends AbstractR2dbcConfiguration {

    private R2dbcProperties r2dbcProperties; // 1. a property class R2dbcProperties

    public R2dbcConfig(R2dbcProperties rp) {
        this.r2dbcProperties = rp;
    }

    @Override
    @Bean
    public ConnectionFactory connectionFactory() {
// a connection class that loads properties.
        return ConnectionFactoryBuilder.of(r2dbcProperties,  ()->EmbeddedDatabaseConnection.NONE).build();   
    }

// other configurations...

}

One thing to note, org.springframework.boot.autoconfigure.r2dbc.R2dbcProperties contains basic configurations. There are a lot more OPTIONs that a driver can support, for example, on r2dbc-mysql GH page, it has at least these OPTIONs.

ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
    .option(DRIVER, "mysql")
    .option(HOST, "127.0.0.1")
    .option(USER, "root")
    .option(PORT, 3306)  // optional, default 3306
    .option(PASSWORD, "database-password-in-here") // optional, default null, null means has no password
    .option(DATABASE, "r2dbc") // optional, default null, null means not specifying the database
    .option(CONNECT_TIMEOUT, Duration.ofSeconds(3)) // optional, default null, null means no timeout
    .option(Option.valueOf("socketTimeout"), Duration.ofSeconds(4)) // optional, default null, null means no timeout
    .option(SSL, true) // optional, default sslMode is "preferred", it will be ignore if sslMode is set
    .option(Option.valueOf("sslMode"), "verify_identity") // optional, default "preferred"
    .option(Option.valueOf("sslCa"), "/path/to/mysql/ca.pem") // required when sslMode is verify_ca or verify_identity, default null, null means has no server CA cert
    .option(Option.valueOf("sslCert"), "/path/to/mysql/client-cert.pem") // optional, default null, null means has no client cert
    .option(Option.valueOf("sslKey"), "/path/to/mysql/client-key.pem") // optional, default null, null means has no client key
    .option(Option.valueOf("sslKeyPassword"), "key-pem-password-in-here") // optional, default null, null means has no password for client key (i.e. "sslKey")
    .option(Option.valueOf("tlsVersion"), "TLSv1.3,TLSv1.2,TLSv1.1") // optional, default is auto-selected by the server
    .option(Option.valueOf("sslHostnameVerifier"), "com.example.demo.MyVerifier") // optional, default is null, null means use standard verifier
    .option(Option.valueOf("sslContextBuilderCustomizer"), "com.example.demo.MyCustomizer") // optional, default is no-op customizer
    .option(Option.valueOf("zeroDate"), "use_null") // optional, default "use_null"
    .option(Option.valueOf("useServerPrepareStatement"), true) // optional, default false
    .option(Option.valueOf("tcpKeepAlive"), true) // optional, default false
    .option(Option.valueOf("tcpNoDelay"), true) // optional, default false
    .option(Option.valueOf("autodetectExtensions"), false) // optional, default false
    .build();

[spring-data][r2dbc] How to connect to Oracle DB with r2dbc from Application.yml?

1 Answers1