How to avoid 1 + n database calls for a bidirectional optional one to one association in Hibernate?

Question

Having the following simplified entities:

@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;
}

@Entity
@Table(name = "t_invoice")
public class Invoice extends AbstractEntity {
    @OneToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "order_id")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private Order order;
}

@Entity
@Table(name = "t_order")
public class Order extends AbstractEntity {
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    @SortNatural
    private SortedSet<OrderLine> orderLines = new TreeSet<>();

    @OneToOne(optional = true, mappedBy = "order", fetch = FetchType.EAGER)
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private Invoice invoice;
}

and following repository using Spring data

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {
    List<Invoice> findDistinctByInvoiceDateBetween(LocalDate from, LocalDate until);
}

when fetching the invoices using the repository method 1 + n SQL statements are executed as is shown in the logs:

SELECT DISTINCT i.id, ... FROM t_invoice i WHERE i.invoice_date BETWEEN ? AND ?;
SELECT i.id, ... FROM t_invoice i WHERE i.order_id = ?;
SELECT i.id, ... FROM t_invoice i WHERE i.order_id = ?;
... n

From this SO answer I understand that when having a one to one optional association, Hibernate needs to make the n database calls to determine if the optional invoice in order is null or not. What confuses me is that Hibernate already has the invoice in question fetched in the initial query, so why would it not use the data from invoice already fetched?

I also tried avoiding the n calls by using @NamedEntityGraph and @NamedSubgraph to eagerly populate invoice in order.

Thus now the invoice entity looks like:

@Entity
@NamedEntityGraph(
        name = Invoice.INVOICE_GRAPH,
        attributeNodes = {
                @NamedAttributeNode(value = "order", subgraph = "order.subgraph")
        },
        subgraphs = {
                @NamedSubgraph(name = "order.subgraph", attributeNodes = {
                        @NamedAttributeNode("invoice"),
                        @NamedAttributeNode("orderLines")
                }),
        }
)
@Table(name = "t_invoice")
public class Invoice extends AbstractEntity {
@OneToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "order_id")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private Order order;
}

and the method in the repository looks like:

@EntityGraph(value = Invoice.INVOICE_GRAPH, type = EntityGraph.EntityGraphType.LOAD)
List<Invoice> findDistinctByInvoiceDateBetween(LocalDate from, LocalDate until);

But it still makes the n database calls even though the first sql select clause contains invoice data twice as you can see:

SELECT DISTINCT
    invoice0_.id                      AS id1_13_0_,
    order1_.id                        AS id1_14_2_,
    orderlines4_.id                   AS id1_15_4_,
    invoice5_.id                      AS id1_13_5_,
    invoice0_.created                 AS created2_13_0_,
    order1_.created                   AS created2_14_2_,
    orderlines4_.created              AS created2_15_4_,
    invoice5_.created                 AS created2_13_5_,
FROM t_invoice invoice0_ ... more join clausules ... 
WHERE invoice0_.order_id = order1_.id AND (invoice0_.invoice_date BETWEEN ? AND ?)

So now I'm wondering how you would avoid the n extra calls to populate invoice in order?

Answer 1

I understand that when having a one to one optional association, Hibernate needs to make the n database calls to determine if the optional invoice in order is null or not

Yes. More precicely, hibernate does not support laziness for optional ToOne associations, so it will always load the associated data.

What confuses me is that Hibernate already has the invoice in question fetched in the initial query, so why would it not use the data from invoice already fetched?

Hibernate does not realize that it has already loaded that invoice. To do so, it would either have to keep an map of Invoice objects by their order_id, or have special handling for mutual OneToOne associations. OneToOne associations being rare, it does not have such handling.

This can be solved by any of the following approaches:

• only map one end of the association, and use a query to navigate in the other direction (i.e. when you want the invoice for an order, do "select invoice from invoice where invoice.order = ?")
• map both ends of the association, but make the optional end the primary mapping, i.e. move the foreign key from order to invoice. That way, the invoice for an order is referred to by its primary key, which hibernate will be smart enough to look up in the persistence context first, and the order for an invoice is lazy, so hibernate will only issue a redundant query if that property is actually accessed.

Which of these is a better solution for your problem depends on the other queries operating on that data. In general, I'd favor the first option, because having to query for data that is not mapped is easier to understand for programmers than having to do arcane tricks to get JPA to not load implicitly requested data.