Result Set Mapping: Complex Mappings (http://www.thoughts-on-java.org/2015/04/result-set-mapping-complex.html)

additionalColumn.sql

SELECT a.id, a.firstName, a.lastName, a.version, count(b.id) as bookCount FROM Book b JOIN Author a ON b.author_id = a.id GROUP BY a.id, a.firstName, a.lastName, a.version

AuthorBookCountMapping.java

@SqlResultSetMapping(
        name = "AuthorBookCountMapping",
        entities = @EntityResult(
                entityClass = Author.class,
                fields = {
                    @FieldResult(name = "id", column = "id"),
                    @FieldResult(name = "firstName", column = "firstName"),
                    @FieldResult(name = "lastName", column = "lastName"),
                    @FieldResult(name = "version", column = "version")}),
        columns = @ColumnResult(name = "bookCount", type = Long.class))

authorBookCountMapping.xml

<sql-result-set-mapping name="AuthorBookCountMappingXml">
    <entity-result entity-class="org.thoughts.on.java.jpa.model.Author">
        <field-result name="id" column="id"/>
        <field-result name="firstName" column="firstName"/>
        <field-result name="lastName" column="lastName"/>
        <field-result name="version" column="version"/>
    </entity-result>
    <column-result name="bookCount" class="java.lang.Long" />
</sql-result-set-mapping>

BookAuthorMapping.java

@SqlResultSetMapping(
        name = "BookAuthorMapping",
        entities = {
            @EntityResult(
                    entityClass = Book.class,
                    fields = {
                        @FieldResult(name = "id", column = "id"),
                        @FieldResult(name = "title", column = "title"),
                        @FieldResult(name = "author", column = "author_id"),
                        @FieldResult(name = "version", column = "version")}),
            @EntityResult(
                    entityClass = Author.class,
                    fields = {
                        @FieldResult(name = "id", column = "authorId"),
                        @FieldResult(name = "firstName", column = "firstName"),
                        @FieldResult(name = "lastName", column = "lastName"),
                        @FieldResult(name = "version", column = "authorVersion")})})

bookAuthorMapping.xml

<sql-result-set-mapping name="BookAuthorMappingXml">
    <entity-result entity-class="org.thoughts.on.java.jpa.model.Author">
        <field-result name="id" column="authorId"/>
        <field-result name="firstName" column="firstName"/>
        <field-result name="lastName" column="lastName"/>
        <field-result name="version" column="authorVersion"/>
    </entity-result>
    <entity-result entity-class="org.thoughts.on.java.jpa.model.Book">
        <field-result name="id" column="id"/>
        <field-result name="title" column="title"/>
        <field-result name="author" column="author_id"/>
        <field-result name="version" column="version"/>
    </entity-result>
</sql-result-set-mapping>

booksWithAuthor.sql

SELECT b.id, b.title, b.author_id, b.version, a.id as authorId, a.firstName, a.lastName, a.version as authorVersion FROM Book b JOIN Author a ON b.author_id = a.id

UseAuthorBookCountMapping.java

List<Object[]> results = this.em.createNativeQuery("SELECT a.id, a.firstName, a.lastName, a.version, count(b.id) as bookCount FROM Book b JOIN Author a ON b.author_id = a.id GROUP BY a.id, a.firstName, a.lastName, a.version", "AuthorBookCountMapping").getResultList();

results.stream().forEach((record) -> {
    Author author = (Author)record[0];
    Long bookCount = (Long)record[1];
    System.out.println("Author: ID ["+author.getId()+"] firstName ["+author.getFirstName()+"] lastName ["+author.getLastName()+"] number of books ["+bookCount+"]");
});

UseBookAuthorMapping.java

List<Object[]> results = this.em.createNativeQuery("SELECT b.id, b.title, b.author_id, b.version, a.id as authorId, a.firstName, a.lastName, a.version as authorVersion FROM Book b JOIN Author a ON b.author_id = a.id", "BookAuthorMapping").getResultList();

results.stream().forEach((record) -> {
    Book book = (Book)record[0];
    Author author = (Author)record[1];
    // do something useful
});