Using @Query

有时使用方法名构造查询并不能满足某些相对复杂的需求，这时候可以使用 @Query 注解来自定义 JPQL 查询语句。

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);
}

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("select u from User u where u.firstname like %?1")
    List<User> findByFirstnameEndsWith(String firstname);
}

如果你不会使用 JPQL 查询，也可以使用 SQL 查询，也即使用 Native Query:

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
    User findByEmailAddress(String emailAddress);
}

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. You can, however, use native queries for pagination by specifying the count query yourself, as shown in the following example:

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
           countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
           nativeQuery = true)
    Page<User> findByLastname(String lastname, Pageable pageable);
}

Modifying

The @Modifying annotation is used to enhance the @Query annotation so that we can execute not only SELECT queries, but also INSERT, UPDATE, DELETE, and even DDL queries.

@Modifying
@Query("update User u set u.active = false where u.lastLoginDate < :date")
void deactivateUsersNotLoggedInSince(@Param("date") LocalDate date);

@Modifying
@Query("delete User u where u.active = false")
int deleteDeactivatedUsers();

@Modifying
@Query(value = "alter table USERS.USERS add column deleted int(1) not null default 0", nativeQuery = true)
void addDeletedColumn();

也就是说，默认来讲，Spring Data JPA 只让我们使用 @Query 进行查询，不让我们进行可以修改数据的操作，而 @Modifying 注解可以让我们修改数据。