Spring Data JPA使用自定义查询进行分页

朴灿烈づ我的快乐病毒、 2023-09-25 12:39 16阅读 0赞

在本教程中，我将向您展示如何使用带有自定义查询的 Spring 数据分页来实现 Spring 引导分页。

### Spring Data Pageable ###

`Pageable`接口包含有关所请求页面的信息，例如大小、页面编号或`Sort对象`排序信息。

public interface Pageable {
      int getPageNumber();
      int getPageSize();
      long getOffset();
      Sort getSort();
      Pageable next();
      Pageable previousOrFirst();
      Pageable first();
      boolean hasPrevious();
      ...
    }

因此，当我们想在结果中进行分页和排序（带或不带过滤器）时，我们只需将方法的定义添加`Pageable`参数。

这就是我们使用实现`Pageable`接口的[PageRequest][]类创建`Pageable`对象的方式：

Pageable paging = PageRequest.of(page, size, sort);

*  `page`：从零开始的页面索引，不得为负数。
 *  `size`：页面中要返回的项目数必须大于 0。
 *  `sort`：`Sort`对象。

Spring Data 还支持许多有用的从方法名称创建查询（派生查询），我们将使用这些方法名称来过滤此示例中的结果，例如：

Page<Tutorial> findByPublished(boolean published, Pageable pageable);
    Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

有关更多详细信息，请访问：  
[Spring Boot 中的 Spring JPA 派生查询示例][Spring Boot _ Spring JPA]

让我们注意上面存储库方法中的[可分页][Link 1]参数。Spring 数据基础设施将自动识别此参数，以将分页和排序应用于数据库。

那么，如何将自定义查询与 Spring 数据分页一起使用呢？  
让我们来探索一下。

### **可通过自定义查询对 Spring 数据进行分页** ###

#### **创建实体** ####

在**模型**包中，我们定义`Tutorial`类。

教程有六个字段：id、标题、级别、描述、发布、创建于。

**model**/*Tutorial.java*

package com.bezkoder.spring.query.model;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Entity
    @Table(name = "tutorials")
    public class Tutorial {
    
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private long id;
    
      private String title;
    
      private String description;
      
      private int level;
    
      private boolean published;
      
      @Temporal(TemporalType.TIMESTAMP)
      private Date createdAt;
    
      public Tutorial() {
    
      }
    
      public Tutorial(String title, String description, int level, boolean published, Date createdAt) {
        this.title = title;
        this.description = description;
        this.level = level;
        this.published = published;
        this.createdAt = createdAt;
      }
    
      // getters and setters
    }

`@Entity`–注释表示该类是持久性 Java 类。

`@Table`–注释提供映射此实体的表。

`@Id`–注释用于主键。  
`@GeneratedValue`–注释用于定义主键的生成策略。

`@Temporal`–注释在时间戳和`java.util.Date`或时间戳之间来回转换到时间。例如，`@Temporal(TemporalType.DATE)`删除时间值并仅保留日期。

@Temporal(TemporalType.DATE)
    private Date createdAt;

#### **使用自定义查询方法定义存储库** ####

让我们创建一个存储库来与数据库进行交互。  
在**存储库**包中，创建扩展`JpaRepository`接口的`TutorialRepository`。

**repository**/*TutorialRepository.java*

package com.bezkoder.spring.query.repository;
    
    import com.bezkoder.spring.query.model.Tutorial;
    
    public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
    
    }

在此接口中，我们将编写 JPA 自定义查询（带有 where 条件）以从数据库中获取数据。  
我将向您展示如何使用`Pageable进行`JPQL查询和本机查询。

假设我们已经有了这样的**教程**表：

![e1efbe62c6264e708b51e1fe77681539.png][]

#### **使用 JPQL 进行可分页的自定义查询** ####

Spring Data JPA 查询示例使用`Pageable`类进行分页（具有排序和过滤）：

@Query("SELECT t FROM Tutorial t")
    Page<Tutorial> findAllWithPagination(Pageable pageable);
    
    @Query("SELECT t FROM Tutorial t WHERE t.published=?1")
    Page<Tutorial> findByPublishedWithPagination(boolean isPublished, Pageable pageable);
      
    @Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
    Page<Tutorial> findByTitleWithPagination(String title, Pageable pageable);

结果：

int page = 0;
    int size = 3;
    
    Pageable pageable = PageRequest.of(page, size);
    tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size, Sort.by("level").descending());
    tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size);   
    tutorials = tutorialRepository.findByTitleWithPagination("ring", pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size, Sort.by("level").descending());
    tutorials = tutorialRepository.findByPublishedWithPagination(false, pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */

#### **使用本机查询进行分页的自定义查询** ####

Spring Data JPA 本机查询示例使用`Pageable`类进行分页（带有排序和过滤）：

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
    Page<Tutorial> findAllWithPagination(Pageable pageable);
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
    Page<Tutorial> findByPublished(boolean isPublished, Pageable pageable);
    
    @Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
    Page<Tutorial> findByTitleLike(String title, Pageable pageable);

结果：

int page = 0;
    int size = 3;
    
    Pageable pageable = PageRequest.of(page, size);
    tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size, Sort.by("level").descending());
    tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size);
    tutorials = tutorialRepository.findByTitleLike("ring", pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */
    
    pageable = PageRequest.of(page, size, Sort.by("level").descending());
    tutorials = tutorialRepository.findByPublished(false, pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */

### **结论** ###

今天，我们已经知道如何在 Spring Boot 示例中使用 JPQL 和本机查询将 Spring Data JPA Pageable 与自定义查询一起使用。

[PageRequest]: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/PageRequest.html
[Spring Boot _ Spring JPA]: https://www.bezkoder.com/jpa-repository-query/
[Link 1]: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Pageable.html
[e1efbe62c6264e708b51e1fe77681539.png]: https://img-blog.csdnimg.cn/e1efbe62c6264e708b51e1fe77681539.png