SQL generation in JDBC and R2DBC relies heavily on pre-compiled or pre-constructed SQL statements that either do not provide sufficient flexibility or impose severe complexity on the actual generation. Furthermore, it gets more and more difficult to consider vendor-specific dialect nuances.
We should ideally provide an API that allows SQL generation on an abstract level (abstract syntax tree) to express the intention behind the statement. The AST is then visited and rendered by dialect-specific components to its final representation.
So far identified requirements:
Support for SELECT, INSERT, UPDATE and DELETE statements.
SELECT: Projection of fields/simple aggregate functions such as COUNT, support for the following clauses: WHERE, JOIN, WHERE, ORDER BY, pagination
INSERT: Field assignments
UPDATE: Assignments, WHERE clause
DELETE: WHERE clause
Some clauses support a wide variety of operators. Our goal is to provide feature coverage for query requirements in repository support and entity mapping. It is not a goal to provide a fully sophisticated query builder API.
The AST API creates an AST object and visitor SPI. We require a follow-up ticket to implement the actual rendering considering dialect-specifics and technology specifics (e.g. native bind markers for R2DBC while JDBC uses questionmark)
DATAJDBC-278 Create vendor specific database dialect to handle ID creation specifics of MS-SQL Server