Custom comparators for R2dbcRepository in Spring Data

[IvanKonevJr]

Feature Request
Needed custom comparators for supporting special iLike, Jsonb, Arrays, Ranges and Full Text Search of PostgreSQL inline querying in Criteria builder (do not custom, only inline)

Describe the solution you'd like
As solution may be create comparators as bean and dynamic extending collection:

enum Comparator { EQ, NEQ, LT, LTE, GT, GTE, IS_NULL, IS_NOT_NULL, LIKE, NOT_IN, IN, }

[carhartl]

I was also facing the issue that I wanted to use a jsonb operator in a condition, such as data ? foo, which doesn't seem to be possible usingCriteria at the moment. I had to resort to go lower level...

[carhartl]

E.g. I would see some value in enhancing the Criteria builder one way or the other... I noticed there's Conditions.just(), so could there be something like a Criteria.just()?

[mp911de]

This ticket isn't an exact duplicate of #289 but the referenced ticket contains a few details on why this enhancement isn't really going to work. We recommend using the execute() API for more flexibility.

[carhartl]

Hi @mp911de, understood, I had to resort to the execute() api :)

Though if you don't know upfront which conditions there are going to be (think: taking filters from http request params) I found it difficult because having to bind() values individually. For this Criteria was really convenient... could there be an alternative bind() taking a map, or an array? Or am I overlooking something?

[mp911de]

DatabaseClient's API is comparable to what JdbcTemplate provides though it has a tiny POJO-mapping-based API. I suggest using something like jOOQ to generate dynamic SQL statements. We do not plan to evolve the Criteria API too much. There are all sorts of ways to express the WHERE clause or the functionality around Postgres' Fulltext-search or any JSON query predicates so that we would end up with a massive pile of functionality and a very broad API to cater for the individual things one might want to achieve with Criteria.

[IvanKonevJr]

Criteria API it is a high level and provides a certain level of security, if we move to a lower level as jOOQ, then we will have to write a bunch of code to fully repeat the Criteria API functionality with only possibility of custom comparators )))

[mp911de]

In #265, we provide two rather simple examples. Without introducing another abstraction level we cannot infer type and parameter binding information that would be required to render proper criteria. Spring Data's support for relational databases is primary a library to implement the repository pattern and to aid common use-cases. It's by no means an exhaustive library that supports all types of features that are provided by the underlying databases.

[IvanKonevJr]

@mp911de
criterial.and("score").has("<@ int4range(1,7)")

has function is planed?

[IvanKonevJr]

Hi @carhartl, you wrote:

Hi @mp911de, understood, I had to resort to the execute() api :)

Can you show of use execute() api for jsonb?

[IvanKonevJr]

In #265, we provide two rather simple examples. Without introducing another abstraction level we cannot infer type and parameter binding information that would be required to render proper criteria. Spring Data's support for relational databases is primary a library to implement the repository pattern and to aid common use-cases. It's by no means an exhaustive library that supports all types of features that are provided by the underlying databases.

yes, but Hibernate support custom operators

[carhartl]

@LaoTsing

Can you show of use execute() api for jsonb?

To give you an idea:

databaseClient.execute("SELECT data FROM things WHERE data ? $1").bind(0, "foo");

[IvanKonevJr]

Thank you, but i think to as clean is supporting custom operators in Criteria API, because each database have a lot inline operators