What is the best optimized way to select many entities with subentities in JPA?

Question

Let's say we have:

@Entity public class Order {
  @Id private int id;
  @OneToMany(mappedBy="order") private List<Item> items;
  ...
}

and

@Entity public class Item {
  @Id private int id;
  @ManyToOne private Order order;
  ...
}

And let's say there is 10.000 orders with each having 20 items.

We need to iterate thought all order and all their items. What is the best way to do it in JPA?

My issue is, if I just iterate the elements like:

for (Order order: em.createTypeQuery("select o from Order o", Order.class).getResultList()) {
    report.printOrder(order);
    for (Item item: order.getItems()) {
        report.printOrderItem(item);
    }
}

it will result in 10.001 sql queries: 1 time: select * from order 10.000 times: select * from item where order_id = ?

Is there any way to optimize it? Two queries? One query?

(We are using EclipseLink)

Thanks.

Answer 1

You may use join fetch (this also requires distinct since join fetch have a join semantics):

select distinct o from Order o join fetch o.items

Answer 2

You may also want to consider the EclipseLink query Hint "eclipselink.batch" with value "o.items". This results in two queries but can be more effecient than once large joined query.