Spring Data JPA @Query: Custom JPQL, Native Queries, and Named Queries

If you've used Spring Data JPA for more than a week, you've hit the wall where derived query methods just don't cut it. Maybe you need a complex join across three tables, an aggregation with grouping, or a database-specific function call. That's where @Query comes in—your escape hatch from the magic method naming convention.

I've seen teams abuse @Query in ways that make production support engineers cry. I once debugged a production outage at 2 AM where a native query with unvalidated input caused a full table scan on a 50-million-row table. The developer had just copied a query from a SQL client without understanding the execution plan. The fix? A simple JPQL query with proper indexing.

In this article, I'll walk you through the three flavors of custom queries in Spring Data JPA: JPQL, native queries, and named queries. I'll show you when to use each, what the official docs gloss over, and how to debug them when things go wrong. By the end, you'll write queries that are both powerful and maintainable—and you'll know exactly what to do when a query brings your application to its knees.

What is @Query in Spring Data JPA?

The @Query annotation is your escape hatch when derived query methods (like findByLastNameAndFirstName) become too complex or impossible. It lets you define custom JPQL or native SQL queries directly on repository methods. I've used it for everything from simple filtering to complex reporting queries.

Here's the basic syntax:

@Query("SELECT u FROM User u WHERE u.email = :email") User findByEmail(@Param("email") String email);

This is JPQL (Java Persistence Query Language)—it operates on entity objects and their fields, not database tables and columns. It's database-agnostic and type-safe. Under the hood, Spring Data JPA creates a proxy that executes this query when the method is called.

You can also use native queries:

@Query(value = "SELECT * FROM users u WHERE u.email = :email", nativeQuery = true) User findByEmailNative(@Param("email") String email);

Native queries bypass the entity abstraction and talk directly to the database. They're powerful but dangerous—you lose portability and risk SQL injection if you concatenate parameters.

Key point: Always use @Param for named parameters. Positional parameters (?1, ?2) are error-prone and deprecated in JPA 2.0+. I can't count the number of times I've seen bugs caused by reordering method parameters without updating the query.

Another thing: @Query methods don't need to follow the naming convention. You can name them anything you want. But please, for the love of clean code, give them descriptive names like findActiveOrdersByCustomerId, not just getOrders.

public interface UserRepository extends JpaRepository<User, Long> {

    // JPQL with named parameter
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);

    // Native query
    @Query(value = "SELECT * FROM users u WHERE u.email = :email", nativeQuery = true)
    User findByEmailNative(@Param("email") String email);

    // JPQL with multiple parameters
    @Query("SELECT u FROM User u WHERE u.lastName = :lastName AND u.age > :age")
    List<User> findByLastNameAndAgeGreaterThan(@Param("lastName") String lastName, @Param("age") int age);
}

JPQL vs Native Queries: When to Use Which

This is a religious debate in some teams, but here's my take after 15 years: use JPQL by default, native queries only when you must.

JPQL operates on your entity model. It's database-agnostic, so you can switch from PostgreSQL to Oracle without changing your queries. It's also type-safe—you get compilation errors for invalid property names. And it integrates with the persistence context, so changes made during the current transaction are visible to JPQL queries (the 'flush mode' can trip you up, but more on that later).

Here's a real example: I worked on a SaaS billing system where we needed to calculate usage across multiple time zones. PostgreSQL's date/time functions were perfect, but JPQL doesn't support AT TIME ZONE. We used a native query for that specific report.

Rule of thumb: If you can write it in JPQL, do it. If you can't, consider redesigning your entity model first. Only then reach for native queries.

public interface OrderRepository extends JpaRepository<Order, Long> {

    // JPQL - database agnostic
    @Query("SELECT o FROM Order o WHERE o.createdAt >= :since")
    List<Order> findRecentOrders(@Param("since") LocalDateTime since);

    // Native query - PostgreSQL specific
    @Query(value = "SELECT * FROM orders WHERE created_at >= :since " +
           "AND EXTRACT(EPOCH FROM (NOW() - created_at)) > :threshold",
           nativeQuery = true)
    List<Order> findSlowOrders(@Param("since") LocalDateTime since,
                                @Param("threshold") long thresholdSeconds);
}

Named Queries: Organizing Reusable JPQL

Named queries let you define JPQL queries in a central location (either on the entity class or in a JPA XML file) and reference them by name in your repository. They're great for queries that are used in multiple places or that you want to keep separate from the repository interface.

Here's how to define a named query on an entity:

@Entity @NamedQuery(name = "User.findByEmail", query = "SELECT u FROM User u WHERE u.email = :email") public class User { ... }

Then in your repository:

public interface UserRepository extends JpaRepository<User, Long> { User findByEmail(@Param("email") String email); // Spring Data automatically maps to the named query }

Wait, that looks just like a derived query! The magic is that Spring Data JPA checks for a named query matching the method name before falling back to derived query generation. So if you define a named query called "User.findByEmail", Spring Data will use it.

You can also explicitly reference a named query:

@Query(name = "User.findByEmail") User findByEmail(@Param("email") String email);

Named queries are compiled and validated at startup, so you catch errors early. They also make it easy to share queries across repositories or even across different persistence units.

But there's a catch: named queries are static. You can't dynamically modify them. For dynamic queries, you need Spring Data JPA Specifications or QueryDSL.

@Entity
@NamedQueries({
    @NamedQuery(name = "User.findByEmail",
                query = "SELECT u FROM User u WHERE u.email = :email"),
    @NamedQuery(name = "User.findActiveUsers",
                query = "SELECT u FROM User u WHERE u.active = true")
})
public class User {
    @Id
    private Long id;
    private String email;
    private boolean active;
    // getters and setters
}

What the Official Docs Won't Tell You

I've been burned by these gotchas, and I want you to avoid them.

1. The Flush Mode Trap

By default, Hibernate flushes the persistence context before a JPQL query to ensure consistency. But if you set flushMode = COMMIT (for performance), your query may not see uncommitted changes. This caused a bug in a billing system where a discount was applied but not visible to a subsequent query, resulting in incorrect invoices.

2. Native Queries and the Persistence Context

Native queries don't trigger a flush. If you save an entity and then run a native query that reads the same table, the saved entity may not be visible. Always call entityManager.flush() explicitly or set the flush mode to AUTO before native queries that need to see recent changes.

3. Pageable and Native Queries

When you add Pageable to a native query, Spring Data JPA generates a count query automatically. But the generated count query may fail if your native query has complex syntax (e.g., CTEs, window functions). You can provide a custom count query using countQuery parameter:

@Query(value = "...", countQuery = "SELECT COUNT(*) FROM ...", nativeQuery = true) Page<Order> findOrders(Pageable pageable);

4. SpEL in @Query

Spring Data JPA supports SpEL in @Query, but it's limited. You can use ?#{#entityName} to refer to the entity name dynamically. This is useful in a base repository interface. But SpEL for parameters (like ?#{[0]}) is fragile and I've seen it cause ClassCastException.

5. DTO Projections with @Query

You can return DTOs directly from @Query using JPQL constructor expressions:

@Query("SELECT new com.example.UserDTO(u.id, u.name) FROM User u") List<UserDTO> findAllUserDTOs();

But the DTO class must have a matching constructor. This is compile-time safe and more efficient than mapping entities manually. However, if the constructor changes, you'll get a runtime error.

6. Locking and @Query

You can add pessimistic locking to a @Query using @Lock:

@Lock(LockModeType.PESSIMISTIC_WRITE) @Query("SELECT u FROM User u WHERE u.id = :id") Optional<User> findByIdWithLock(@Param("id") Long id);

But if you use native queries, locking is database-specific and you must add FOR UPDATE manually. Also, be aware that locking interacts with transaction isolation levels—test thoroughly.

7. The N+1 Problem with @Query

Even if your @Query fetches entities, lazy associations will still trigger additional queries when accessed. Use JOIN FETCH in JPQL to eagerly load associations:

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id") Order findByIdWithItems(@Param("id") Long id);

For native queries, you can't use JOIN FETCH; you'll need to manually map the result set or use a second query.

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Custom count query for native queries with complex syntax
    @Query(value = "SELECT * FROM orders WHERE status = :status ORDER BY created_at DESC",
           countQuery = "SELECT COUNT(*) FROM orders WHERE status = :status",
           nativeQuery = true)
    Page<Order> findByStatus(@Param("status") String status, Pageable pageable);

    // JPQL with JOIN FETCH to avoid N+1
    @Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") Long id);

    // DTO projection with constructor expression
    @Query("SELECT new com.example.dto.OrderSummary(o.id, o.total, o.createdAt) FROM Order o")
    List<OrderSummary> findAllSummaries();
}

Dynamic Queries with @Query: Using SpEL and Conditional Logic

Sometimes you need a query that changes based on input parameters. Spring Data JPA offers limited support for dynamic queries via SpEL (Spring Expression Language) in @Query. But let me be blunt: it's not a replacement for a proper dynamic query framework like Specifications or QueryDSL.

You can use SpEL to conditionally include parts of a query:

@Query("SELECT u FROM User u WHERE (:name is null OR u.name = :name) AND (:email is null OR u.email = :email)") List<User> findUsers(@Param("name") String name, @Param("email") String email);

This works for small variations, but the query plan may not be optimal because the database can't cache it effectively. For complex dynamic queries, use Specifications.

Another SpEL trick: ?#{#entityName} gives you the entity name. This is useful in a base repository:

@MappedSuperclass public class BaseEntity { ... }

@NoRepositoryBean public interface BaseRepository<T extends BaseEntity> extends JpaRepository<T, Long> { @Query("SELECT e FROM #{#entityName} e WHERE e.active = true") List<T> findAllActive(); }

This way, each sub-interface gets the correct entity name injected automatically.

But SpEL has limits. You can't use it to dynamically add JOIN clauses or change the SELECT list. For those, you need Criteria API or QueryDSL.

My advice: Keep @Query static. If you need dynamic queries, use Specifications (which integrate with Spring Data JPA) or QueryDSL. They're more verbose but safer and more maintainable.

@NoRepositoryBean
public interface BaseRepository<T extends BaseEntity> extends JpaRepository<T, Long> {

    @Query("SELECT e FROM #{#entityName} e WHERE e.active = true")
    List<T> findAllActive();
}

// Usage
public interface UserRepository extends BaseRepository<User> {
    // Inherits findAllActive() which resolves to "SELECT u FROM User u WHERE u.active = true"
}

Testing @Query Methods: Ensuring Correctness and Performance

Testing @Query methods is non-negotiable. I've seen too many queries that work in development with 10 rows but fail in production with 10 million rows. Here's my testing strategy:

Unit Tests with @DataJpaTest

Use @DataJpaTest to test your repository in isolation. It sets up an in-memory database (H2) and only loads JPA components. You can test that the query returns the expected results:

@ExtendWith(SpringExtension.class) @DataJpaTest class UserRepositoryTest {

@Autowired private UserRepository userRepository;

@Test void findByEmail_ShouldReturnUser() { User user = new User("test@example.com"); userRepository.save(user);

User found = userRepository.findByEmail("test@example.com");

assertThat(found).isNotNull(); assertThat(found.getEmail()).isEqualTo("test@example.com"); } }

Integration Tests with Testcontainers

In-memory databases often behave differently from your production database. For example, H2 doesn't support all PostgreSQL-specific functions. Use Testcontainers to spin up a real database container:

@Testcontainers @SpringBootTest class UserRepositoryIntegrationTest {

@Container static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15");

@DynamicPropertySource static void configureProperties(DynamicPropertyRegistry registry) { registry.add("spring.datasource.url", postgres::getJdbcUrl); registry.add("spring.datasource.username", postgres::getUsername); registry.add("spring.datasource.password", postgres::getPassword); }

@Autowired private UserRepository userRepository;

@Test void testNativeQuery() { // Test native queries against real PostgreSQL } }

Performance Testing

For queries expected to handle large datasets, write performance tests with realistic data volumes. Use @DataJpaTest with a large dataset (e.g., 100k records) and measure execution time. If a query takes >100ms, investigate indexing.

SQL Logging

Always enable SQL logging during development:

spring.jpa.show-sql=true spring.jpa.properties.hibernate.format_sql=true

This lets you see the actual SQL generated by your JPQL queries. Compare it with what you expect. For native queries, you see the exact SQL you wrote.

What about @Query validation?

Spring Data JPA validates @Query syntax at startup? No! That's a common misconception. Inline @Query strings are only parsed when the method is first called. Named queries are validated at startup. So if you have a typo in a @Query, you won't know until someone calls it. I've seen this cause production outages where a rarely-used endpoint suddenly fails after a deployment.

Mitigation: Write integration tests that call every @Query method. Or use a tool like ArchUnit to enforce that every @Query method has a corresponding test.

@ExtendWith(SpringExtension.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // Use Testcontainers
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    void findByEmail_ShouldReturnUser() {
        User user = new User("test@example.com");
        userRepository.save(user);

        User found = userRepository.findByEmail("test@example.com");

        assertThat(found).isNotNull();
        assertThat(found.getEmail()).isEqualTo("test@example.com");
    }

    @Test
    void findActiveUsers_ShouldReturnOnlyActive() {
        User active = new User("active@example.com", true);
        User inactive = new User("inactive@example.com", false);
        userRepository.saveAll(List.of(active, inactive));

        List<User> activeUsers = userRepository.findActiveUsers();

        assertThat(activeUsers).hasSize(1);
        assertThat(activeUsers.get(0).getEmail()).isEqualTo("active@example.com");
    }
}