Tag: n+1 selects

Hibernate n+1 selects and lazy loading vs eager

Assuming a shop can have many products we want to display shops along with products:
1) with one select query using join – we need to annotate in @OneToMany fetchType to EAGER to produce:

Hibernate:
select
this_.id as id1_1_1_,
this_.shipment_address as shipment2_1_1_,
products2_.shop_id as shop_id4_0_3_,
products2_.id as id1_0_3_,
products2_.id as id1_0_0_,
products2_.name as name2_0_0_,
products2_.price as price3_0_0_,
products2_.shop_id as shop_id4_0_0_
from
Shop this_
left outer join
Product products2_
on this_.id=products2_.shop_id

when calling

            Session session = sessionFactory.openSession();
            Criteria criteria = session.createCriteria(Shop.class).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
            List list = criteria.list();

2) when we set fetch type to LAZY (default) then we get one select for shops and optionally additional selects for products for particular shop(s):

Hibernate:
select
this_.id as id1_1_0_,
this_.shipment_address as shipment2_1_0_
from
Shop this_

when retrieving shops
and when we want to retrieve products for particular shop(s)

            Shop shop = shops.get(0);
            List products = shop.getProducts();

then we get:

Hibernate:
select
products0_.shop_id as shop_id4_0_0_,
products0_.id as id1_0_0_,
products0_.id as id1_0_1_,
products0_.name as name2_0_1_,
products0_.price as price3_0_1_,
products0_.shop_id as shop_id4_0_1_
from
Product products0_
where
products0_.shop_id=?

for code

@Entity
public class Shop extends BaseEntity {

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

    @OneToMany(mappedBy = "shop", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List products = new ArrayList<>();

    Shop() {
        // default constructor needed for ORM
    }

    public Shop(String shipmentAddress, Product... products) {
        this.shipmentAddress = shipmentAddress;
        for (Product product : products) {
            product.addShop(this);
            this.products.add(product);
        }
    }

    public List getProducts() {
        return products;
    }

    @Override
    public String toString() {
        return "Shop{" +
                super.toString() +
                ", shipmentAddress='" + shipmentAddress + '\'' +
                //", products=" + products +
                '}';
    }
}

@Entity
public class Product extends BaseEntity {

    @Column
    private String name;

    @Column
    private BigDecimal price;

    @ManyToOne
    @JoinColumn(name = "shop_id", foreignKey = @ForeignKey(name = "fk_product_shop"))
    private Shop shop;

    Product() {
        // default constructor needed for ORM
    }

    public Product(String name, BigDecimal price) {
        this.name = name;
        this.price = price;
    }

    void addShop(Shop shop) {
        this.shop = shop;
    }

    @Override
    public String toString() {
        return "Product{" +
                super.toString() +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", shop.id=" + shop.getId() + '}';
    }
}