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

Question

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?

Answer 1

The problem here is not related to Hibernate, but to JPA.

Prior to JPA 1.0, Hibernate 3 used lazy loading for all associations.

However, the JPA 1.0 specification uses FetchType.LAZY only for collection associations:

• @OneToMany,
• @ManyToMany
• @ElementCollection)

The @ManyToOne and @OneToOne associations use FetchType.EAGER by default, and that's very bad from a performance perspective.

The behavior described here is called the [N+1 query issue][5], and it happens because Hibernate needs to make sure that the @ManyToOne association is initialized prior to returning the result to the user.

Now, if you are using direct fetching via entityManager.find, Hibernate can use a LEFT JOIN to initialize the FetchTYpe.EAGER associations.

However, when executing a query that does not explicitly use a JOIN FETCH clause, Hibernate will not use a JOIN to fetch the FetchTYpe.EAGER associations, as it cannot alter the query that you already specified how to be constructed. So, it can only use secondary queries.

The fix is simple. Just use FetchType.LAZY for all associations:

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

More, you should use the db-util project to assert the number of statements executed by JPA and Hibernate.

Answer 2

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.