Ability to define basic queries dynamically

[njr-11]

Users will sometimes need to define queries dynamically instead of statically in response to what they see happen at run time. For example, a user specifies various customizable search options and in response, the application dynamically builds a query to filter for the matching results.

An application ought to be able to define the same kind of queries dynamically that it can already define statically (with patterns like Query by Method Name). It might be useful to go beyond that as well, but this is a good starting point.

A separate approach for advanced patterns could be to supply JDQL dynamically, but that is not covered here. That can be considered separately, and I'd like to get a more basic pattern in place first.

Dynamic queries should make use of the Jakarta Data Static Metamodel and generally be agnostic to the database as much as possible, other than the same sort of limitations that Query By Method Name and the other static patterns have on which operations certain classes of NoSQL databases are incapable of.

The Jakarta Data Static Metamodel will be expanded to represent conditions that are possible per attribute type. These conditions can be supplied to a varargs where method that is the starting point for building dynamic queries:

products.where(_Product.name.like(namePattern),
               _Product.price.lessThan(100.0));

From there, you can match the capability of the Query By Method Name pattern:

Count Query:

long count = products.where(_Product.name.like(namePattern),
                            _Product.price.lessThan(100.0))
                     .count();

Exists Query:

boolean found = products.where(_Product.name.like(namePattern),
                               _Product.price.lessThan(100.0))
                        .exists();

Delete Query:

long numDeleted = products.where(_Product.id.equals(id))
                          .delete();

Find Query (as List):

List<Product> list = products.where(_Product.name.like(namePattern),
                                    _Product.price.lessThan(100.0))
                             .list(Order.by(_Product.price.desc()));

Find Query (as offset-based Page):

Page<Product> page1 = products.where(_Product.name.like(namePattern),
                                     _Product.price.lessThan(100.0))
                              .page(PageRequest.ofSize(10),
                                    Order.by(_Product.price.desc(),
                                             _Product.id.asc()));

Find Query (as CursoredPage):

CursoredPage<Product> next = products.where(_Product.name.like(namePattern),
                                            _Product.price.lessThan(100.0))
                                     .cursoredPage(current.nextPageRequest(),
                                                   Order.by(_Product.price.desc(),
                                                            _Product.id.asc()));

Find First:

Optional<Product> first = products.where(_Product.name.like(namePattern),
                                         _Product.price.lessThan(100.0))
                                  .first(Order.by(_Product.price.desc()));

Find single attribute of entity:

Optional<Float> lowestPrice = products.where(_Product.name.equals(name))
                                      .select(_Product.price)
                                      .first(Order.by(_Product.price.asc()));

Update (not part of Query By Method Name, but would be possible to do this):

long updated = products.where(_Product.id.equals(id))
                       .modify(_Product.description.assign(newDesc),
                               _Product.price.add(5.00));

[otaviojava]

Including this as a reference:

• Proposal: Enhanced find() / count() methods that take Consumer<QueryCriteria> lambdas persistence#580
• https://docs.jboss.org/hibernate/orm/6.6/introduction/html_single/Hibernate_Introduction.html#criteria-definition

[otaviojava]

One point, I like the more fluent and explity style, I mean, when we need to join more conditions it should have either, and/or method.

products.where(_Product.name.like(namePattern).and(_Product.price.lessThan(100.0)));

[gavinking]

I think I liked this idea better when it was based around passing a Restriction as a parameter to a regular query method of a repository. I'm a bit scared that this is now going in the direction of growing into a complete criteria query API, which:

• from past experience, always seem to end in a mess, and
• I don't really think is actually that useful.

99% or more of use cases for dynamic query construction involve dynamically adding restrictions or sorting to a query that is otherwise completely static. So those are the use cases I would prefer to emphasize here.

Sure, given how limited JDQL is by its very nature, a "full" criteria API for it would certainly not need to be anywhere near as complicated as, say, the JPA criteria API. But even so, I would still begin by just adding the ability to add restrictions the way we can currently add ordering.

[graemerocher]

generally data repositories are designed for static queries and dynamic queries are left to the implementation or some other implement. Developers using Micronaut Data pair it with JPA or JDBC + JDBI, JDBC + JOOQ, JDBC + MyBatis when dynamic queries are needed.

I personally think it is a mistake to try over complicate the spec with dynamic queries because there are many different ways and many different databases each with pros and cons and trying to standardise is likely not going to lead to anything useful.

If this going to be added like @gavinking said it should be for basic restrictions if anything.

[njr-11]

I like the Restrictions approach proposed by Gavin that allows adding restrictions dynamically the same way we can already add Sorts dynamically. It is better aligned with what we already have in Jakarta Data, and I think even covers all the same scenarios by allowing findAll/deleteAll/countAll/existsAll methods with restrictions all supplied dynamically, plus offers the ability to have restrictions add on to a static query. It's a better idea. I just hadn't thought of it.

[gavinking]

It's a better idea. I just hadn't thought of it.

Ironic. I was 100% convinced it was your idea.

[njr-11]

It's a better idea. I just hadn't thought of it.

Ironic. I was 100% convinced it was your idea.

Adding restrictions to the metamodel was my idea. But I didn't realize the better way of using it until you pointed it out.

In any case, closing this issue in favor of #829 which is written up in terms of the improved approach.