Spring Data JPA查询方法

约 1438 字大约 5 分钟

spring-datajpa

2025-04-03

概述

Spring Data JPA 是 Spring Data 家族中最核心的模块，它在 JPA（Java Persistence API）之上提供了更高层次的抽象。通过 Repository 接口和方法名约定查询、@Query 注解、Specification 动态查询等多种方式，极大地简化了数据访问层的开发。

基本使用

实体定义

@Entity
@Table(name = "users")
public class User {

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

    @Column(nullable = false, length = 50)
    private String username;

    @Column(nullable = false, unique = true)
    private String email;

    @Enumerated(EnumType.STRING)
    private UserStatus status;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Order> orders;

    // constructors, getters, setters...
}

Repository 接口

public interface UserRepository extends JpaRepository<User, Long>,
        JpaSpecificationExecutor<User> {
    // Spring Data JPA 会根据方法名自动生成查询实现
}

派生查询方法（Derived Query Methods）

Spring Data JPA 可以根据方法名自动推导 JPQL 查询语句：

public interface UserRepository extends JpaRepository<User, Long> {

    // SELECT u FROM User u WHERE u.username = ?1
    User findByUsername(String username);

    // SELECT u FROM User u WHERE u.email = ?1
    Optional<User> findByEmail(String email);

    // SELECT u FROM User u WHERE u.status = ?1
    List<User> findByStatus(UserStatus status);

    // SELECT u FROM User u WHERE u.username LIKE %?1%
    List<User> findByUsernameContaining(String keyword);

    // SELECT u FROM User u WHERE u.email LIKE ?1%
    List<User> findByEmailStartingWith(String prefix);

    // SELECT u FROM User u WHERE u.createdAt BETWEEN ?1 AND ?2
    List<User> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);

    // SELECT u FROM User u WHERE u.status = ?1 AND u.createdAt > ?2
    List<User> findByStatusAndCreatedAtAfter(UserStatus status, LocalDateTime date);

    // SELECT u FROM User u WHERE u.status IN ?1
    List<User> findByStatusIn(Collection<UserStatus> statuses);

    // SELECT u FROM User u WHERE u.email IS NOT NULL
    List<User> findByEmailIsNotNull();

    // SELECT u FROM User u WHERE u.status = ?1 ORDER BY u.createdAt DESC
    List<User> findByStatusOrderByCreatedAtDesc(UserStatus status);

    // 分页查询
    Page<User> findByStatus(UserStatus status, Pageable pageable);

    // 限制结果数量
    List<User> findTop10ByStatusOrderByCreatedAtDesc(UserStatus status);
    User findFirstByUsernameOrderByCreatedAtDesc(String username);

    // 统计
    long countByStatus(UserStatus status);

    // 判断是否存在
    boolean existsByEmail(String email);

    // 删除
    void deleteByStatus(UserStatus status);
}

方法名关键词对照表

关键词	JPQL 等价	示例
`And`	AND	`findByNameAndAge`
`Or`	OR	`findByNameOrAge`
`Between`	BETWEEN	`findByAgeBetween`
`LessThan`	<	`findByAgeLessThan`
`GreaterThanEqual`	>=	`findByAgeGreaterThanEqual`
`Like`	LIKE	`findByNameLike`
`Containing`	LIKE %..%	`findByNameContaining`
`In`	IN	`findByStatusIn`
`IsNull`	IS NULL	`findByEmailIsNull`
`OrderBy`	ORDER BY	`findByStatusOrderByNameAsc`
`Not`	!=	`findByStatusNot`
`True` / `False`	= true / false	`findByActiveTrue`

@Query 注解

当方法名过长或表达力不足时，使用 @Query 编写自定义查询：

public interface UserRepository extends JpaRepository<User, Long> {

    // JPQL 查询
    @Query("SELECT u FROM User u WHERE u.status = :status AND u.createdAt > :date")
    List<User> findActiveUsersAfter(@Param("status") UserStatus status,
                                     @Param("date") LocalDateTime date);

    // 原生SQL查询
    @Query(value = "SELECT * FROM users u WHERE u.email LIKE %:domain",
           nativeQuery = true)
    List<User> findByEmailDomain(@Param("domain") String domain);

    // 更新操作
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.status = :status WHERE u.id = :id")
    int updateStatus(@Param("id") Long id, @Param("status") UserStatus status);

    // 批量更新
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.status = :status WHERE u.id IN :ids")
    int batchUpdateStatus(@Param("ids") List<Long> ids,
                          @Param("status") UserStatus status);

    // 聚合查询
    @Query("SELECT u.status, COUNT(u) FROM User u GROUP BY u.status")
    List<Object[]> countByStatusGroup();

    // DTO 投影
    @Query("SELECT new com.example.dto.UserSummary(u.id, u.username, u.email) " +
           "FROM User u WHERE u.status = :status")
    List<UserSummary> findUserSummaries(@Param("status") UserStatus status);
}

Specification 动态查询

JpaSpecificationExecutor 支持基于 Criteria API 的动态查询，适合复杂的搜索条件组合：

public class UserSpecifications {

    public static Specification<User> hasStatus(UserStatus status) {
        return (root, query, cb) ->
                status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<User> usernameLike(String keyword) {
        return (root, query, cb) ->
                keyword == null ? null :
                cb.like(root.get("username"), "%" + keyword + "%");
    }

    public static Specification<User> createdAfter(LocalDateTime date) {
        return (root, query, cb) ->
                date == null ? null :
                cb.greaterThan(root.get("createdAt"), date);
    }

    public static Specification<User> emailDomain(String domain) {
        return (root, query, cb) ->
                domain == null ? null :
                cb.like(root.get("email"), "%" + domain);
    }
}

在 Service 中使用：

@Service
public class UserSearchService {

    @Autowired
    private UserRepository userRepository;

    public Page<User> search(UserSearchRequest request, Pageable pageable) {
        Specification<User> spec = Specification
                .where(UserSpecifications.hasStatus(request.getStatus()))
                .and(UserSpecifications.usernameLike(request.getKeyword()))
                .and(UserSpecifications.createdAfter(request.getStartDate()))
                .and(UserSpecifications.emailDomain(request.getEmailDomain()));

        return userRepository.findAll(spec, pageable);
    }
}

分页与排序

@GetMapping("/users")
public Page<User> listUsers(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "createdAt") String sortBy,
        @RequestParam(defaultValue = "desc") String direction) {

    Sort sort = Sort.by(Sort.Direction.fromString(direction), sortBy);
    Pageable pageable = PageRequest.of(page, size, sort);

    return userRepository.findByStatus(UserStatus.ACTIVE, pageable);
}

// 多字段排序
Sort sort = Sort.by(
    Sort.Order.desc("createdAt"),
    Sort.Order.asc("username")
);

审计（Auditing）

@Entity
@EntityListeners(AuditingEntityListener.class)
public class User {

    @CreatedDate
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @CreatedBy
    @Column(name = "created_by", updatable = false)
    private String createdBy;

    @LastModifiedBy
    @Column(name = "updated_by")
    private String updatedBy;
}

// 配置审计者
@Configuration
@EnableJpaAuditing
public class JpaAuditingConfig {

    @Bean
    public AuditorAware<String> auditorAware() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext())
                .map(SecurityContext::getAuthentication)
                .map(Authentication::getName);
    }
}

投影（Projections）

// 接口投影 - 只查询需要的字段
public interface UserSummaryProjection {
    Long getId();
    String getUsername();
    String getEmail();

    @Value("#{target.username + ' <' + target.email + '>'}")
    String getDisplayName();
}

public interface UserRepository extends JpaRepository<User, Long> {
    List<UserSummaryProjection> findProjectedByStatus(UserStatus status);
}

// 类投影（DTO）
public record UserSummary(Long id, String username, String email) {}

最佳实践

1. N+1 查询问题

// 问题：遍历用户时，每个用户都会触发一次查询订单
List<User> users = userRepository.findAll();
users.forEach(u -> System.out.println(u.getOrders().size()));

// 解决方案1：@EntityGraph
@EntityGraph(attributePaths = {"orders"})
@Query("SELECT u FROM User u WHERE u.status = :status")
List<User> findUsersWithOrders(@Param("status") UserStatus status);

// 解决方案2：JPQL JOIN FETCH
@Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders WHERE u.status = :status")
List<User> findUsersWithOrdersFetch(@Param("status") UserStatus status);

2. 批量操作优化

// 开启批量插入
// application.yml:
// spring.jpa.properties.hibernate.jdbc.batch_size=50
// spring.jpa.properties.hibernate.order_inserts=true

@Transactional
public void batchInsert(List<User> users) {
    for (int i = 0; i < users.size(); i++) {
        entityManager.persist(users.get(i));
        if (i % 50 == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
}

Spring Data JPA 通过 Repository 接口抽象大幅简化了数据访问层的开发。派生查询方法适用于简单条件查询，@Query 注解适用于复杂的固定查询，Specification 适用于动态搜索条件组合。配合分页、排序、审计和投影等特性，可以覆盖绝大多数数据访问需求。需要注意 N+1 查询等性能陷阱，合理使用 @EntityGraph 和 JOIN FETCH 进行优化。

贡献者

withesse

更新日志

2026/3/14 13:09

查看所有更新日志

9f6c2-feat: organize wiki content and refresh site setup于 2026/3/14