Jooq: Add Settings.renderNamedParamPrefix to support dialect specific named parameter placeholders

[mp911de]

Is it possible to configure vendor-specific bind markers that are used natively by a database instead of JDBC-style (?) bind markers?

I want to use jOOQ as SQL engine for various databases and use the native format (e.g. Postgres with $1, $2, SQL Server @-prefixed named parameters) without implementing Binding types for all dialect combinations.

Relates to #6298 regarding SQL rendering, not the execution part.

Versions:

• Database (include vendor): Postgres, SQL Server, MySQL
• Driver: R2DBC, ADBA

[lukaseder]

Mark, thank you very much for your request.

Unfortunately, this is not possible yet, although I have just recently thought about this in a very similar context, as I was looking into parsing Spring Data JPA annotation content, where bind variables use Spring specific ?1, ?2, ... markers.

Supporting these both in the parser and in the SQL generation would be very useful. Some are already supported through ParamType.

I will think about this. Probably, it should be relatively easy to implement in jOOQ 3.12. As a workaround, you could generate named parameters (which are less ambiguous than ?) and use an ExecuteListener to regex-replace them to your syntax. This could work for a lot of queries, even if not for all special cases.

[mp911de]

Thanks for your prompt response. I wasn't thinking so much about parsing queries, more from the rendering perspective.

Do you have a pointer for an ExecuteListener example for me?

On a related note: For Spring Data, we've introduced BindMarkersFactory to represent various placeholder and binding strategies.

[lukaseder]

Thanks for your prompt response. I wasn't thinking so much about parsing queries, more from the rendering perspective.

Sure, but if we do this in jOOQ, it will need to be consistent across all features.

Do you have a pointer for an ExecuteListener example for me?

Sure, there are some examples in the manual:
https://www.jooq.org/doc/latest/manual/sql-execution/execute-listeners/

And the Javadoc also describes its working:
http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html

Essentially you can hook into the SQL execution lifecycle via the renderEnd() event, and replace the SQL string.

On a related note: For Spring Data, we've introduced BindMarkersFactory to represent various placeholder and binding strategies.

Yes, I've thought about introducing a similar SPI instead of hard coding the various additional options.

[lukaseder]

Independently of my disagreement with R2DBC's support for this or that parameter syntax (see https://groups.google.com/forum/#!topic/r2dbc/z6FcAdr441w), I definitely think that jOOQ should allow for globally overriding the default syntax for bind markers when generating SQL. The current defaults are:

• ? for indexed markers (JDBC syntax)
• :name for named markers (Oracle, Spring, JPA syntax)

It should be possible to generate things like:

• @1 (SQL Server indexed variable)
• ?1 (JPA indexed variable)
• $1 (PostgreSQL indexed variable)
• @name (SQL Server named variable)
• $name (PostgreSQL named variable)

This may be generally useful for jOOQ users, e.g. when passing jOOQ generated SQL directly to an RDBMS

[lukaseder]

Hmm, an SPI will probably be overkill for this relatively simple feature. A single new Settings will suffice.

• Settings.renderNamedParamPrefix

jOOQ already generates indexed named parameters (e.g. @1 or $1 or :1) if ParamType.NAMED is specified and parameters do not have explicit names.

[mp911de]

Just for completeness:

MySQL uses ? as marker.

[lukaseder]

Yes indeed. Well, to be complete, we'd need (in jOOQ's case) a list of all 25 RDBMS :) But this particular issue will just take care of named parameters (I consider $1 a named parameter, where the name is 1)

[lukaseder]

Implemented for jOOQ 3.12. Thanks again for the suggestion!

[mp911de]

Sweet! Thanks for the quick turnaround.

[lukaseder]

Related: Supporting to override the prefix in the parser: #8821

[lukaseder]

The Settings.renderNamedParamPrefix flag must also be taken into account in the plain SQL templating engine. We'll fix this "bug" after releasing 3.12: https://github.com/jOOQ/jOOQ/issues/8822