You need to execute a complex query that involves multiple tables and conditional logic. How do you implement this in Spring JPA?

For queries that span multiple tables with conditional logic beyond what derived query method names can express, Spring Data JPA's @Query annotation lets you write the query explicitly, either as JPQL (object-oriented, entity-based) or native SQL, directly on the repository method.

Key Points: • JPQL queries reference entity names and their fields rather than table and column names, keeping the query somewhat database-agnostic. • nativeQuery = true switches @Query to accept raw SQL when you need database-specific syntax or performance the JPA provider can't generate on its own. • Named parameters via @Param keep the query readable and less error-prone than positional parameters. • For queries where the number of conditions varies at runtime, the Specification API (JPA Criteria under the hood) or QueryDSL is often a better fit than a fixed @Query string. • Complex read-heavy multi-table queries returning a subset of fields can be projected directly into a DTO constructor from within the JPQL query itself.

Example: Finding all orders for a customer that are both above a certain amount and placed within a date range, joined against the customer table for the customer's name, is a natural fit for a JPQL query with multiple WHERE conditions and a JOIN, written directly with @Query.

Code Example:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN o.customer c " +
           "WHERE c.id = :customerId AND o.total > :minTotal " +
           "AND o.orderDate BETWEEN :start AND :end")
    List<Order> findLargeRecentOrders(@Param("customerId") Long customerId,
                                       @Param("minTotal") BigDecimal minTotal,
                                       @Param("start") LocalDate start,
                                       @Param("end") LocalDate end);
}

Interview Tip: A concise interview answer is:

"For multi-table queries with real conditional logic, I write an explicit @Query using JPQL, joining across entities and binding named parameters with @Param, falling back to nativeQuery = true only when I need database-specific SQL. If the number of filter conditions varies dynamically at runtime, I'd reach for the Specification API instead of a fixed query string."