An update on R2DBC matters. This was really much simpler than I thought. The DSLContext.parsingConnectionFactory() is now implemented for jOOQ 3.15.0-SNAPSHOT for all jOOQ editions, including the jOOQ Open Source Edition. It works just like the DSLContext.parsingConnection(), and profits from the recently implemented translation cache https://github.com/jOOQ/jOOQ/issues/8334 and batch support https://github.com/jOOQ/jOOQ/issues/5757. Named parameters are still a TODO. So, that's exciting news already for your particular use-case.
I've decided not to add a new module for this. R2DBC is a slim SPI that only depends on reactive-streams, which we already have as a dependency. I'm undecided whether R2DBC will be an optional dependency, but I think it's worth having it directly in the core jOOQ module. Now, the big task is to offer full support for R2DBC, which has been very frequently requested and ignored for a long time here: https://github.com/jOOQ/jOOQ/issues/6298 I'm tracking R2DBC specific stuff in a new issue, as #6298 has mixed a lot of concerns and unrelated discussions: https://github.com/jOOQ/jOOQ/issues/11700 So, this will be a top priority for jOOQ 3.15 (along with the planned parser / translator improvements and 4 new dialects). #11700 aims for full support of all the jOOQ goodies on top of R2DBC, including: - A parsing / translating ConnectionFactory - A MockConnectionFactory (I'm sure this will be quite useful for R2DBC in general, given that it is much harder to test / interact with than JDBC) - A DiagnosticsConnectionFactory (not top prio) - Execution of jOOQ ResultQuery statements as Publisher<? extends Record> (already exists, but currently blocking and not passing the TCK) - Execution of jOOQ RowCountQuery statements as Publisher<? extends Integer> (already exists, but currently blocking and not passing the TCK) - RowCountQuery statements that support RETURNING clauses will also offer a Publisher<? extends Record> result - Execution of Batch API as Publisher<? extends Integer> - Execution of UpdatableRecord calls and DAO calls as Publisher<? extends X> (to be investigated) Not all of this might fit in 3.15, but the most important parts definitely will. With 3.15 jOOQ will finally be reactive! Your early feedback will be highly appreciated, of course! Cheers, Lukas On Monday, March 22, 2021 at 10:22:37 PM UTC+1 Lukas Eder wrote: > This really doesn't seem too hard! Here's a quick draft leveraging the > translation capabilities from the jOOQ 3.15.0-SNAPSHOT Professional Edition > to translate Teradata QUALIFY to PostgreSQL (I marked the interesting stuff > in yellow): > > package org.jooq.r2dbc; > > import static org.jooq.impl.DSL.val; > > import java.util.Map; > import java.util.TreeMap; > > import org.jooq.Configuration; > import org.jooq.Param; > import org.jooq.Query; > import org.jooq.SQLDialect; > import org.jooq.conf.ParamType; > import org.jooq.impl.DefaultConfiguration; > > import org.reactivestreams.Publisher; > import org.reactivestreams.Subscriber; > import org.reactivestreams.Subscription; > > import io.r2dbc.postgresql.PostgresqlConnectionConfiguration; > import io.r2dbc.postgresql.PostgresqlConnectionFactory; > import io.r2dbc.spi.Batch; > import io.r2dbc.spi.Connection; > import io.r2dbc.spi.ConnectionFactory; > import io.r2dbc.spi.ConnectionFactoryMetadata; > import io.r2dbc.spi.ConnectionMetadata; > import io.r2dbc.spi.IsolationLevel; > import io.r2dbc.spi.Result; > import io.r2dbc.spi.Statement; > import io.r2dbc.spi.TransactionDefinition; > import io.r2dbc.spi.ValidationDepth; > import reactor.core.publisher.Flux; > > public class X { > > public static void main(String[] args) { > PostgresqlConnectionConfiguration config = > PostgresqlConnectionConfiguration.builder() > .username("postgres") > .password("test") > .host("localhost") > .database("postgres") > .build(); > ConnectionFactory f = new PostgresqlConnectionFactory(config); > > Flux.from(new X(f).factory().create()) > .flatMap(connection -> connection > .createStatement( > "select row_number() over w, table_schema, table_name " > + "from information_schema.tables " > + "window w as (order by table_schema, table_name) " > + "qualify row_number() over w between :1 and :2 " > + "order by 1, 2 ") > .bind(1, 6) > .bind(2, 10) > .execute() > ) > .flatMap(it -> it.map((a, b) -> String.format("%1$5s: > %2$s.%3$s", a.get(0), a.get(1), a.get(2)))) > .collectList() > .block() > .stream() > .forEach(System.out::println); > } > > final Configuration configuration; > final ConnectionFactory delegate; > > public X(ConnectionFactory delegate) { > this.configuration = new DefaultConfiguration() > .set(SQLDialect.POSTGRES); > this.configuration > .settings() > .withRenderNamedParamPrefix("$") > .withParamType(ParamType.NAMED); > this.delegate = delegate; > } > > ConnectionFactory factory() { > return new ConnectionFactory() { > > @Override > public ConnectionFactoryMetadata getMetadata() { > return () -> "jOOQ"; > } > > @Override > public Publisher<? extends Connection> create() { > Publisher<? extends Connection> connection = > delegate.create(); > > return subscriber -> { > connection.subscribe(new Subscriber<Connection>() { > @Override > public void onSubscribe(Subscription s) { > subscriber.onSubscribe(s); > } > > @Override > public void onNext(Connection connection) { > subscriber.onNext(new Connection() { > @Override > public Publisher<Void> beginTransaction() { > return connection.beginTransaction(); > } > > @Override > public Publisher<Void> > beginTransaction(TransactionDefinition definition) { > return > connection.beginTransaction(definition); > } > > @Override > public Publisher<Void> close() { > return connection.close(); > } > > @Override > public Publisher<Void> commitTransaction() > { > return connection.commitTransaction(); > } > > @Override > public Publisher<Void> > createSavepoint(String name) { > return > connection.createSavepoint(name); > } > > @Override > public boolean isAutoCommit() { > return connection.isAutoCommit(); > } > > @Override > public ConnectionMetadata getMetadata() { > return connection.getMetadata(); > } > > @Override > public IsolationLevel > getTransactionIsolationLevel() { > return > connection.getTransactionIsolationLevel(); > } > > @Override > public Publisher<Void> > releaseSavepoint(String name) { > return > connection.releaseSavepoint(name); > } > > @Override > public Publisher<Void> > rollbackTransaction() { > return > connection.rollbackTransaction(); > } > > @Override > public Publisher<Void> > rollbackTransactionToSavepoint(String name) { > return > connection.rollbackTransactionToSavepoint(name); > } > > @Override > public Publisher<Void> > setAutoCommit(boolean autoCommit) { > return > connection.setAutoCommit(autoCommit); > } > > @Override > public Publisher<Void> > setTransactionIsolationLevel(IsolationLevel isolationLevel) { > return > connection.setTransactionIsolationLevel(isolationLevel); > } > > @Override > public Publisher<Boolean> > validate(ValidationDepth depth) { > return connection.validate(depth); > } > > @Override > public Batch createBatch() { > // TODO > throw new > UnsupportedOperationException(); > } > > @Override > public Statement createStatement(String > input) { > return new Statement() { > Map<Integer, Param<?>> params = > new TreeMap<>(); > > @Override > public Statement add() { > // TODO > throw new > UnsupportedOperationException(); > } > > @Override > public Statement bind(int index, > Object value) { > params.put(index, val(value)); > return this; > } > > @Override > public Statement bind(String name, > Object value) { > // TODO > throw new > UnsupportedOperationException(); > } > > @Override > public Statement bindNull(int > index, Class<?> type) { > params.put(index, val(null, > type)); > return this; > } > > @Override > public Statement bindNull(String > name, Class<?> type) { > // TODO > throw new > UnsupportedOperationException(); > } > > @Override > public Publisher<? extends Result> > execute() { > Query query = > configuration.dsl().parser().parseQuery(input, params.values().toArray()); > Statement statement = > connection.createStatement(query.getSQL()); > > int i = 0; > for (Param<?> o : > query.getParams().values()) > if (!o.isInline()) > if (o.getValue() == > null) > > statement.bindNull(i++, o.getType()); > else > > statement.bind(i++, o.getValue()); > > return statement.execute(); > } > }; > } > }); > } > > @Override > public void onError(Throwable t) { > subscriber.onError(t); > } > > @Override > public void onComplete() { > subscriber.onComplete(); > } > }); > }; > } > }; > } > } > > > The output being: > > 6:information_schema.administrable_role_authorizations > 7:information_schema.applicable_roles > 8:information_schema.attributes > 9:information_schema.character_sets > 10:information_schema.check_constraint_routine_usage > > > On Mon, Mar 22, 2021 at 9:17 PM Lukas Eder <lukas...@gmail.com> wrote: > >> Here we go. As I said, I think we should offer this out of the box, and >> integration test it: https://github.com/jOOQ/jOOQ/issues/11700. It's a >> rather low hanging fruit, much lower than executing jOOQ queries on R2DBC. >> I'll look into this later this week. >> >> On Mon, Mar 22, 2021 at 9:08 PM Lukas Eder <lukas...@gmail.com> wrote: >> >>> Search "(?<!:):\\w+" >>> Replace "?" >>> 😉 >>> >>> But it's a great hint. We should supprt that OOTB. At the time, I was >>> trying to get the R2DBC folks not to diverge too far from JDBC and accept ? >>> as well, but I failed. >>> >>> Am Montag, 22. März 2021 schrieb Magnus Persson <magnus.e...@gmail.com>: >>> >>>> It almost gets me all the way :) >>>> >>>> The input sql has named parameters, as in "SELECT c1, c2 FROM foo WHERE >>>> bar = :baz OR quz = :qux". I would like to have that rendered as "SELECT >>>> c1, c2 FROM foo WHERE bar = $1 OR quz = $2". The intention is to use jooq >>>> as an sql parser and executing it with r2dbc-postgres. >>>> >>>> On Monday, March 22, 2021 at 1:26:23 PM UTC+1 lukas...@gmail.com wrote: >>>> >>>>> Hi Magnus, >>>>> >>>>> You can set Settings.paramType = ParamType.NAMED (this would produce >>>>> :1, :2 if you don't actually provide any named parameters), and then >>>>> Settings.renderNamedParamPrefix = "$" to replace the default prefix ":" >>>>> by >>>>> "$". >>>>> >>>>> I hope this helps, >>>>> Lukas >>>>> >>>>> On Sat, Mar 20, 2021 at 5:01 PM Magnus Persson <magnus.e...@gmail.com> >>>>> wrote: >>>>> >>>>>> I'm using the jooq sql parser with the intention of outputting sql >>>>>> and bind values. Unfortunatly the sql coming out of jooq has either >>>>>> named >>>>>> ":param" or indexed by "?". How would I go about having jooq output >>>>>> sequenced index markers such as "$1", "$2" etc? >>>>>> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "jOOQ User Group" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to jooq-user+...@googlegroups.com. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/jooq-user/8405b074-cb9e-4be5-9f2d-f8bc74d2fde8n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/jooq-user/8405b074-cb9e-4be5-9f2d-f8bc74d2fde8n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "jOOQ User Group" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to jooq-user+...@googlegroups.com. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/dc4f4015-c14b-4e0c-953b-9720b3bfb347n%40googlegroups.com.