Custom Queries

When it is better to write SQL explicitly

Sometimes a query is simpler and clearer when expressed directly in SQL instead of extending the repository method name. For this purpose, Spring Data JDBC supports the @Query annotation. Method parameters can be connected to named SQL parameters.

interface CustomerRepository extends CrudRepository<Customer, Long> {

    @Query("""
        SELECT id, full_name, email, active
        FROM customers
        WHERE active = :active
          AND full_name LIKE :pattern
        ORDER BY full_name
        """)
    List<Customer> search(
            @Param("active") boolean active,
            @Param("pattern") String pattern
    );
}

This method shows the actual SQL explicitly while remaining part of the repository interface. Spring Data JDBC maps the returned rows to the declared result type. The names and set of selected columns must match the expected mapping.

Modifying queries

If custom SQL performs an UPDATE or DELETE, the query must be marked as modifying.

@Modifying
@Query("UPDATE customers SET active = false WHERE id = :id")
int deactivate(@Param("id") long id);

Here the returned number indicates how many rows were changed. Such a method is useful when a targeted SQL operation is needed and there is no reason to load an object first, change it in memory, and then save the whole thing.