Spring Data JPA: Repository, Query Methods, and @Query

1. Overview

Spring Data JPA helps you write the data access layer with almost no implementation code. You only need to declare an interface, and Spring will auto-generate SQL statements based on the method name or the @Query annotation.

This article covers three main parts: how to create a basic repository, naming conventions for query methods so Spring can infer WHERE clauses, and how to use @Query when method names aren’t expressive enough or when you need complex JPQL/native SQL. After reading, you’ll know how to choose the right approach for each real-world scenario.

2. Setting up the project

Add Spring Data JPA dependency and a database driver (for example, H2 for demo):

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Sample entity used throughout the article:

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String customerEmail;
    private BigDecimal totalAmount;
    private OrderStatus status;
    private LocalDateTime createdAt;

    // getters, setters
}

public enum OrderStatus {
    PENDING, PAID, SHIPPED, CANCELLED
}

Order has simple fields: customer email, total amount, status, and creation timestamp. The query examples below all revolve around this entity.

3. Basic Repository

Just extend JpaRepository, and Spring Boot will automatically create a bean implementation at startup:

public interface OrderRepository extends JpaRepository<Order, Long> {
}

JpaRepository<Order, Long> tells Spring that the entity managed is Order and the primary key type is Long. This interface already includes methods like save(), findById(), findAll(), deleteById() — no need to write any additional code.

@Service
public class OrderService {

    private final OrderRepository orderRepository;

    public OrderService(OrderRepository orderRepository) {
        this.orderRepository = orderRepository;
    }

    public Order createOrder(Order order) {
        return orderRepository.save(order);
    }
}

Calling save() will INSERT if id is null, UPDATE if id already exists. This is the default behavior of SimpleJpaRepository — the implementation that Spring Data auto-generates.

Note: save() doesn’t distinguish “create new” from “update” based on business logic, but based on whether id is null. If you manually set id before calling save(), Spring will treat it as an update and perform findById() + merge, causing an unnecessary extra SELECT query.

4. Query Methods: Naming to generate queries

4.1. Basic Rules

Spring Data JPA parses method names according to patterns like findBy, existsBy, countBy, deleteBy combined with field names and condition keywords:

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByStatus(OrderStatus status);

    List<Order> findByCustomerEmailAndStatus(String email, OrderStatus status);

    Optional<Order> findFirstByCustomerEmailOrderByCreatedAtDesc(String email);

    long countByStatus(OrderStatus status);
}

• findByStatus generates WHERE status = ?.
• findByCustomerEmailAndStatus combines two conditions with AND.
• findFirstByCustomerEmailOrderByCreatedAtDesc fetches 1 record, sorted descending by createdAt — useful for finding a customer’s latest order.
• countByStatus returns a count, not a list.

4.2. Comparison and Range Keywords

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByTotalAmountGreaterThan(BigDecimal amount);

    List<Order> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);

    List<Order> findByStatusIn(List<OrderStatus> statuses);

    List<Order> findByCustomerEmailContainingIgnoreCase(String keyword);
}

GreaterThan, Between, In, ContainingIgnoreCase are keywords that Spring Data JPA supports out of the box, mapping directly to corresponding SQL clauses. ContainingIgnoreCase generates LOWER(customer_email) LIKE LOWER('%keyword%') — convenient for case-insensitive searches.

Test to verify behavior:

@DataJpaTest
class OrderRepositoryTest {

    @Autowired
    private OrderRepository orderRepository;

    @Test
    void shouldFindOrdersByStatus() {
        Order order = new Order();
        order.setStatus(OrderStatus.PAID);
        order.setCustomerEmail("a@test.com");
        order.setTotalAmount(BigDecimal.valueOf(100));
        order.setCreatedAt(LocalDateTime.now());
        orderRepository.save(order);

        List<Order> result = orderRepository.findByStatus(OrderStatus.PAID);

        assertThat(result)
            .hasSize(1)
            .first()
            .extracting(Order::getCustomerEmail)
            .isEqualTo("a@test.com");
    }
}

@DataJpaTest loads only JPA-related context (repositories, entity manager), running faster than @SpringBootTest. By default, it uses an in-memory database and rolls back after each test.

4.3. Pagination and Sorting

public interface OrderRepository extends JpaRepository<Order, Long> {

    Page<Order> findByStatus(OrderStatus status, Pageable pageable);
}

Pageable pageable = PageRequest.of(0, 10, Sort.by("createdAt").descending());
Page<Order> page = orderRepository.findByStatus(OrderStatus.PAID, pageable);

System.out.println(page.getTotalElements());
System.out.println(page.getContent());

Pageable is a special parameter — Spring Data automatically recognizes it and doesn’t treat it as a WHERE condition. Page<Order> returns metadata along with content: total elements, total pages, and the current page’s content.

4.4. When Method Names Become Unwieldy

// Hard to read, easy to misspell keywords
List<Order> findByStatusAndCustomerEmailContainingIgnoreCaseAndCreatedAtAfterOrderByTotalAmountDesc(
    OrderStatus status, String keyword, LocalDateTime after);

A method name this long still works, but is hard to read, difficult to maintain, and the compiler can’t help you check for syntax errors — Spring Data only detects errors at context startup (fail-fast but still runtime). This is when you should switch to @Query.

5. @Query: Controlling Statements Directly

5.1. Basic JPQL

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.totalAmount > :minAmount")
    List<Order> findHighValueOrders(@Param("status") OrderStatus status,
                                     @Param("minAmount") BigDecimal minAmount);
}

@Query writes JPQL — an entity-oriented query language, not raw SQL (uses Order o, o.status instead of table/column names). @Param maps the named parameter in the query to the method parameter, allowing flexible variable names independent of order.

5.2. Native SQL When Needed

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = "SELECT * FROM orders WHERE total_amount > :minAmount LIMIT :limit",
           nativeQuery = true)
    List<Order> findTopOrders(@Param("minAmount") BigDecimal minAmount,
                               @Param("limit") int limit);
}

nativeQuery = true allows you to write actual SQL, using database-specific syntax like LIMIT. The trade-off is that you lose portability across different databases (for example, Oracle doesn’t support LIMIT).

Note: Native queries returning entities still work if column names match the mapping, but if you SELECT into a custom DTO, you need additional @SqlResultSetMapping or projection interfaces — more complex than JPQL.

5.3. Update and Delete with @Modifying

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Modifying
    @Query("UPDATE Order o SET o.status = :newStatus WHERE o.status = :oldStatus")
    int updateStatusBulk(@Param("oldStatus") OrderStatus oldStatus,
                          @Param("newStatus") OrderStatus newStatus);
}

@Modifying tells Spring Data this is a data-changing statement (UPDATE/DELETE), not a SELECT. The method returns int representing the number of affected rows. Without @Modifying, you’ll get InvalidDataAccessApiUsageException because JPA expects a ResultSet to map.

Note: Bulk update via @Modifying goes directly to the database and does not run through entity lifecycle (doesn’t trigger @PreUpdate, doesn’t update entity cache in the persistence context). If your service holds an old Order instance, the in-memory data will be out of sync — use clearAutomatically = true on @Modifying or manually call entityManager.clear().

5.4. Projection: Fetching Only Needed Fields

When you don’t need the entire entity, use interface projection to reduce returned data:

public interface OrderSummary {
    Long getId();
    String getCustomerEmail();
    BigDecimal getTotalAmount();
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o.id AS id, o.customerEmail AS customerEmail, o.totalAmount AS totalAmount " +
           "FROM Order o WHERE o.status = :status")
    List<OrderSummary> findSummariesByStatus(@Param("status") OrderStatus status);
}

Spring Data creates a proxy implementing OrderSummary at runtime, mapping only the 3 selected fields. This approach avoids loading the entire entity when you only need a summary list — reducing load on both query and memory.

Test for @Query:

@DataJpaTest
class OrderRepositoryQueryTest {

    @Autowired
    private OrderRepository orderRepository;

    @Test
    void shouldFindHighValueOrders() {
        Order cheap = buildOrder(OrderStatus.PAID, BigDecimal.valueOf(50));
        Order expensive = buildOrder(OrderStatus.PAID, BigDecimal.valueOf(500));
        orderRepository.saveAll(List.of(cheap, expensive));

        List<Order> result = orderRepository.findHighValueOrders(
            OrderStatus.PAID, BigDecimal.valueOf(100));

        assertThat(result)
            .hasSize(1)
            .first()
            .extracting(Order::getTotalAmount)
            .isEqualTo(BigDecimal.valueOf(500));
    }

    private Order buildOrder(OrderStatus status, BigDecimal amount) {
        Order order = new Order();
        order.setStatus(status);
        order.setTotalAmount(amount);
        order.setCustomerEmail("test@test.com");
        order.setCreatedAt(LocalDateTime.now());
        return order;
    }
}

This test confirms that the totalAmount > minAmount condition works correctly — only the 500 order is returned, the 50 order is filtered out.

6. Comparing Query Methods and @Query

Criterion	Query Method	@Query
Writing speed	Fast for simple conditions	Requires manual JPQL/SQL writing
Complexity support	Limited for JOINs, subqueries	Flexible, supports all JPQL/SQL
Readability	Long names with many conditions	Clear, query logic separated
Error checking	Error on context startup	Error on context startup (JPQL), runtime (native SQL)
Best for	Basic CRUD, 1-3 conditions	Complex queries, JOINs, aggregates, projections

Selection principle: start with query methods for simple cases. When method names exceed 3-4 conditions or require JOINs/subqueries, switch to @Query for clarity and control.

7. Common Mistakes

Forgetting @Param when using named parameters:

// Error: missing @Param, Spring doesn't know how to map :status to a parameter
@Query("SELECT o FROM Order o WHERE o.status = :status")
List<Order> findByStatusRaw(OrderStatus status);

Fix: add @Param("status") to the parameter, or compile with the -parameters flag so Spring can extract parameter names from bytecode (Spring Boot enables this by default in Maven/Gradle plugin, but you should still add @Param explicitly to avoid build configuration dependencies).

Using findAll() then filtering with Java streams instead of writing a query:

// Don't do this: loads entire table into memory then filters
List<Order> paidOrders = orderRepository.findAll().stream()
    .filter(o -> o.getStatus() == OrderStatus.PAID)
    .toList();

This approach loads all data from the database into your application, wasting bandwidth and memory. Always push filter conditions down to the database via query methods or @Query.

Forgetting @Transactional when calling @Modifying outside a transactional scope:

@Modifying
@Query("DELETE FROM Order o WHERE o.status = :status")
void deleteByStatusBulk(@Param("status") OrderStatus status);

Calling this method directly without an enclosing transaction throws TransactionRequiredException. Always wrap it in a @Transactional method at the service layer, don’t call it directly from a controller.

8. Conclusion

Query methods are suitable for simple queries, keeping code short and readable when conditions don’t exceed 2-3 fields. When logic becomes more complex — requiring JOINs, subqueries, bulk updates, or custom projections — @Query is the clearer, more maintainable choice.

General principle: prioritize pushing data filtering logic down to the database instead of processing in Java, always write tests for each important query, and consider native SQL only when JPQL can’t handle it (for example, using database-specific functions). Master these three techniques and you’ll have enough tools to handle most data access problems in real-world Spring Boot applications.