Spring Data queries: derived, @Query and projections

Query method design in Spring Data can feel like choosing between power and simplicity. Every repository you write accumulates dozens of methods, and each one trades off readability against flexibility. We'll walk through the practical patterns that work at scale: derived queries for the straightforward cases, explicit @Query annotations when things get complex, interface projections to avoid loading unnecessary columns, and native queries for database-specific features.

Derived queries — Great for simple filters

Derived query methods read like English and generate SQL automatically. Spring Data parses the method name and builds the where clause for you. For simple predicates—a single column check, two columns combined with AND, a date range—derived queries stay readable and require zero boilerplate.

java

public interface UserRepository extends JpaRepository<User, Long> {
  List<User> findByEmailAndActiveTrue(String email);
  
  List<User> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
  
  Optional<User> findByEmailIgnoreCase(String email);
  
  List<User> findByRoleInAndActiveTrue(List<String> roles);
}

This approach shines when your filter logic is straightforward. The method name documents the intent without requiring you to read SQL. The repository layer stays thin. And Spring Data handles pagination and sorting seamlessly if you add Pageable as a parameter.

The constraint is readability. Once a method name spans multiple lines or combines more than three conditions, the naming convention becomes a liability. Method names like findByEmailAndActiveTrueAndRoleInAndCreatedAtGreaterThanAndLastLoginIsNotNull defeat the purpose. That's the signal to switch approaches.

For optional OR conditions or complex boolean logic, derived queries force you into corners. You can't express "email OR phone equals this value" cleanly. You also can't add custom sorting or filtering logic without jumping to @Query.

@Query for anything longer — Method names longer than a line are a smell

When derived queries get unwieldy, @Query lets you write JPQL or SQL directly. This is your escape hatch. Use it for multi-table joins, conditional logic that doesn't fit the naming convention, or database-specific optimizations.

java

public interface OrderRepository extends JpaRepository<Order, Long> {
  @Query("SELECT o FROM Order o " +
         "WHERE o.user.id = :userId " +
         "AND o.status IN ('PENDING', 'SHIPPED') " +
         "AND o.createdAt >= :since " +
         "ORDER BY o.createdAt DESC")
  List<Order> findUserRecentOrders(@Param("userId") Long userId, 
                                      @Param("since") LocalDateTime since);
}

JPQL stays portable across databases. It works with JPA's object model, so you reference entity fields and relationships rather than table columns. If you migrate databases later, the query usually still works.

When you need database-specific features—window functions, CTEs, specialized indexes, or vendor-specific syntax—switch to native SQL. Set nativeQuery = true and accept that this repository is now tied to PostgreSQL, MySQL, or whatever you're running.

java

public interface AnalyticsRepository extends JpaRepository<SaleEvent, Long> {
  @Query(value = 
    "SELECT user_id, COUNT(*) as purchase_count, " +
    "ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as rank " +
    "FROM sales WHERE created_at >= :since GROUP BY user_id",
    nativeQuery = true)
  List<Object[]> topBuyersByDay(@Param("since") LocalDateTime since);
}

The trade-off is immediate: you now maintain SQL strings in your codebase. IDE support is weaker. You lose some of the type safety that JPQL provides. For straightforward queries, this overhead isn't worth it. For complex reporting or performance-critical paths, it often is.

A practical rule: try derived queries first, then @Query with JPQL, then native SQL only when you need database-specific features or have benchmarked and found it necessary.

Interface projections — Select only the columns you need

Loading entire entities when you only need a handful of fields wastes memory and database bandwidth. Interface projections let you fetch only what you need and bind it to a lightweight DTO-like interface at query time.

java

public interface UserSummary {
  String getName();
  String getEmail();
}

public interface UserRepository extends JpaRepository<User, Long> {
  List<UserSummary> findByActiveTrue();
}

Spring Data generates a dynamic proxy that maps result columns to the interface methods. No mapping layer, no manual DTO construction. The database query only selects the columns you declared in the interface.

This scales well. If you have a reporting endpoint that renders user summaries across thousands of records, projections cut network and memory overhead. You also decouple the API contract (the summary) from the internal entity model. The User entity can gain new fields without affecting the projection.

Projections work with derived queries, @Query, and pagination. You can also compose them with nested interfaces if your data model is hierarchical.

java

public interface OrderSummary {
  Long getId();
  LocalDateTime getCreatedAt();
  
  UserSummary getUser();  // nested projection
  
  default String getDisplayName() {
    return "Order #" + getId();  // computed properties
  }
}

public interface OrderRepository extends JpaRepository<Order, Long> {
  List<OrderSummary> findByStatusAndCreatedAtAfter(
    String status, 
    LocalDateTime since);
}

Class-based projections (concrete DTOs with a constructor matching the selected columns) are also available if you prefer explicit mapping. They're heavier to maintain but give you full control.

A word of caution: projections don't work with all queries. If you use @Modifying or custom repository methods with template callbacks, projections may not apply. Test them in your integration suite to confirm the SQL reflects your expectations.

Native queries — Use them for database-specific features, and cover them with integration tests

Native SQL queries are your final tool when JPQL can't express what you need. PostgreSQL window functions, recursive CTEs, bulk operations, or vendor-specific performance hints all require native queries.

java

public interface ReportRepository extends JpaRepository<Report, Long> {
  @Query(value = 
    "WITH RECURSIVE dates AS (" +
    "  SELECT CURRENT_DATE as report_date " +
    "  UNION ALL " +
    "  SELECT report_date - INTERVAL '1 day' FROM dates " +
    "  WHERE report_date > :startDate" +
    ") " +
    "SELECT COALESCE(d.report_date, e.event_date) as date, " +
    "COUNT(e.id) as event_count " +
    "FROM dates d LEFT JOIN events e ON e.event_date = d.report_date " +
    "GROUP BY d.report_date, e.event_date ORDER BY date DESC",
    nativeQuery = true)
  List<Object[]> dailyEventCounts(@Param("startDate") LocalDate startDate);
}

Native queries require discipline. You're writing SQL strings that won't be validated until runtime. Parameter binding with @Param protects against SQL injection. But typos in column names, missing joins, or dialect mismatches only surface when the method runs.

Integration tests become essential. Before shipping a native query, run it in your real database (or a test database with the same schema and version). Verify the result set matches expectations. Check performance with realistic data volumes. Document why you needed native SQL and which database(s) it targets.

java

@DataJpaTest
public class ReportRepositoryTest {
  @Autowired
  private ReportRepository reportRepository;
  
  @Test
  void dailyEventCountsReturnsCorrectRows() {
    LocalDate start = LocalDate.now().minusDays(10);
    List<Object[]> results = reportRepository.dailyEventCounts(start);
    
    assertThat(results).isNotEmpty();
    assertThat(results).allMatch(row -> 
      row.length == 2 && 
      row[0] instanceof LocalDate &&
      row[1] instanceof Number
    );
  }
}

If you find yourself writing native queries frequently, consider whether your ORM is the right fit or if a dedicated reporting tool might serve you better. Native queries are powerful but add maintenance burden.

Common mistakes

Derived queries that are too long. A method name spanning two lines in your IDE is a signal to use @Query. Readability suffers and renaming becomes painful.
Forgetting projections for high-volume queries. Loading entire entities when you only need a few fields is wasteful. Projections are free performance wins if you remember to use them.
Unparameterized native queries. String concatenation in @Query opens SQL injection vulnerabilities. Always use @Param or positional binding.
Native queries without database tests. A query that works on PostgreSQL 13 might fail on version 14 or MySQL. Test native queries in their target environment.
Mixing concerns in repositories. Business logic doesn't belong in query method names. Keep repositories focused on data access; move filtering and transformation logic to the service layer.

Key takeaways

Derived queries are the default for simple predicates. They're readable and require no SQL knowledge.
Use @Query with JPQL when derived queries get unwieldy. It stays portable and maintainable.
Switch to native SQL only when you need database-specific features or have performance data justifying the extra complexity.
Projections let you fetch only the columns you need, reducing bandwidth and memory overhead. Use them liberally in read-heavy paths.
Write integration tests for native queries. Database-specific SQL needs verification in your actual environment.
Choose the simplest query style that stays readable. Avoid clever naming conventions; use @Query instead.

---

Spring Data queries: derived, @Query and projections