Support Criteria with SqlIdentifier (supporting "quoted" column names)

[nt-gt]

Hi,

Currently, Criteria only supports "unquoted" SqlIdentifiers - this occurs because the API only accepts a String and then passes it on to SqlIdentifier.unquoted().

I have a use-case where I would like to reuse the Criteria API but need to construct SqlIdentifier (I need support for quoted and composite). This would enable to better compose a custom add-on on top of the R2DBC repositories as I would be able to reuse the same Criteria class for filtering (rather than having to "invent my own").

[mp911de]

This is a neat enhancement. Moving this ticket into Spring Data JDBC as the Criteria API is provided by Spring Data Relational.

In general, the criteria API is based on property names so referring to a property name will translate the referenced property into a proper SqlIdentifier.

[nt-gt]

In general, the criteria API is based on property names so referring to a property name will translate the referenced property into a proper SqlIdentifier.

"property" as in a "Java field"?

If so, I find it difficult to see how Criteria can be generalized to work in a context where a JOIN is involved if we assume the SqlIdentifier should expand to a java field. I know R2DBC does not support this currently JOINs (and I am not trying to solve the 1-N JOINs)

[mp911de]

Property as in domain model property. Properties can be backed either by fields or by using property accessors (getter/setter).

[nt-gt]

My argument is rather - the property name does not match the @Column name, so how will this work when JOINs are in play?

Consider a case like this:

@Data
@Table("location")
public class Location {
   private Long id;

   @Column("street_name")
   private String streetName;
}

SELECT ... 
  FROM order order
  JOIN location AS pickup_location ON order.pickup_location_id = location.id
  JOIN location AS delivery_location ON order.delivery_location_id = location.id
  WHERE ...

If I wanted to do a Criteria based on the streetName of the delivery_location, I would have expected to use an SqlIdentifier.from(SqlIdentifier.unquoted("delivery_location"), SqlIdentifier.unquoted("street_name")).

But that does not match that Criteria is using property name. So would that instead be SqlIdentifier.from(SqlIdentifier.unquoted("delivery_location"), SqlIdentifier.unquoted("streetName"))? How would Spring know that delivery_location is in fact the Location model in this case?

[mp911de]

It pretty much depends on who creates the SQL statement. In the case of Spring Data JDBC, it's our components that are aware of properties and property paths (order.location.id). From that perspective, Criteria isn't required to be aware of an SQL identifier as the query mapper resolves properties/property paths to a SqlIdentifier.

[nt-gt]

It pretty much depends on who creates the SQL statement. In the case of Spring Data JDBC, it's our components that are aware of properties and property paths (order.location.id). From that perspective, Criteria isn't required to be aware of an SQL identifier as the query mapper resolves properties/property paths to a SqlIdentifier.

Thinking more about this, I think my source of confusion is in fact that Criteria has SqlIdentifiers but they are not used as SqlIdentificers (don't know if it is specific to R2DBC - it is my only point of reference).

The documentation for SqlIdentificer says Represents a named object that exists in the database like a table name or a column name. That phrase makes me feel that it is a disconnect logically for Criteria to "reuse" SqlIdentificer as a "Property path" - the first seems to work on SQL level and the other on the JVM level.

At the implementation level of R2DBC, I can see that the important aspect is whether there is a RelationalPersistentEntity associated with the SqlIdentificer - if there is one, then the SqlIdentificer is interpreted as a "Property path" and otherwise it is interpreted as a "classic" SQL identificer (AFAIUI).

Having dug through the Spring code code, I think I will also hit a road block with Sort.Order (from spring-data-commons) that uses simple String property names rather than SqlIdentificer. So my approach would either need a different representation for sorting and manual mapping of those or a change to Sort.Order and related R2DBC statement mappers.