I'm using SpringData Reactive Repositories for the first time.
I've been going over the official documentation and I created a basic CRUD API to use them.
I started with H2, just for simplicity, and everything works as expected.
When I try to create a new entity, everything works:
% curl -v -# -X POST http://localhost:8080/wallet/
* Trying ::1:8080...
* Connected to localhost (::1) port 8080 (#0)
> POST /wallet/ HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.77.0
> Accept: */*
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 201 Created
< Content-Type: application/json
< Content-Length: 57
<
* Connection #0 to host localhost left intact
{"id":"6cccd902-01a4-4a81-8166-933b2a109ecc","balance":0}
The code is pretty simple (as usually with SpringData Repositories):
import com.jfcorugedo.reactivedemo.wallet.model.Wallet;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
public interface WalletRepository extends ReactiveCrudRepository<Wallet, String> {
}
And the controller:
import com.jfcorugedo.reactivedemo.wallet.dao.WalletRepository;
import com.jfcorugedo.reactivedemo.wallet.model.Wallet;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.r2dbc.core.R2dbcEntityTemplate;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import reactor.core.publisher.Mono;
import java.math.BigDecimal;
import static org.springframework.data.relational.core.query.Query.query;
@RestController
@RequestMapping("wallet")
@Slf4j
public class WalletController {
private WalletRepository walletRepository;
@Autowired
private R2dbcEntityTemplate template;
public WalletController(WalletRepository walletRepository) {
this.walletRepository = walletRepository;
}
@GetMapping("{id}")
public Mono<ResponseEntity<Wallet>> get(@PathVariable("id") String id) {
return walletRepository
.findById(id)
.map(ResponseEntity::ok)
.defaultIfEmpty(ResponseEntity.notFound().build());
}
@GetMapping("count")
public Mono<ResponseEntity<Long>> count() {
return walletRepository
.count()
.map(ResponseEntity::ok)
.defaultIfEmpty(ResponseEntity.notFound().build());
}
@PostMapping
public Mono<ResponseEntity<Wallet>> create() {
return walletRepository
.save(Wallet.empty())
.map(w -> ResponseEntity.status(201).body(w));
}
@PostMapping("/entityTemplate")
public Mono<ResponseEntity<Wallet>> insert() {
log.info("Inserting using R2dbcEntityTemplate");
return template.insert(new Wallet(null, BigDecimal.ZERO))
.map(ResponseEntity::ok);
}
}
The DTO is also quite simple:
import lombok.AllArgsConstructor;
import lombok.Getter;
import org.springframework.data.annotation.Id;
import java.math.BigDecimal;
@AllArgsConstructor
@Getter
public class Wallet {
@Id
private String id;
private BigDecimal balance;
public static Wallet empty() {
return new Wallet(null, BigDecimal.ZERO);
}
public Wallet withId(String id) {
return new Wallet(id, this.balance);
}
}
Then I check in the documentation that Oracle is also supported.
And I went over the official Oracle driver documentation.
It is true that this driver is under development, so it is not production-ready.
However I clone the repository and I try to execute some tests over my local Oracle instance and everything works well.
Here is the code I execute using Oracle driver directly:
String r2dbcUrl = "r2dbc:oracle://?oracleNetDescriptor="+DESCRIPTOR;
Mono.from(ConnectionFactories.get(ConnectionFactoryOptions.parse(r2dbcUrl)
.mutate()
.option(ConnectionFactoryOptions.USER, USER)
.option(ConnectionFactoryOptions.PASSWORD, PASSWORD)
.build())
.create())
.flatMapMany(connection ->
Mono.from(connection.createStatement(
"INSERT INTO WALLET (ID, BALANCE) VALUES ('" + UUID.randomUUID().toString() + "', 0)")
.execute())
.flatMapMany(result ->
result.map((row, metadata) -> row.get(0, String.class)))
.concatWith(Mono.from(connection.close()).cast(String.class)))
.toStream()
.forEach(System.out::println);
// A descriptor may also be specified as an Option
Mono.from(ConnectionFactories.get(ConnectionFactoryOptions.builder()
.option(ConnectionFactoryOptions.DRIVER, "oracle")
.option(Option.valueOf("oracleNetDescriptor"), DESCRIPTOR)
.option(ConnectionFactoryOptions.USER, USER)
.option(ConnectionFactoryOptions.PASSWORD, PASSWORD)
.build())
.create())
.flatMapMany(connection ->
Mono.from(connection.createStatement(
"SELECT * from wallet")
.execute())
.flatMapMany(result ->
result.map((row, metadata) -> row.get(0, String.class)))
.concatWith(Mono.from(connection.close()).cast(String.class)))
.toStream()
.forEach(System.out::println);
I'm using the code Oracle developers provide in the sample folder.
After executing this code, everything works and a new row is created in my WALLET table.
Finally I try to do the same in SpringData.
I used exactly the same DESCRIPTOR, USER and PASSWORD to connect to Oracle.
This is the configuration class I'm using to get the ConnectionFactory:
package com.jfcorugedo.reactivedemo.config;
import com.jfcorugedo.reactivedemo.wallet.model.Wallet;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;
import org.springframework.data.r2dbc.mapping.event.BeforeConvertCallback;
import reactor.core.publisher.Mono;
import java.util.UUID;
@Configuration
@ConditionalOnProperty(name = "dababase.vendor", havingValue = "oracle")
@Slf4j
public class OracleR2dbcConfig extends AbstractR2dbcConfiguration {
@Value("${database.host:localhost}")
private String host;
@Value("${database.port:1521}")
private int port;
@Value("${database.serviceName}")
private String serviceName;
@Override
@Bean("r2dbcConnectionFactory")
public ConnectionFactory connectionFactory() {
String descriptor = "(DESCRIPTION=" +
"(ADDRESS=(HOST=" + host + ")(PORT=" + port + ")(PROTOCOL=tcp))" +
"(CONNECT_DATA=(SERVICE_NAME=" + serviceName + ")))";
log.info("Creating connection factory with descriptor " + descriptor);
String r2dbcUrl = "r2dbc:oracle://?oracleNetDescriptor="+descriptor;
return ConnectionFactories.get(ConnectionFactoryOptions.parse(r2dbcUrl)
.mutate()
.option(ConnectionFactoryOptions.USER, "jfcorugedo")
.option(ConnectionFactoryOptions.PASSWORD, System.getenv("DB_PASSWORD"))
.build());
}
@Bean
BeforeConvertCallback<Wallet> idGenerator() {
return (entity, table) -> entity.getId() == null ? Mono.just(entity.withId(UUID.randomUUID().toString())) : Mono.just(entity);
}
}
It is pretty similar to the one I used in the other project:
private static final String DESCRIPTOR = "(DESCRIPTION=" +
"(ADDRESS=(HOST="+HOST+")(PORT="+PORT+")(PROTOCOL=tcp))" +
"(CONNECT_DATA=(SERVICE_NAME="+SERVICE_NAME+")))";
...
String r2dbcUrl = "r2dbc:oracle://?oracleNetDescriptor="+DESCRIPTOR;
Mono.from(ConnectionFactories.get(ConnectionFactoryOptions.parse(r2dbcUrl)
.mutate()
.option(ConnectionFactoryOptions.USER, USER)
.option(ConnectionFactoryOptions.PASSWORD, PASSWORD)
.build())
.create())
...
After switching to Oracle, SpringBoot application started without any error:
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.5.3)
2021-08-05 12:48:22.891 INFO 99453 --- [ main] c.j.r.ReactiveDemoApplication : Starting ReactiveDemoApplication using Java 11.0.10 on APM3LC02CH2VNMD6R with PID 99453 (/Users/lp68ba/Developer/personal/reactive-demo/target/classes started by lp68ba in /Users/lp68ba/Developer/personal/reactive-demo)
2021-08-05 12:48:22.892 INFO 99453 --- [ main] c.j.r.ReactiveDemoApplication : No active profile set, falling back to default profiles: default
2021-08-05 12:48:23.165 INFO 99453 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
2021-08-05 12:48:23.214 INFO 99453 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 45 ms. Found 1 R2DBC repository interfaces.
2021-08-05 12:48:23.554 INFO 99453 --- [ main] c.j.r.config.OracleR2dbcConfig : Creating connection factory with descriptor (DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB)))
2021-08-05 12:48:24.129 INFO 99453 --- [ main] o.s.b.web.embedded.netty.NettyWebServer : Netty started on port 8080
2021-08-05 12:48:24.142 INFO 99453 --- [ main] c.j.r.ReactiveDemoApplication : Started ReactiveDemoApplication in 1.466 seconds (JVM running for 2.021)
However now when I try to execute any operation, the connections remains open and nothing happens:
% curl -v -# -X POST http://localhost:8080/wallet/
* Trying ::1:8080...
* Connected to localhost (::1) port 8080 (#0)
> POST /wallet/ HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.77.0
> Accept: */*
>
In the logs of the application I can see this trace:
2021-08-05 13:08:20.735 DEBUG 144 --- [nPool-worker-19] o.s.r2dbc.core.DefaultDatabaseClient : Executing SQL statement [INSERT INTO WALLET (ID, BALANCE) VALUES (:P0_id, :P1_balance)]
However the execution never ends, and nothing is created in the database.
I've tried with both: Spring Data Reactive Repositories and R2DBCEntityTemplate with same result.
I've generated a custom version of the Oracle R2DBC Driver with some traces, and this is what I've got:
Using Oracle R2DBC Driver directly (everything works):
Creating OracleConnectionFactoryImpl with options: ConnectionFactoryOptions{options={driver=oracle, oracleNetDescriptor=(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB))), password=REDACTED, user=jfcorugedo}}
Oracel reactive adapter obtained: oracle.r2dbc.impl.OracleReactiveJdbcAdapter@c33b74f
Datasource obtained: oracle.jdbc.pool.OracleDataSource@696da30b
Creating a new connection
using adatper y datasource to create a new connection
Creating a OracleConnectionImpl with JDBC connection oracle.jdbc.driver.T4CConnection@10f7f7de
createStatement(sql): INSERT INTO WALLET (ID, BALANCE) VALUES ('9a3ab3db-ec38-4544-ac87-4e1a4ad40343', 0)
close()
Using SpringData Reactive Repositories (the connection get stuck and nothing happens):
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.5.3)
2021-08-05 13:12:49.557 INFO 304 --- [ main] c.j.r.ReactiveDemoApplication : Starting ReactiveDemoApplication using Java 11.0.10 on APM3LC02CH2VNMD6R with PID 304 (/Users/lp68ba/Developer/personal/reactive-demo/target/classes started by lp68ba in /Users/lp68ba/Developer/personal/reactive-demo)
2021-08-05 13:12:49.559 INFO 304 --- [ main] c.j.r.ReactiveDemoApplication : No active profile set, falling back to default profiles: default
2021-08-05 13:12:49.849 INFO 304 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
2021-08-05 13:12:49.891 INFO 304 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 38 ms. Found 1 R2DBC repository interfaces.
2021-08-05 13:12:50.208 INFO 304 --- [ main] c.j.r.config.OracleR2dbcConfig : Creating connection factory with descriptor (DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB)))
Creating OracleConnectionFactoryImpl with options: ConnectionFactoryOptions{options={driver=oracle, oracleNetDescriptor=(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB))), password=REDACTED, user=jfcorugedo}}
Oracel reactive adapter obtained: oracle.r2dbc.impl.OracleReactiveJdbcAdapter@5f172d4a
Datasource obtained: oracle.jdbc.pool.OracleDataSource@934b52f
2021-08-05 13:12:50.736 INFO 304 --- [ main] o.s.b.web.embedded.netty.NettyWebServer : Netty started on port 8080
2021-08-05 13:12:50.745 INFO 304 --- [ main] c.j.r.ReactiveDemoApplication : Started ReactiveDemoApplication in 1.417 seconds (JVM running for 4.428)
Creating a new connection
using adatper y datasource to create a new connection
Creating a OracleConnectionImpl with JDBC connection oracle.jdbc.driver.T4CConnection@42dce884
2021-08-05 13:12:54.481 DEBUG 304 --- [nPool-worker-19] o.s.r2dbc.core.DefaultDatabaseClient : Executing SQL statement [INSERT INTO WALLET (ID, BALANCE) VALUES (:P0_id, :P1_balance)]
OracleConnectionImpl#createStatement(sql): INSERT INTO WALLET (ID, BALANCE) VALUES (:P0_id, :P1_balance)
Creating OracleStatementImpl with SQL: INSERT INTO WALLET (ID, BALANCE) VALUES (:P0_id, :P1_balance)
OracleConnectionImpl#close()
I have no idea why the execution is getting stuck with SpringData. The connection seems to be ok and I'm using exactly the same parameters here than I used with Oracle Driver directly.
Does someone have a working example using SpringData R2DBC repositories and Oracle R2DBC Driver?
You can check the code in this repository.
For now, stick with version 0.1.0 of Oracle R2DBC when programming with Spring Data.
The newer versions of Oracle R2DBC implement version 0.9.0.M1 of the R2DBC SPI, which is not currently supported by Spring Data. This was confirmed in a GitHub discussion: https://github.com/oracle/oracle-r2dbc/issues/30#issuecomment-862989986
Once I rolled back to version 0.1.0 of Oracle R2DBC, I was able to get the demo application working. I had to refactor OracleR2dbcConfig.java because support for Oracle Net Descriptors wasn't added until after 0.1.0. A plain URL will work fine to configure a host, port, and service name:
public ConnectionFactory connectionFactory() {
String url =
String.format("r2dbc:oracle://%s:%d/%s", host, port, serviceName);
log.info("Creating connection factory with URL:" + url);
return ConnectionFactories.get(ConnectionFactoryOptions.parse(url)
.mutate()
.option(ConnectionFactoryOptions.USER, user)
.option(ConnectionFactoryOptions.PASSWORD, System.getenv("DB_PASSWORD"))
.build());
}
Also, I had to manually create the table before executing the test with curl:
create table wallet (id VARCHAR2(256), balance NUMBER);
I thought Spring Data usually creates tables automatically, so I'm not sure why I had to do this manually. If the table isn't created, then the INSERT will fail with an error indicating that the wallet table doesn't exist:
ORA-04043: object WALLET does not exist
After these changes, it seems like the curl command is going through ok:
curl -v -# -X POST http://localhost:8080/wallet/
* Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8080 (#0)
> POST /wallet/ HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.64.1
> Accept: */*
>
< HTTP/1.1 201 Created
< Content-Type: application/json
< Content-Length: 57
<
* Connection #0 to host localhost left intact
{"id":"2bcecf46-05eb-46b4-90ec-cfacff2bbaa8","balance":0}* Closing connection 0