New MemberMapping

[sebersole]

I know @gavinking is not super keen on unsealing ResultSetMapping - #887.

As mentioned there, there are a few things in Hibernate's legacy dynamic ResultSet mapping API for native SQL that we cannot express in ResultSetMapping. The biggest one is the notion of mapping a join fetch (to-one).

As an alternative to #887, how about adding the ability to map fetched to-one associations -

public sealed interface MemberMapping<T>
        permits FieldMapping, EmbeddedMapping, ToOneMapping {
}

public record ToOneMapping<T>
        (Class<C> containerType, String alias,
        String name, Class<T> toOneType,
        ColumnMapping[] fkColumns,
        ...) implements MemberMapping<T> {
}

E.g.

var sqlString = """
        select ...,
            b.title,
            p.name,
            b.publisher_fk,
            b.id,
            p.id
        from books b
            join publishers p on b.publisher_fk = p.id
        """;

var publisher = toOne(Book_.publisher,
        "p",
        members(
                field("name", ... ),
                ...
        )
);
var bookMapping = entity(Book.class,
        "b",
        members(
                field("title" ),
                publisher,
                ...
        )
);

em.createNativeQuery( sqlString, bookMapping );

[gavinking]

Yeah I agree we should add this. But what about fetched to-many associations?

[sebersole]

How much of this rabbit hole do you want to see? ;)

Hibernate does have support for collection fetching too, yes. I was just trying to keep this vanilla. But I definitely think that would be good to have as well...

public sealed interface MemberMapping<T>
        permits FieldMapping, EmbeddedMapping, ToOneMapping, ToManyMapping {
}

public record ToManyMapping<E>
        (Class<C> containerType, String alias,
        String name, Class<E> elementType, Class<I> indexType,
        ColumnMapping[] fkColumns,
        ...) implements MemberMapping<T> {
}

[gavinking]

So you have flattened stuff out here: Class<T> toOneType, Class<E> elementType. Is there a reason to not use a nested EntityMapping there?

[sebersole]

Well, first elementType might not be an entity. I naively said to-many, but ofc you might want to join fetch an element-collection as well. So not sure what to do there. Another type of MemeberMapping perhaps.

For to-one, sure that's an option. Though I also think EntityMapping needs a ColumnMapping version as well. From playing with implementing it, I'd even say that a separate mapping for identifier would be nice there as well, though that is not simple for non-EmbeddedId composite ids.

[gavinking]

In #959 I propose FetchMapping, allowing code like this:

var bookWithPublisherAndAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                fetch(Book_.publisher,
                        entity(Publisher.class,
                                field(Publisher_.name, "PUB_NAME")),
                        "BOOK_PUB_ID"),
                fetch(Book_.authors,
                        entity(Author.class,
                                field(Author_.ssn, "AUTH_SSN"),
                                field(Author_.name, "AUTH_NAME")),
                        "BOOK_AUTH_ISBN"));

WDYT?

[gavinking]

A slightly different API, that really works well for to-ones, but not quite so well for to-manys, is something like this:

var bookWithPublisher =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                entity(Publisher.class, field(Publisher_.name, "PUB_NAME"))
        );

That is, just make EntityMapping a MemberMapping (or introduce a new, intermediate sealed class). I really like how clean this looks.

For collections, I suppose something like this sort of works if you assume that there's at most one fetched collection in the result set. (Which is actually a very reasonable restriction.)

var bookWithAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                collection(Book_.authors, "BOOK_AUTH_ISBN"),
                entity(Author.class,
                        field(Author_.ssn, "AUTH_SSN"),
                        field(Author_.name, "AUTH_NAME")),
        );

@sebersole How important is it to be able to deal with to-manys?

[gavinking]

A perhaps even more readable (if somewhat redundant) variation of the above would be:

var bookWithPublisher =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                entity(Book_.publisher, field(Publisher_.name, "PUB_NAME"))
        );

And

var bookWithAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                collection(Book_.authors, "BOOK_AUTH_ISBN"),
                entity(Book_.authors,
                        field(Author_.ssn, "AUTH_SSN"),
                        field(Author_.name, "AUTH_NAME")),
        );

[gavinking]

Hrrrrm, perhaps the following works:

var bookWithAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                collection(Book_.authors, "BOOK_AUTH_ISBN", "BOOK_AUTH_SSN"),
                entity(Author.class,
                        field(Author_.ssn, "AUTH_SSN"),
                        field(Author_.name, "AUTH_NAME")),
        );

And for an @ElementCollection:

var bookWithTopics =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                field(Book_.publisher, "BOOK_PUB_ID"),
                collection(Book_.topics, "BOOK_TOPIC_ISBN", "BOOK_TOPIC")
        );

Though it looks a bit uglier with composite keys....

[gavinking]

I guess a third option might be to flatten out the EntityMapping into the FetchMapping, resulting in:

var bookWithPublisherAndAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                fetch(Book_.publisher,
                        field(Publisher_.name, "PUB_NAME"),
                        "BOOK_PUB_ID"),
                fetch(Book_.authors,
                        field(Author_.ssn, "AUTH_SSN"),
                        field(Author_.name, "AUTH_NAME"),
                        "BOOK_AUTH_ISBN"));

That's ... a bit easier on the eyes at least.

[gavinking]

The advantage of the nested EntityMapping is that the approach can in principle be extended to fetch other kinds of collection, for example:

var bookWithPublisherAndAuthors =
        entity(Book.class,
                field(Book_.isbn, "BOOK_ISBN"),
                field(Book_.title, "BOOK_TITLE"),
                fetch(Book_.topics,
                        column("TOPIC_NAME", String.class),
                        "BOOK_TOPIC_ISBN"));

[sebersole]

This last one actually maps pretty conceptually close to what we have in Hibernate, so +1