How to fetch an @ElementCollection map's related tables

Question

This is a simple Product entity that refers to a subgroup:

public class Product implements Comparable<Product> {
    ...    
    @ManyToOne(optional=false, fetch=FetchType.LAZY)
    @NotNull
    private ProductSubGroup productSubGroup;
    ...
}

I have a map containing Product's in another entity:

public class FinishedProduct {
    ...
    @NotNull
    @ManyToOne
    private Product product;

    @ElementCollection(fetch=FetchType.LAZY)
    @MapKeyJoinColumn
    @Column(name="amount")
    @Sort(type=SortType.NATURAL)
    @Fetch(FetchMode.SUBSELECT)
    private SortedMap<Product, Double> byproducts = new TreeMap<>();   
    ...
}

I can load the map with this code:

Root<FinishedProduct> root = q.from(FinishedProduct.class);
root.fetch("product", JoinType.LEFT);
root.fetch("byproducts", JoinType.LEFT);

This works, but I need the productSubGroup of the byproducts stored in the map without generating n+1 selects. How can I fetch them? Just adding the fetch to the end results in an exception:

root.fetch("byproducts", JoinType.LEFT).fetch("productSubGroup", JoinType.LEFT);

org.springframework.dao.InvalidDataAccessApiUsageException: 
Collection of values [null] cannot be source of a fetch

Also tried to fool around with MapJoin, same exception:

MapJoin<FinishedProduct,Product,Double> map = root.joinMap("byproducts", JoinType.LEFT);
map.fetch("productSubGroup", JoinType.LEFT);

I guess I somehow need to refer to the map key, but no idea how.

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

These are a bit complex mappings that you have here and I am not sure if there is an easier way to accomplish this. Hopefully somebody will provide a better answer, but as an alternative there is always the ability to pre-load into the persistence context all the entity instances that you know will be fetched with n+1 selects.

So, before firing your query, just load all ProductSubGroups which are expected to be fetched:

select p.productSubGroup from Product p
where p in (select index(byproducts) from FinishedProduct)

Of course, repeat any other additional restrictions on FinishedProduct in the subquery which you have in your original query to avoid loading ProductSubGroups which you don't need.

As even better alternative (in my opinion), you may want to consider defining @BatchSize for Product.productSubGroup association. That way ProductSubGroups would be loaded in batches instead of one by one.

edited May 23 '17 at 10:30

Community

1
1

answered Nov 26 '16 at 16:46

Dragan Bozanovic

23,102
5
43
110

Both are valuable suggestions, although I couldn't make `@BatchSize` work in this Spring Boot/Data/JPA/Hibernate mess for some reason. But the other worked, just had to add `@Transactional` to the calling controller method. – Arthur Nov 27 '16 at 08:40
I was hoping to apply your solution to my other problem at http://stackoverflow.com/questions/40419986/hibernate-join-on-generates-invalid-query but that's unfortunately different, because caching works by ID and a three-way join queries by two other IDs, I guess. – Arthur Nov 27 '16 at 08:41
Sometimes I think there is a person in the Hibernate team whose sole job is to find real-life usage scenarios and make them impossible. :) – Arthur Nov 27 '16 at 08:42
Like in any framework, there are some missing features that could be useful (like outer joins with non-associated entities which may be related to your other question), and some things that may seem unintuitive at first glance. It takes time to get used to ORM concepts. – Dragan Bozanovic Nov 27 '16 at 17:11

How to fetch an @ElementCollection map's related tables

1 Answers1