Spring JPA most efficient way to load ManyToOne relationship?

Question

I have an entity called StoreQuantity, which stores the current in stock quantity of all products/items in a store:

@Data
@Entity
@Table(name = "STORE_QUANTITY")
public class StoreQuantity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "STORE_QUANTITY_ID", nullable = false)
    private int storeQuantityId;

    @ManyToOne
    @JoinColumn(name = "PRODUCT_ID", nullable = false)
    private Product product;

    @Column(name = "INSTORE_QUANTITY")
    private int instoreQuantity;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "STORE_ID", nullable = false)
    private Store store;
}

Corresponding Store entity: @Entity @Table(name = "store") public class Store implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "STORE_ID", nullable = false)
    private int storeId;

    @Column(name = "NAME", nullable = false)
    private String name;

    @JsonIgnore
    @OneToMany(mappedBy = "store")
    private List<StoreQuantity> storeQuantityList;
}

Im trying to retrieve all the quantities of products in all stores, and export as csv. I currently have thought of two ways of doing so:

Either Retrieve the entire storequantity table in one call, and for each storequantity I print as csv.

public String currentStoreQuantitiesCSV() {

    List<StoreQuantity> storeQuantityList = storeQuantityRepository.findAllByOrderByStoreDesc();

            for (StoreQuantity storeQuantity : storeQuantityList) {
                //StoreId
                csvString.append(storeQuantity.getStore().getStoreId()).append(',');
                //ProductId
                csvString.append(storeQuantity.getProduct().getProductId());
                //Product Quantity
                csvString.append(storeQuantity.getInstoreQuantity());
                csvString.append(',');
            }

Or I call them by store:

public String currentStoreQuantitiesCSV() {

        List<Store> storeList = storeRepository.findAll();

        for (Store store:storeList){
            List<StoreQuantity> storeQuantityList = store.getStoreQuantityList();

            for (StoreQuantity storeQuantity : storeQuantityList) {
                //Store Name
                csvString.append(storeQuantity.getStore().getName()).append(',');
                //ProductId
                csvString.append(storeQuantity.getProduct().getProductId());
                //Product Quantity
                csvString.append(storeQuantity.getInstoreQuantity());
                csvString.append(',');
            }
        }

They both work, now it's just a matter of efficiency and ram utilization. I read by default JPA will eagerly load any ManyToOne relationships: Default fetch type for one-to-one, many-to-one and one-to-many in Hibernate So does this mean if I choose option 1, there will be as many copies of store objects for every storequantity object? This will be extremely bad as I only have 20-or so stores, but thousands and thousands of storequantities, and id each of them are loaded with their own store object it will be very bad. Or will every storequantity point to the same store Objects? I'm only considering method two because that way there wouldnt be a lot of store objects in memory.

if you have to print only those 3 fields you should not fetch full Entities. The best thing is to create a DTO and create a query that fetches only the columns you need to export. — tremendous7, Jun 19 '21 at 01:14
Im fetching more fields, i just removed them to make the question more readable. — Oirampok, Jun 19 '21 at 03:35
https://www.geekyhacker.com/2019/03/26/high-performance-data-fetching-using-spring-data-jpa-stream/ — K.Nicholas, Jun 19 '21 at 14:36

score 0 · Accepted Answer · answered Jun 19 '21 at 16:36

I did some testing looking at the stack memory, it seems that JPA will automatically map all ManyToOne relationships to one object. So in this case for example we have one store, and 10 storequantities that have a ManyToOne to that store. JPA will only instantiate one store object and point all 10 storequantity objects to that one store, instead of creating one store for every storequantity object. So option 1 will be the most efficient as we decrease the amount of database calls.

Spring JPA most efficient way to load ManyToOne relationship?

1 Answers1