Rewrite queries using LIKE with wildcards using CONCAT.

[gregturn]

Queries like select e from EmployeeWithName e where e.name like %:partialName% is shorthand for select e from EmployeeWithName e where e.name like :partialName where the bound input value gets wrapped with percentage wildcards. It works when it's the only usage of that parameter in the query.

However if that parameter is used more than once in the query, there is a race condition that can cause the query to get changed into something different.

By replacing this with CONCAT('%', :param, '%'), we are able to use a very widely used standard function across both native and JPA providers to achieve the same effect. This lets us STOP adding wildcards to parameter bindings, and eliminates the race condition seen in places like #2760.

[FrostbittenKing]

Hi, this unfortunately breaks pgjdbc-ng. A jdbc driver that is not very well maintained at the moment, and in the process of beeing transferred to the postgresql organization (impossibl/pgjdbc-ng#588).
This commit: https://github.com/spring-projects/spring-data-jpa/pull/2940/files#diff-8d8e64e6840c6fb5b432751859c56b15861f9367a395d337054bd14c6fd88508 (potentiallyWrapWithWildcards(..)) leads to an obscure error:
Query: (removed) like ('%'||?||'%') escape ''] [operator does not exist: unknown ||?|| unknown] [n/a]; SQL [n/a]

[davemorrissey]

This is a breaking change for Oracle databases because CONCAT supports only two arguments.

The query now contains CONCAT('%', ?1, '%') which results in java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments

[FrostbittenKing]

This is a breaking change for Oracle databases because CONCAT supports only two arguments.

The query now contains CONCAT('%', ?1, '%') which results in java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments

I know this is a hassle, but you could try to rewrite the jpa query by using concat directly. It then skips the jpa code that replaces the like ... with concat ... , ahtough you would probably have to nest 2 concats to get around the oracle oddity of conact only supporting two parameters. https://docs.oracle.com/cd/B13789_01/server.101/b10759/functions022.htm.
Another solution would be for you to patch the class org.hibernate.dialect.function.ConcatPipeFunction. That was what I did too, before I came up with the less intrusive workaround to replace the db driver with the original one, everywhere where it was an option.
Don't know if this is an option. We got lucky, since we only need pgjdbc-ng in one service for one specific feature - async notify from postgres, and additionally this service only uses simple jdbc access without hibernate. In all other services we replaced the driver with the original postgresql jdbc driver, and solved this issue. Good luck with whatever solution you come up.