[SOLVED] Hibernate creating N+1 queries for @ManyToOne JPA annotated property

Hibernate creating N+1 queries for @ManyToOne JPA annotated property

I have these classes:

@Entity
public class Invoice implements Serializable {
    @Id
    @Basic(optional = false)
    private Integer number;

    private BigDecimal value;

    //Getters and setters
}

@Entity
public class InvoiceItem implements Serializable {
    @EmbeddedId
    protected InvoiceItemPK invoiceItemPk;

    @ManyToOne
    @JoinColumn(name = "invoice_number", insertable = false, updatable = false)
    private Invoice invoice;

    //Getters and setters
}

When i run this query:

session.createQuery("select i from InvoiceItem i").list();

It executes one query to select the records from InvoiceItem, and if I have 10000 invoice items, it generates 10000 additional queries to select the Invoice from each InvoiceItem.

I think it would be a lot better if all the records could be fetched in a single sql. Actually, I find it weird why it is not the default behavior.

So, how can I do it?

Solution

Try with

session.createQuery("select i from InvoiceItem i join fetch i.invoice inv").list();

It should get all the data in a single SQL query by using joins.