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

在本教程中，我将向您展示如何使用带有自定义查询的 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 findByPublished(boolean published, Pageable pageable);
Page findByTitleContaining(String title, Pageable pageable);

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

让我们注意上面存储库方法中的可分页参数。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 {
 
}

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

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

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

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

@Query("SELECT t FROM Tutorial t")
Page findAllWithPagination(Pageable pageable);
 
@Query("SELECT t FROM Tutorial t WHERE t.published=?1")
Page findByPublishedWithPagination(boolean isPublished, Pageable pageable);
  
@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
Page 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 findAllWithPagination(Pageable pageable);
 
@Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
Page findByPublished(boolean isPublished, Pageable pageable);
 
@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
Page 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 与自定义查询一起使用。