Support full projections when using JdbcOperations, like RepositoryOperations.

[nhoughto]

I was working through an approach to this discussion (#1108) and ended up digging into the code so understand it more now.

What was surprising to me was the large difference between RepositoryOperations, which is effectively what you are using with @repository and named methods (findByNameOrderByStatus etc) and JdbcOperations which is basically everything else.

Naively I thought JdbcOperations was roughly equivalent but lower level, the major difference from a developer usage point of view is that JdbcOperations doesn't seem to support any way to use joins to build projections. If I use .entityStream(..) there is no way for me to use all the micronaut-data goodness to create an object graph as the result that is specifically only called when using RepositoryOperations methods like findStream().

The reason for this is obvious, with RepositoryOperations it captures all the context in QueryModel like JoinPath to make joins etc to work and power projections, serialises that into an ExecutionMethod (i think its called?) and then uses that detail to construct a PreparedQuery at runtime. It is possible today with a bit of contorting to use RepositoryOperations and PreparedQuery at runtime without actually defining a Repository class or method, and doing that is how i've addressed our need for keyset pagination amongst other unique needs but in doing the work it became clear just how little change would be required to support this out of the box without contortion, which seems like a pretty great outcome? Super powered JdbcOperations.

Whilst most of the bits required to enable this are already there, it would be tinkering with the vital organs of how queries are executed so i'd imagine this would not be a 'PRs welcome' kind of change.

[dstepanov]

Can you show how did you manage to address your problem?

I'm thinking that we can partially support JPA's criteria API for JDBC/R2DBC and use QueryModel behind it. Or some kind of mixed content, where select .. from xyz will be pre-generated at the runtime but the where part can be generated using criteria or by hand.

[nhoughto]

So basically looks like this, subclass SqlQueryBuilder, use addCriterionHandler() in the constructor, define a new handler for my row wise comparison, rough impl looks like:

addCriterionHandler(
                RowWiseAllComparison.class,
                (ctx, criterion) -> {
                    ctx.whereClause().append(OPEN_BRACKET);
                    Iterator<Map.Entry<String, QueryParameter>> firstLoopIterator =
                            criterion.getProperties().entrySet().stream()
                                    .sorted(queryParamComparator)
                                    .iterator();
                    while (firstLoopIterator.hasNext()) {
                        Map.Entry<String, QueryParameter> entry = firstLoopIterator.next();
                        QueryPropertyPath prop =
                                ctx.getRequiredProperty(
                                        new QueryModel.PropertyNameCriterion(entry.getKey()));
                        try {
                            appendPropertyRefMethod.invoke(thisObject, ctx.whereClause(), prop);
                        } catch (Exception e) {
                            // this shouldnt happen as we've allowed access in the constructor
                            throw new RuntimeException("Error invoking appendPropertyRef method");
                        }
                        if (firstLoopIterator.hasNext()) {
                            ctx.whereClause().append(COMMA).append(SPACE);
                        }
                    }
                    ctx.whereClause().append(CLOSE_BRACKET);
                    ctx.whereClause().append(criterion.getOperator());
                    ctx.whereClause().append(OPEN_BRACKET);

                    Iterator<Map.Entry<String, QueryParameter>> secondLoopIterator =
                            criterion.getProperties().entrySet().stream()
                                    .sorted(queryParamComparator)
                                    .iterator();
                    while (secondLoopIterator.hasNext()) {
                        Map.Entry<String, QueryParameter> entry = secondLoopIterator.next();
                        QueryPropertyPath prop =
                                ctx.getRequiredProperty(
                                        new QueryModel.PropertyNameCriterion(entry.getKey()));
                        String placeholder =
                                ctx.addParameter(
                                        prop.getProperty(), prop.getPath(), entry.getValue());
                        ctx.whereClause().append(placeholder);

                        if (secondLoopIterator.hasNext()) {
                            ctx.whereClause().append(COMMA).append(SPACE);
                        }
                    }

                    ctx.whereClause().append(CLOSE_BRACKET);
                });

worst bit if reflection to access appendPropertyRef but change visibility to protected to solve that =) easy.

Then build up the QueryModel with our new Criterion RowWiseAllComparison and query gets written out as desired. We might use this pattern for JSONB interactions too, which obv aren't supported OOTB.

[graemerocher]

This relates to #298 and #89

I don't think we should be exposing QueryModel directly, if we do something it should be to integrate requery, querydsl or immutables.org and not re-invent the wheel for runtime query building

[nhoughto]

Seems like this has made some progress. Is the new Criteria API what I want?

https://micronaut-projects.github.io/micronaut-data/latest/guide/#dbcCriteriaSpecifications

[graemerocher]

Yes

[dstepanov]

The best solution would be to use https://github.com/micronaut-projects/micronaut-data/blob/master/data-processor/src/main/java/io/micronaut/data/processor/visitors/finders/MethodMatcher.java with JPA criteria API to create some custom queryRowWise. MethodMatcher is not a stable API and might change.

[nhoughto]

thanks for the tip, are there any best examples of using this API?

[dstepanov]

The API is not stable, you can find only some internal usage examples