* Add RawSqlBuilder.withPlaceholders() for CTEs and other complex SQL (#3649)
RawSqlBuilder.parse() uses keyword-based scanning to locate SELECT
columns and the WHERE/HAVING injection points. This fails for CTEs,
window functions and other complex SQL where "select"/"from" keywords
appear in places the scanner doesn't expect.
withPlaceholders(sql) skips SELECT/FROM column parsing entirely and
only locates the ${where}, ${andWhere}, ${having} and ${andHaving}
placeholder positions, requiring explicit columnMapping() calls (as
with unparsed()). This lets dynamic where()/having() expressions be
injected into otherwise unparseable SQL.
Also fixes two bugs in the underlying placeholder-position splitting:
- static SQL following a ${having}/${andHaving} placeholder (e.g. a
trailing ORDER BY) was silently dropped when both a where and a
having placeholder were present
- using only ${having}/${andHaving} (no where placeholder) caused a
dynamically added HAVING clause to be appended after trailing
static SQL, producing invalid SQL
Changes:
- RawSqlBuilder.withPlaceholders(sql) + SpiRawSqlService.withPlaceholders()
- DRawSqlParser.parseAsTemplate() / parseTemplate() - placeholder-position
only parsing, correctly splitting preWhere/preHaving/trailing SQL
- CQueryBuilderRawSql - skip column-list and "select" prefix handling
in template mode (signalled by an empty preFrom)
- Unit tests in DRawSqlServiceTest covering where/andWhere/having/andHaving
placeholder combinations, including the two fixed edge cases
- Integration tests in TestRawSqlWithPlaceholders (ebean-test) covering
CTE queries with dynamic where/having and verifying generated SQL
* Add examples test using query beans
* Add docs guides for RawSql
* Add ${orderBy} ${andOrderBy}
---------
Co-authored-by: robin.bygrave <robin.bygrave@eroad.com>
