Different result for @SQLResultSetMapping+Joins on multiple entities | JPA

Question

I am running a NativeQuery with JPA that gives different results compared to running the query in an sql tool. Probably I missunderstand s.th. within the concept of @SQLResultSetMapping.

--- Overview ---

I am mapping the result to two entities, so I am expecting to receive a List of Entity-Pairs. This works.

When you look at the picture below, you'll see the result of the query in an sql tool, where ..

• .. the RED BOX maps to one entity
• .. the GREEN BOX maps to the second entity

JPA should give me one of the native row as a pair of two entities.

Problem

This is where things go wrong. Yes, I will receive a list of pairs of both entities, but unlike in the picture the column "pp.id" does not iterate over all rows of the respective table (in the picture "5,6,7,..", from JPA "5,5,5,5,..").

The column pp.id is a joined column, I guess that I missunderstand something within JPA when it comes to Joins + SQLResultSetMappings. It appears to me that the difference is that JPA is always joining THE SAME row from table 'propertyprofile' (more detailes below), unlike when the query is run in sql.

I hope that somebody takes pity on me and helps me out. :)

--- Details ---

Query

I am basically trying to find out if every 'product', has defined a 'value' (table propertyvalue) for a predefined 'property' (table propertyprofile).

The probably most relevant part is at the bottom, where "propertyprofile" is joined and "propertyvalue" is left-joined.

select sg.ID as 'sg.id', sg.Name as 'sg.name', ppcount.totalppcount as 'sg.totalppcount', ppcount.totalppothercount as 'sg.totalppothercount',
p.ID as 'product.id', pp.id as 'pp.id', pp.Role as 'pp.role', pp.Name as 'pp.name',
(case when pv.id is null then '0' else '1' end) as 'hasPropertyValue', pv.ID as 'pv.id', pv.StringValue, pv.IntervallMin, pv.IntervallMax
from shoppingguide sg
join
(
    select sg.ID as 'sgid', count(*) as 'totalppcount', count(pp_other.ID) as 'totalppothercount' from propertyprofile pp_all
    left join propertyprofile pp_other on pp_other.id = pp_all.id AND pp_other.Role = '0'
    join shoppingguide sg on pp_all.ShoppingGuideID = sg.ID
    join shopifyshop ss on sg.ShopifyShopID = ss.ID
    where
    pp_all.ShoppingGuideID = sg.ID AND
    ss.Name = :shopName
    GROUP BY pp_all.ShoppingGuideID
) ppcount on ppcount.sgid = sg.id
join shopifyshop ss on sg.ShopifyShopID=ss.ID
join product p on p.ShopifyShopID = ss.ID
join propertyprofile pp on (pp.ShoppingGuideID = sg.id AND pp.Role = '0')
left join propertyvalue pv on (pv.ProductID=p.ID and pv.PropertyProfileID = pp.id)
where 
ss.Name = :shopName
order by sg.id asc, p.id asc, pp.id asc
;

Tables

There are a lot of tables involved, but these are the most important ones to understand the query:

• product
• propertyprofile - a feature that all products have (e.g. height, price)
• propertyvalue - data for a specific feature; relates to propertyprofile (e.g. 5cm; $120)

SQLResultSetMapping

The mapping is done onto two entites: ProductDataFillSummary_ShoppingGuideInformation, ProductDataFillSummary_ProductInformation.

@SqlResultSetMapping(
        name = "ProductDataFillSummaryMapping",
        entities = {
            @EntityResult (
                    entityClass = ProductDataFillSummary_ShoppingGuideInformation.class,
                    fields = {
                        @FieldResult(name = "shoppingGuideId", column = "sg.id"),
                        @FieldResult(name = "shoppingGuideName", column = "sg.name"),
                        @FieldResult(name = "numberOfTotalPropertyProfiles", column = "sg.totalppcount"),
                        @FieldResult(name = "numberOfTotalPropertyProfilesOther", column = "sg.totalppothercount")
                    }),
            @EntityResult(
                    entityClass = ProductDataFillSummary_ProductInformation.class,
                    fields = {
                        @FieldResult(name = "productID", column = "product.id"),
                        @FieldResult(name = "propertyProfileId", column = "pp.id"),
                        @FieldResult(name = "propertyProfileRole", column = "pp.role"),
                        @FieldResult(name = "propertyValueId", column = "pv.id"),
                        @FieldResult(name = "hasPropertyValue", column = "hasPropertyValue")
                        }
                    )
        })

Answer 1

Analysis

The problem seems to be that Hibernate does NOT ..

• .. process each row
• .. per row map onto designated entities
• .. put the mapped entities for this row into List (in my example a pair of entities)

In fact hibernate seems to match both entities, which should go into the same entry of List, based on the primary key attributes, i.e. sth like this:

• .. process each row
• .. for each row map to respective entities (separately)
• .. store the mapped entities using their primary key
• .. match respective entities which go into the same entry for List

In my example, a pairs of [ProductDataFillSummary_ShoppingGuideInformation, ProductDataFillSummary_ProductInformation] will be inserted into the list. When 'ProductDataFillSummary_ProductInformation' is insterted, Hibernate will try to find the correct instance using the primary key (here 'ProductDataFillSummary_ProductInformation.productId'). Due to several rows for ProductDataFillSummary_ProductInformation having the same value for productId, always the first instance will be fetched and used for List.

Solution

Either use a compound key that considers 'ProductDataFillSummary_ProductInformation.productId' and '.propertyProfileId', or ..

Use an artifical key (uuid) if it's not possible to use a combined key:

concat(p.ID, '-', pp.ID) as 'uuid'