One of many largest new options of the not too long ago launched jOOQ 3.15 is its new help for reactive querying by way of R2DBC. This has been a extremely widespread characteristic request, and we lastly delivered on it.
You possibly can proceed utilizing jOOQ the best way you have been used to, offering you with kind secure, embedded SQL in Java, kotlin, or scala, however your question executions are now not blocking. As a substitute, your jOOQ ResultQuery or Question can be utilized as a Writer<R> or Writer<Integer> within the reactive-streams implementation of your selection.
As a substitute of (or along with) configuring your jOOQ DSLContext with a JDBC java.sql.Connection or javax.sql.DataSource, simply configure it with an R2DBC io.r2dbc.spi.Connection or io.r2dbc.spi.ConnectionFactory:
ConnectionFactory connectionFactory = ConnectionFactories.get(
ConnectionFactoryOptions
.parse("r2dbc:h2:file://localhost/~/r2dbc-test")
.mutate()
.choice(ConnectionFactoryOptions.USER, "sa")
.choice(ConnectionFactoryOptions.PASSWORD, "")
.construct()
);
DSLContext ctx = DSL.utilizing(connectionFactory);
Alternatively, use Spring Boot to auto-configure jOOQ like this:
Ranging from this DSLContext, you’ll be able to construct your queries as at all times, however as an alternative of calling the same old blocking execute() or fetch() strategies, you’ll simply wrap the question in a Flux, for instance. Assuming you ran the jOOQ code generator in your H2 INFORMATION_SCHEMA, now you can write:
file Desk(String schema, String desk) {}
Flux.from(ctx
.choose(
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME)
.from(INFORMATION_SCHEMA.TABLES))
// Sort secure mapping from Record2<String, String> to Desk::new
.map(Data.mapping(Desk::new))
.doOnNext(System.out::println)
.subscribe();
jOOQ will purchase an R2DBC Connection out of your ConnectionFactory, and launch it once more after the question has been executed, permitting for optimised useful resource administration, one thing that’s in any other case a bit tough with R2DBC and reactor. In different phrases, the above execution corresponds to this manually written question:
Flux.usingWhen(
connectionFactory.create(),
c -> c.createStatement(
"""
SELECT table_schema, table_name
FROM information_schema.tables
"""
).execute(),
c -> c.shut()
)
.flatMap(it -> it.map((r, m) ->
new Desk(r.get(0, String.class), r.get(1, String.class))
))
.doOnNext(System.out::println)
.subscribe();
Each will print one thing like the next:
Desk[schema=INFORMATION_SCHEMA, table=TABLE_PRIVILEGES] Desk[schema=INFORMATION_SCHEMA, table=REFERENTIAL_CONSTRAINTS] Desk[schema=INFORMATION_SCHEMA, table=TABLE_TYPES] Desk[schema=INFORMATION_SCHEMA, table=QUERY_STATISTICS] Desk[schema=INFORMATION_SCHEMA, table=TABLES] Desk[schema=INFORMATION_SCHEMA, table=SESSION_STATE] Desk[schema=INFORMATION_SCHEMA, table=HELP] Desk[schema=INFORMATION_SCHEMA, table=COLUMN_PRIVILEGES] Desk[schema=INFORMATION_SCHEMA, table=SYNONYMS] Desk[schema=INFORMATION_SCHEMA, table=SESSIONS] Desk[schema=INFORMATION_SCHEMA, table=IN_DOUBT] Desk[schema=INFORMATION_SCHEMA, table=USERS] Desk[schema=INFORMATION_SCHEMA, table=COLLATIONS] Desk[schema=INFORMATION_SCHEMA, table=SCHEMATA] Desk[schema=INFORMATION_SCHEMA, table=TABLE_CONSTRAINTS] Desk[schema=INFORMATION_SCHEMA, table=INDEXES] Desk[schema=INFORMATION_SCHEMA, table=ROLES] Desk[schema=INFORMATION_SCHEMA, table=FUNCTION_COLUMNS] Desk[schema=INFORMATION_SCHEMA, table=CONSTANTS] Desk[schema=INFORMATION_SCHEMA, table=SEQUENCES] Desk[schema=INFORMATION_SCHEMA, table=RIGHTS] Desk[schema=INFORMATION_SCHEMA, table=FUNCTION_ALIASES] Desk[schema=INFORMATION_SCHEMA, table=CATALOGS] Desk[schema=INFORMATION_SCHEMA, table=CROSS_REFERENCES] Desk[schema=INFORMATION_SCHEMA, table=SETTINGS] Desk[schema=INFORMATION_SCHEMA, table=DOMAINS] Desk[schema=INFORMATION_SCHEMA, table=KEY_COLUMN_USAGE] Desk[schema=INFORMATION_SCHEMA, table=LOCKS] Desk[schema=INFORMATION_SCHEMA, table=COLUMNS] Desk[schema=INFORMATION_SCHEMA, table=TRIGGERS] Desk[schema=INFORMATION_SCHEMA, table=VIEWS] Desk[schema=INFORMATION_SCHEMA, table=TYPE_INFO] Desk[schema=INFORMATION_SCHEMA, table=CONSTRAINTS]
Be aware that in the event you’re utilizing JDBC and never R2DBC, you’ll be able to proceed to make use of the jOOQ API along with your reactive streams libraries in a blocking method in precisely the identical approach as above, e.g. in case your favorite RDBMS doesn’t but help a reactive R2DBC driver. At the moment supported drivers in keeping with r2dbc.io embrace:
All of which we integration check with jOOQ 3.15+.
A runnable instance
Go play with the instance right here: https://github.com/jOOQ/jOOQ/tree/major/jOOQ-examples/jOOQ-r2dbc-example
It makes use of the next schema:
CREATE TABLE r2dbc_example.creator (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_author PRIMARY KEY (id)
);
CREATE TABLE r2dbc_example.guide (
id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
title VARCHAR(100) NOT NULL,
CONSTRAINT pk_book PRIMARY KEY (id),
CONSTRAINT fk_book_author FOREIGN KEY (id)
REFERENCES r2dbc_example.creator
);
And runs this code
Flux.from(ctx
.insertInto(AUTHOR)
.columns(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values("John", "Doe")
.returningResult(AUTHOR.ID))
.flatMap(id -> ctx
.insertInto(BOOK)
.columns(BOOK.AUTHOR_ID, BOOK.TITLE)
.values(id.value1(), "Fancy Ebook"))
.thenMany(ctx
.choose(
BOOK.creator().FIRST_NAME,
BOOK.creator().LAST_NAME,
BOOK.TITLE)
.from(BOOK))
.doOnNext(System.out::println)
.subscribe();
To insert two information and fetch the joined outcome as follows:
+----------+---------+----------+ |FIRST_NAME|LAST_NAME|TITLE | +----------+---------+----------+ |John |Doe |Fancy Ebook| +----------+---------+----------+
