Implementing the Fusion Pattern with Hibernate Pagination in Java

Desired Outcome

The goal is to expose a single, paginated API endpoint that returns a merged view of records from two or more Hibernate‑managed tables. The merged result must respect the requested page size, avoid the N+1 query problem, and maintain consistent transaction isolation.

Prerequisites

• Java 17 or later with JPA 2.2+ (Hibernate 6.x preferred).
• Two entity classes mapped to separate tables (e.g., Order and Invoice).
• Spring Boot or a CDI container to manage transactions.
• Database with sufficient indexes on the columns used for sorting and filtering.

Procedure

1. Define the Unified DTO

   Create a simple POJO that will hold fields from both entities. Keep it immutable to avoid accidental changes during merging.

   public record UnifiedRecord(Long id, String type, LocalDateTime createdAt, String description) {}
   

2. Configure EntityGraphs to Avoid N+1

   For each entity, define an EntityGraph that eagerly loads the necessary associations. This prevents lazy loading during the merge.

   @Entity
   @EntityGraph(name = "Order.full", attributeNodes = @NamedAttributeNode("customer"))
   public class Order { … }
   

3. Implement Repository Methods with Pagination

   Use setFirstResult() and setMaxResults() to fetch a page from each source. Since offset‑based pagination can degrade for large offsets, consider keyset pagination if the dataset is >1M rows.

   public List findOrders(int page, int size) {
       Query q = em.createQuery("SELECT o FROM Order o ORDER BY o.createdAt DESC", Order.class)
                   .setFirstResult(page * size)
                   .setMaxResults(size);
       return q.getResultList();
   }
   

4. Merge Result Sets in the Service Layer

   Collect the paginated lists, convert each to the DTO, concatenate, sort by the common key (e.g., createdAt), and slice the final list to the requested page size. This step is performed in memory; keep an eye on heap usage.

   public Page fuse(int page, int size) {
       List orders = orderRepo.findOrders(page, size);
       List invoices = invoiceRepo.findInvoices(page, size);
   
       List merged = Stream.concat(
               orders.stream().map(o -> new UnifiedRecord(o.getId(), "ORDER", o.getCreatedAt(), o.getDescription())),
               invoices.stream().map(i -> new UnifiedRecord(i.getId(), "INVOICE", i.getCreatedAt(), i.getDescription()))
       ).sorted(Comparator.comparing(UnifiedRecord::createdAt).reversed())
         .collect(Collectors.toList());
   
       int fromIndex = Math.min(page * size, merged.size());
       int toIndex = Math.min(fromIndex + size, merged.size());
       List pageContent = merged.subList(fromIndex, toIndex);
   
       return new PageImpl<>(pageContent, PageRequest.of(page, size), merged.size());
   }
   

5. Wrap in a Transaction

   Annotate the service method with @Transactional(readOnly = true) to ensure consistent isolation across both queries.

Expected Checks

• Verify SQL logs show LIMIT and OFFSET clauses matching the requested page.
• Confirm the merged list length equals the sum of individual counts for the page; no records should be omitted.
• Benchmark first vs. last page response times; a significant increase indicates offset‑based slowdown.

Recovery Options

• Memory Issues – If the merged list grows beyond available heap, switch to a streaming merge: iterate over each source, add to a priority queue, and drain only the needed page.
• Performance Degradation – Replace offset pagination with keyset pagination: use the last seen createdAt value as a cursor.
• Inconsistent Isolation – If phantom reads occur, raise the isolation level to REPEATABLE_READ or use snapshot isolation if supported by the DB.

Limitations and Practical Checks

• Memory overhead: merging large pages can consume several gigabytes. Monitor Runtime.getRuntime().totalMemory() before and after the merge.
• Transaction boundaries: if one source is read‑only and the other is write‑capable, separate transactions may be required to avoid locking conflicts.
• SQL injection risk: never concatenate raw parameters into JPQL; always use named parameters.
• Testing: create unit tests that mock the repositories and assert the merged page contains expected records.

Conclusion

The Fusion pattern can be safely applied in Java/Hibernate environments by carefully managing pagination, eager loading, and in‑memory merging. Keep an eye on performance metrics and memory usage, and be prepared to switch to keyset pagination or streaming merges for very large datasets.