Manual section about like should document example of concatenating "%" to a bind value

[lukaseder]

To help prevent API misuse and possibly even SQL injection if someone tries to use LIKE in a plain SQL template, we should add a short section showing an example of concatenating "%" to a bind value for use in a LIKE predicate:
https://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/like-predicate/

In addition to:

BOOK.TITLE.like("%abc%")
BOOK.TITLE.notLike("%abc%")

Show also:

BOOK.TITLE.like("%" + value + "%")
BOOK.TITLE.notLike("%" + value + "%")

[steinard]

The reason for extending this page with an example for SQL templating is because this is the page that turns up if you google "JOOQ + like". A simple example could be enough here and you could drop hinting at the many things that can generally go wrong to avoid adding to much noise to this section.

[lukaseder]

Thanks for your feedback, @steinard. I understand that this particular combination of features (LIKE predicate, wrapping the bind value in wild cards, and plain SQL templating) was what bit you.

But if we document this here on the page that you've reached through google, someone else might not find this particular piece of documentation, and they would again not profit from the improvement (e.g. they may be looking for CONCAT, not LIKE). Likewise, a hint about plain SQL templating might be easily overlooked just like the section about the contains() predicate that you hadn't seen on the same page, which could have prevented the string concatenation in your case.

While I agree that hinting at concatenating "%" to bind values in the section about the LIKE predicate is useful (which is what this issue is about), I prefer not to document plain SQL templating here in a short section. I would much rather invest some time in a more thorough review on how to improve the manual in general for people who do not use the code generator.

Two examples:

1. We have now added tabs everywhere where we document the code generator. For example: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-version-providers/ (see also Maven, Gradle, programmatic configuration code should be toggled using tabs #7168). The tabs are there to switch between XML/Maven, Programmatic, Gradle configurations. The tabs store their state globally using a cookie, because someone who uses Gradle will never want to look at another Maven example. I very much prefer to have a similar "mode" that someone who is using jOOQ can select, to specify whether they're using code generation or not, so the examples would all be either using generated code, or using plain SQL templating.
2. As another example: On reddit, someone recently commented that the most confusing thing for them had been the fact that jOOQ's code generator requires a build step, when JPA did not (see here). They recommended that we put comments on a few sections mentioning that jOOQ works this way as opposed to JPA. This is obviously not a problem of using JPA (hand written entities) vs jOOQ (generated code), but of misunderstanding the Maven build lifecycle, where the generate-sources phase is just a normal part of the build, and it needs to be invoked for there to be generated code. We could, of course, add a comment on the particular page this user found via google, but there would be no guarantee that 1) that user would have been helped by this change if we had it in place when they first visited, they might still have overlooked it, 2) that someone else can use this information. In fact, someone who is not fluent in JPA might be lead astray and be confused by the out of context comment about JPA.

We do get a lot of documentation improvement suggestions like this. They tend to suggest a fix the immediate problem for 1 person. But I had much rather invest a bit more time and fix the general problem for everyone. We cannot put a disclaimer about plain SQL templating, JPA, etc. etc. on every single page that can be found on google. However, we can identify more high level use-cases (e.g. people working with code generation vs people working with plain SQL templating), and adapt the entire manual for them. I have created another issue for this: #10667

Does this make sense?

[steinard]

Certainly, it would be great to have this option in the documentation to see all the features in the context of SQL templating.

[lukaseder]

The section will be included with the next build of the manual

[steinard]

Great, thanks!