Add infrastructure for semantic SQL generation [DATAJDBC-309]

[spring-projects-issues]

Mark Paluch opened DATAJDBC-309 and commented

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)

---

Issue Links:

• DATAJDBC-278 Create vendor specific database dialect to handle ID creation specifics of MS-SQL Server

• DATAJDBC-29 Add pagination abstraction

• DATAJDBC-103 Introduce SQL specification

• DATAJDBC-340 Use infrastructure for semantic SQL generation in Spring Data JDBC

• DATAJDBC-335 Add StatementBuilder's for INSERT, UPDATE, and DELETE

Referenced from: pull request #119

1 votes, 3 watchers

[spring-projects-issues]

Mark Paluch commented

Here's some pseudocode to illustrate the general idea:

DslContext dsl = …

Table person = dsl.table("person").as("p")
Table employer = dsl.table("employer")
dsl.select(dsl.column("firstname", person), dsl.column("id", employer).as("empId"))
  .from(person)
  .join(dsl.inner(employer).on("id").to("employerId", person))
  .orderBy(dsl.asc("firstname", person).nullsFirst());

[spring-projects-issues]

Jens Schauder commented

Wasn't there also the need to have a RETURNING clause for generated ids somewhere?

Also, not needed right now, but in the future:

• Pagination

[spring-projects-issues]

Mark Paluch commented

We had the need for RETURNING (Postgres) in R2DBC but we're solving this on the driver's level