[Use Case]: multi-item 'select' lists in JDQL

[gavinking]

As a ...

• Application user/user of the configuration itself
• API user (application developer)
• SPI user (container or runtime developer)
• Specification implementer

I need to be able to ...

select multiple fields of my entity in a JDQL select list, and have them packaged into some nice typesafe record object.

Which enables me to ...

handle "subset" or "simplified" views of the entity data, especially useful for externalization.

Additional information

For reference, please see a similar proposal for JPQL: jakartaee/persistence#420

My preferred way to handle this is the following:

// declare a record type
record BookSummary(String title, String author) {}

// use it as the return type of a @Query method
@Query("select title, author.name from Book")
List<BookSummary> bookSummaries();

The repository method would automatically package the the elements of the select list into new instances of the record type. Of course, the "type" of the select list must match the signature of the constructor.

People sometimes find this useful for creating DTOs.

This would not be for 1.0, but for a future release.

[gavinking]

A related idea, that I'm shamelessly stealing from Blaze-Persistence, is the notion of an "entity view".

From our perspective, it's a nice way to handle projections with @Find methods. Consider:

// declare a record type
@Projection(Book.class)
record BookSummary(String title, String isbn, 
                   @Project("length(text)") int length, 
                   @Project("publisher.name") String publisherName) {}

// use it as the return type of a @Find method
@Find
List<BookSummary> bookSummaries();

Note that:

• In Blaze-Persistence, an entity view is an interface, but in modern Java, a record is a better fit.
• The @Projection annotation determines the queried entity class.
• By default, the fields of the record are matched to the field of the entity by name and type, but the @Project annotation lets you specify a fragment of JDQL.

This idea fits in very nicely and consistently with #546.

[beikov]

For reference, Blaze-Persistence Entity-Views also supports classes and records, but interfaces support multi-inheritance and updatable projections which is why that is preferred.

I wanted to propose a new Jakarta EE project at some point to standardize projections, as I believe it has more applicability than just for Jakarta Persistence and Data.

The idea is that this new Jakarta project provides annotations and means to introspect the model, as well as create and take apart object instances.
It's up to the respective other Jakarta implementations to then support these projection types. Wdyt about that? We should get together and talk about this some time.

[gavinking]

For reference, Blaze-Persistence Entity-Views also supports classes and records, but interfaces support multi-inheritance and updatable projections which is why that is preferred.

Sure, I understand that composability is desirable here, but records are composable, just not via inheritance. For modern Java, using records seems more natural and idiomatic. So the trick would be to come up with a nice way to compose them. I have only half-formed ideas about this so far.

That said, you're more familiar with all the common usecases here, so it's something we would need to discuss with concrete examples.

[P.S. In my head, whenever I write "record", I'm thinking "record or class", which would allow at least single inheritance..]

I wanted to propose a new Jakarta EE project at some point to standardize projections, as I believe it has more applicability than just for Jakarta Persistence and Data.

It seems to me that it makes most sense to begin such discussions/work here, since whatever we come up with certainly has to integrate very cleanly with Jakarta Data. If/when we have something concrete, and realize that it makes more sense in a separate spec, and we see that other specs like Persistence and/or NoSQL are interested in reusing it, then it's straightforward to do a split.

We've done that enough times in the past: for example, Persistence started as part of EJB3, Interceptors and even I guess Managed Beans started as part of CDI (then called Web Beans). Similarly, we're currently proposing to split Jakarta Query out of Persistence.

The idea is that this new Jakarta project provides annotations and means to introspect the model, as well as create and take apart object instances.

In my head, I was thinking that these projections would probably have a static metamodel, allowing them to be used in dynamic sorting. For example, you might have _BookSummary.title.ascIgnoreCase().

But when you talk about "introspection" I imagine that you're thinking of something beyond that, something more like a JPA-style ManagedType. That's completely fine, but we would need to to explore the use cases which motivate this—I speculate that externalization is a big one—and consider that we need to careful control the scope of this work at first.

Anyway, the point is: my naive inclination would be to design something "small" for Jakarta Data first, and then take a step back and ask if it should be generalized. But of course I really don't yet understand the full picture here, and I reserve the right to backflip on this. :-)

We should get together and talk about this some time.

Yes of course.

[beikov]

It seems to me that it makes most sense to begin such discussions/work here, since whatever we come up with certainly has to integrate very cleanly with Jakarta Data. If/when we have something concrete, and realize that it makes more sense in a separate spec, and we see that other specs like Persistence and/or NoSQL are interested in reusing it, then it's straightforward to do a split.

My idea for integration is that JPA providers should support accepting the projection type as result type for queries e.g. entityManager.createQuery("select b from Book b", BookSummary.class) and that the JPA provider will just do the right thing. Jakarta Data can then say (which it will have to anyway), that the details of forming queries are up to the data store implementor.

Projections are IMO also very nice for XML/JSON mapping, where you have one type (e.g. entity) that specifies the canonical model and the projection is just a view onto that. A smart XML/JSON parser can then avoid parsing/materializing certain parts based on the projection expression/paths.

We've done that enough times in the past: for example, Persistence started as part of EJB3, Interceptors and even I guess Managed Beans started as part of CDI (then called Web Beans). Similarly, we're currently proposing to split Jakarta Query out of Persistence.

I'm not really a fan of that to be honest as it implies people will have to migrate at some point. I think we will figure out during a discussion that it is worthwhile to have a separate spec for this though.

In my head, I was thinking that these projections would probably have a static metamodel, allowing them to be used in dynamic sorting. For example, you might have _BookSummary.title.ascIgnoreCase().

Yes, that's also something that I more or less did in Blaze-Persistence Entity-Views. I didn't tie that aspect to the metamodel, but instead one can addAttributeSorter(BookSummary_.title, Sorters.asc()), but the idea is the same.
In addition to that, Entity-Views also allow specifying named filters on such projection attributes e.g. @AttributeFilter(StartsWithFilter.class) that can then be activated addAttributeFilter(BookSummary_.title, "Abc").
There is much more we can do here.

But when you talk about "introspection" I imagine that you're thinking of something beyond that, something more like a JPA-style ManagedType. That's completely fine, but we would need to to explore the use cases which motivate this—I speculate that externalization is a big one—and consider that we need to careful control the scope of this work at first.

The main use case is easy integration. If we have this Jakarta Query project and then Jakarta Projections which depends on Jakarta Query, we could expose structured information (like JPA Path/Expression) for the projection mappings, which JPA, JSONB etc. can consume to produce efficient queries/parsers.
I have also been told that people think Entity-Views is the perfect abstraction for GraphQL i.e. you define your GraphQL types as Entity-Views to limit the exposed data and control fetch strategies.

[gavinking]

My idea for integration is that JPA providers should support accepting the projection type as result type for queries e.g. entityManager.createQuery("select b from Book b", BookSummary.class) and that the JPA provider will just do the right thing.

Yes, sure, I understand.

Projections are IMO also very nice for XML/JSON mapping, where you have one type (e.g. entity) that specifies the canonical model and the projection is just a view onto that. A smart XML/JSON parser can then avoid parsing/materializing certain parts based on the projection expression/paths.

Yes, I understand all that. But it seems to me that this automatically Just Works, i.e. that nothing additional needs to be specified to make this possible.

Even in Quarkus today, if I do select new BookSummary(...) in JPQL, and return the result from a RESTEasy @GET method, Quarkus happily serializes that to JSON without me needing to do any additional work.

I'm not really a fan of that to be honest as it implies people will have to migrate at some point.

Well, no, in none of the previous occasions I listed was any migration required.

Yes, that's also something that I more or less did in Blaze-Persistence Entity-Views. I didn't tie that aspect to the metamodel, but instead one can addAttributeSorter(BookSummary_.title, Sorters.asc()), but the idea is the same.

Nice.

In addition to that, Entity-Views also allow specifying named filters on such projection attributes

Yes, that's an obvious clean tie-in with #460 (comment). Again, it can be made typesafe.

I have also been told that people think Entity-Views is the perfect abstraction for GraphQL i.e. you define your GraphQL types as Entity-Views to limit the exposed data and control fetch strategies.

Yes I agree.

[njr-11]

I'll start off by saying that I'm definitely in favor of adding support for projections. We will need to work out the details. The Projection/Project pattern is nice and has uses beyond Jakarta Data, which is also nice. If that is added in a separate spec, or in a common place (Jakarta Annotations?) to be used by multiple specs, I could see Jakarta Data, as well as Jakarta Persistence, and others, leveraging it. An advantage of that pattern is being able to define the mapping once in a single place. A disadvantage is that you need to be able to own the code of the record/class in order to place the annotations on it, so it rules out projecting to a record type that came from a third-party. We would need to look at advantages and disadvantages more closely, but those are a few that immediately stand out.

Starting with the simplest possible scenario where there is direct correspondence between the field names of the record and entity, it would be nice if the user doesn't need to define extra annotations at all, which could be an optimization of the pattern,

record BookSummary(String title, String isbn, String description) {}

@Find
List<BookSummary> bookSummaries();

The above pattern (which works because the primary entity type of the repository is known to be Book) will make sense for scenarios where the user is writing the record for purposes of obtaining the projection.

When mapping of fields is needed, then we need something more verbose to provide more information. The Projection approach is a good idea here. Another idea that I can think of is letting Find specify the projection.

I already want Find to support returning a single column as a type-safe way of doing what the Query Language allows,

@Find(_Book.TITLE)
@OrderBy(_Book.TITLE)
List<String> titles();

If we were doing that anyway, it could be natural to allow multiple values,

record BookSummary(String title, String isbn, String publisherName) {}

@Find({ _Book.TITLE, _Book.ISBN, _Book.PUBLISHER_NAME})
@OrderBy(_Book.ISBN)
List<BookSummary> bookSummaries();

Trading off the safer usage of limiting oneself to metamodel constants, the above could allow query fragments to be intermixed just as the other model does,

record BookSummary(String title, String isbn, int length, String publisherName) {}

static final int BOOK_LENGTH = "length(" + _Book.TEXT + ")";

@Find({ _Book.TITLE, _Book.ISBN, BOOK_LENGTH,  _Book.PUBLISHER_NAME})
@OrderBy(_Book.ISBN)
List<BookSummary> bookSummaries();

I'm not saying this alternative pattern is better than the other one. It's another possibility for us to consider, with some slightly different tradeoffs. I'd like to see where the Projection idea goes with respect to other Jakarta specifications, and if it gets added, we can easily align with it, and if not, we certainly have some other options.