About Martin Mois

Martin is a Java EE enthusiast and works for an international operating company. He is interested in clean code and the software craftsmanship approach. He also strongly believes in automated testing and continuous integration.

Using @NamedEntityGraph to load JPA entities more selectively in N+1 scenarios

The N+1 problem is a common issue when working with ORM solutions. It happens when you set the fetchType for some @OneToMany relation to lazy, in order to load the child entities only when the Set/List is accessed. Let’s assume we have a Customer entity with two relations: a set of orders and a set of addresses for each customer.
 
 
 
 
 
 

@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<OrderEntity> orders;

@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<AddressEntity> addresses;

To load all customers, we can issue the following JPQL statement and afterwards load all orders for each customer:

List<CustomerEntity> resultList = entityManager.createQuery("SELECT c FROM CustomerEntity AS c", CustomerEntity.class).getResultList();
for(CustomerEntity customerEntity : resultList) {
    Set<OrderEntity> orders = customerEntity.getOrders();
    for(OrderEntity orderEntity : orders) {
	...
    }
}

Hibernate 4.3.5 (as shipped with JBoss AS Wildfly 8.1.0CR2) will generate the following series of SQL statements out of it for only two(!) customers in the database:

Hibernate: 
     select
         customeren0_.id as id1_1_,
         customeren0_.name as name2_1_,
         customeren0_.numberOfPurchases as numberOf3_1_ 
     from
         CustomerEntity customeren0_
Hibernate: 
     select
         orders0_.CUSTOMER_ID as CUSTOMER4_1_0_,
         orders0_.id as id1_2_0_,
         orders0_.id as id1_2_1_,
         orders0_.campaignId as campaign2_2_1_,
         orders0_.CUSTOMER_ID as CUSTOMER4_2_1_,
         orders0_.timestamp as timestam3_2_1_ 
     from
         OrderEntity orders0_ 
     where
         orders0_.CUSTOMER_ID=?
Hibernate: 
     select
         orders0_.CUSTOMER_ID as CUSTOMER4_1_0_,
         orders0_.id as id1_2_0_,
         orders0_.id as id1_2_1_,
         orders0_.campaignId as campaign2_2_1_,
         orders0_.CUSTOMER_ID as CUSTOMER4_2_1_,
         orders0_.timestamp as timestam3_2_1_ 
     from
         OrderEntity orders0_ 
     where
         orders0_.CUSTOMER_ID=?

As we can see, the first query selects all customers from the table CustomerEntity. The following two selects fetch then the orders for each customer we have loaded in the first query. When we have 100 customers instead of two, we will get 101 queries. One initial query to load all customers and then for each of the 100 customers an additional query for the orders. That is the reason why this problem is called N+1.

A common idiom to solve this problem is to force the ORM to generate an inner join query. In JPQL this can be done by using the JOIN FETCH clause like demonstrated in the following code snippet:

entityManager.createQuery("SELECT c FROM CustomerEntity AS c JOIN FETCH c.orders AS o", CustomerEntity.class).getResultList();

As expected the ORM now generates an inner join with the OrderEntity table and therewith only needs one SQL statement to load all data:

select
    customeren0_.id as id1_0_0_,
    orders1_.id as id1_1_1_,
    customeren0_.name as name2_0_0_,
    orders1_.campaignId as campaign2_1_1_,
    orders1_.CUSTOMER_ID as CUSTOMER4_1_1_,
    orders1_.timestamp as timestam3_1_1_,
    orders1_.CUSTOMER_ID as CUSTOMER4_0_0__,
    orders1_.id as id1_1_0__
from
    CustomerEntity customeren0_
inner join
    OrderEntity orders1_
        on customeren0_.id=orders1_.CUSTOMER_ID

In situations where you know that you will have to load all orders for each customer, the JOIN FETCH clause minimizes the number of SQL statements from N+1 to 1. This comes of course with the drawback that you now transfer for all orders of one customer the customer data again and again (due to the additional customer columns in the query).

The JPA specification introduces with version 2.1 so called NamedEntityGraphs. This annotation lets you describe the graph a JPQL query should load in more detail than a JOIN FETCH clause can do and therewith is another solution to the N+1 problem. The following example demonstrates a NamedEntityGraph for our customer entity that is supposed to load only the name of the customer and its orders. The orders are described in the subgraph ordersGraph in more detail. Here we see that we only want to load the fields id and campaignId of the order.

@NamedEntityGraph(
        name = "CustomersWithOrderId",
        attributeNodes = {
                @NamedAttributeNode(value = "name"),
                @NamedAttributeNode(value = "orders", subgraph = "ordersGraph")
        },
        subgraphs = {
                @NamedSubgraph(
                        name = "ordersGraph",
                        attributeNodes = {
                                @NamedAttributeNode(value = "id"),
                                @NamedAttributeNode(value = "campaignId")
                        }
                )
        }
)

The NamedEntityGraph is given as a hint to the JPQL query, after it has been loaded via EntityManager using its name:

EntityGraph entityGraph = entityManager.getEntityGraph("CustomersWithOrderId");
entityManager.createQuery("SELECT c FROM CustomerEntity AS c", CustomerEntity.class).setHint("javax.persistence.fetchgraph", entityGraph).getResultList();

Hibernate supports the @NamedEntityGraph annotation since version 4.3.0.CR1 and creates the following SQL statement for the JPQL query shown above:

Hibernate: 
    select
        customeren0_.id as id1_1_0_,
        orders1_.id as id1_2_1_,
        customeren0_.name as name2_1_0_,
        customeren0_.numberOfPurchases as numberOf3_1_0_,
        orders1_.campaignId as campaign2_2_1_,
        orders1_.CUSTOMER_ID as CUSTOMER4_2_1_,
        orders1_.timestamp as timestam3_2_1_,
        orders1_.CUSTOMER_ID as CUSTOMER4_1_0__,
        orders1_.id as id1_2_0__ 
    from
        CustomerEntity customeren0_ 
    left outer join
        OrderEntity orders1_ 
            on customeren0_.id=orders1_.CUSTOMER_ID

We see that Hibernate does not issue N+1 queries but that instead the @NamedEntityGraph annotation has forced Hibernate to load the orders per left outer join. This is of course a subtle difference to the FETCH JOIN clause, where Hibernate created an inner join. The left outer join would also load customers for which no order exists in contrast to the FETCH JOIN clause, where we would only load customers that have at least one order.

Interestingly is also that Hibernate loads more than the specified attributes for the tables CustomerEntity and OrderEntity. As this conflicts with the specification of @NamedEntityGraph (section 3.7.4) I have created an JIRA issue for that.

Conclusion

We have seen that with JPA 2.1 we have two solutions for the N+1 problem: We can either use the FETCH JOIN clause to eagerly fetch a @OneToMany relation, which results in an inner join, or we can use @NamedEntityGraph feature that lets us specify which @OneToMany relation to load via left outer join.

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


1 × six =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books