Spring Data JPA : 分页查询

Spring Data JPA 有多少种查询方式?[1]

7 种,参考Spring Data JPA文档的 Query Methods 一节内容[2]:

  1. 按照jpa规范,通过方法名来创建查询 这种最多.
  2. @Query JPQL 或原生sql查询
  3. 另外一种原生的sql (EntityManager)
  4. 谓语Querydsl 方式(Predicate,相比 Specifications 更推荐)
  5. Specifications(QBC,重写 toPredicate)
  6. Query by Example (QBE,不推荐,每次要 new 传入)
  7. namequery方法(不采用,因为写在实体类上面,不够独立,官方也不推荐)

其他注意:

  1. 参数形式

    1
    2
    
    @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
    User findByLastnameOrFirstname(@Param("lastname") String lastname, @Param("firstname") String firstname);
    
  2. 排序

    分页参数 Pageable 中有排序,无需额外添加 Sort

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    
    public interface UserRepository extends JpaRepository<User, Long> {
      
     @Query("select u from User u where u.lastname like ?1%")
     List<User> findByAndSort(String lastname, Sort sort);
      
     @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
     List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
    }
      
    repo.findByAndSort("lannister", new Sort("firstname"));               
    repo.findByAndSort("stark", new Sort("LENGTH(firstname)")); // Invalid !!!      
    repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)")); 
    repo.findByAsArrayAndSort("bolton", new Sort("fn_len"));    
    
  3. SpEL 表达式

    1
    2
    
    @Query("select t from #{#entityName} t where t.attribute = ?1")
    List<T> findAllByAttribute(String attribute);
    
  4. 查询返回定制

    类投影、接口投影(可添加 default 方法、SqEL)

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    
    interface NamesOnly {
        
      @Value("#{target.firstname + ' ' + target.lastname}")
      String getFullName();
      
    }
       
    interface NamesOnly {
        
      String getFirstname();
      String getLastname();
        
      default String getFullName() {
        return getFirstname.concat(" ").concat(getLastname());
      }
    }
       
    class NamesOnly {
        
      private final String firstname, lastname;
        
      NamesOnly(String firstname, String lastname) {
        
        this.firstname = firstname;
        this.lastname = lastname;
      }
        
      String getFirstname() {
        return this.firstname;
      }
        
      String getLastname() {
        return this.lastname;
      }
        
      // equals(…) and hashCode() implementations
    }
    

@Query 原生SQL 分页查询

参考 Spring Data JPA 文档的 Example 64:

1
2
3
4
5
6
7
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);
}

[1] spring data jpa 几种查询方式

[2] Query Methods

updatedupdated2020-07-272020-07-27
加载评论