Spring Boot JPA 存储库派生查询示例

迷南。 2023-09-25 02:41 59阅读 0赞

在之前的文章中，您已经知道如何使用[JPQL][]和[本机查询][Link 1]通过注释从数据库中检索数据。今天，我将向您展示如何使用派生查询方法在 Spring 引导中实现 Spring Data JPA 存储库查询：`@Query`

*  派生查询方法的结构
 *  配置 Spring 引导应用程序以使用不同的数据库
 *  JPA 按字段、列名、多列查找
 *  用于分页和排序的 JPA 查询方法

**内容**

*  [派生查询方法的结构][Link 2]
 *  [使用 Spring 引导的 JPA 存储库查询示例][Spring _ JPA]
 *  [创建和设置 Spring Boot 项目][Spring Boot]
 *  [配置 Spring 数据源、JPA、Hibernate][Spring _JPA_Hibernate]
 *  [创建实体][Link 3]
 *  [定义 JPA 存储库查询方法][JPA]
 *  [按字段查找 JPA][JPA 1]
 *  [通过多个列查找 JPA][JPA 2]
 *  [JPA 喜欢查询][JPA 3]
 *  [JPA 布尔查询][JPA 4]
 *  [带比较的 JPA 存储库查询][JPA 5]
 *  [带排序的 JPA 存储库查询][JPA 6]
 *  [带分页的 JPA 存储库查询][JPA 7]
 *  [具有分页和排序功能的 JPA 存储库查询][JPA 8]
 *  [JPA 删除多行][JPA 9]
 *  [运行 Spring JPA 派生查询项目][Spring JPA]
 *  [结论][Link 4]
 *  [延伸阅读][Link 5]
 *  [源代码][Link 6]

### **派生查询方法的结构** ###

通常，派生查询方法具有 2 个元素：主语（操作）和谓词（条件）。

*  **主语**：是引入子句（，，例如），它可能包含结果限制关键字（如 asor/）的进一步表达式（介于//和之间）。`find…By``exists…By``count…By``find``exists``count``By``Distinct``Top``First`
 *  **谓语**：放在主语之后。它可以是实体属性（与 / 连接），后跟一个或多个关键字 （,,,...）。`And``Or``StartingWith``EndingWith``Containing``IgnoreCase`

例如：

<code class="language-java">List<Tutorial> findByTitleContainingIgnoreCase(String title);
 
 List<Tutorial> findTop3ByTitleContainingAndPublished(String title, boolean isPublished);
 </code>

您可以在查询方法主题关键字和[查询方法谓词关键字][Link 7]中找到完整列表。

### **使用 Spring 引导的 JPA 存储库查询示例** ###

–科技：

*  爪哇 8
 *  Spring Boot 2.6.3 （带有 Spring Data JPA）
 *  MySQL/PostgreSQL/H2 （嵌入式数据库）
 *  Maven 3.8.1

– 项目结构：

![f6db87a38a6842dfa0d729cc49487797.png][]

让我简要解释一下。

*  `Tutorial`数据模型类对应于实体和表*教程*。
 *  `TutorialRepository`是一个为派生查询方法扩展[JpaRepository][]的接口。它将被自动连接。`SpringBootQueryExampleApplication`
 *  `SpringBootQueryExampleApplication`是哪个实现。我们将在这里运行查询方法。`SpringBootApplication``CommandLineRunner``TutorialRepository`
 *  在**application.properties**中配置Spring Datasource，JPA和Hibernate。
 *  **pom.xml**包含 Spring Boot 和 MySQL/PostgreSQL/H2 数据库的依赖项。

### **创建和设置 Spring Boot 项目** ###

使用 Spring Web 工具或您的开发[工具][Link 8]（[Spring Tool Suite][]、Eclipse、[Intellij][]）创建 Spring Boot 项目。

然后打开**pom.xml**并添加以下依赖项：

<dependency>
 	<groupId>org.springframework.boot</groupId>
 	<artifactId>spring-boot-starter-web</artifactId>
 </dependency>
 
 <dependency>
 	<groupId>org.springframework.boot</groupId>
 	<artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>

我们还需要再添加一个依赖项。  
如果你想使用**MySQL**：

<dependency>
 	<groupId>mysql</groupId>
 	<artifactId>mysql-connector-java</artifactId>
 	<scope>runtime</scope>
 </dependency>

– 或**PostgreSQL**：

<dependency>
 	<groupId>org.postgresql</groupId>
 	<artifactId>postgresql</artifactId>
 	<scope>runtime</scope>
 </dependency>

– 或**H2**（嵌入式数据库）：

<dependency>
 	<groupId>com.h2database</groupId>
 	<artifactId>h2</artifactId>
 	<scope>runtime</scope>
 </dependency>

### **配置 Spring 数据源、JPA、Hibernate** ###

在 src/main/resources 文件夹下，打开 application.properties 并编写这些行。

– 对于 MySQL：

spring.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=false
    spring.datasource.username= root
    spring.datasource.password= 123456
    
    spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect
    
    # Hibernate ddl auto (create, create-drop, validate, update)
    spring.jpa.hibernate.ddl-auto= update

对于PostgreSQL：

spring.datasource.url= jdbc:postgresql://localhost:5432/testdb
    spring.datasource.username= postgres
    spring.datasource.password= 123
    
    spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation= true
    spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect
    
    # Hibernate ddl auto (create, create-drop, validate, update)
    spring.jpa.hibernate.ddl-auto= update

*  `spring.datasource.username` & `spring.datasource.password`属性与数据库安装相同。
 *  Spring Boot 使用 Hibernate 进行 JPA 实现，我们配置 MySQL 或 PostgreSQL`MySQL5InnoDBDialect``PostgreSQLDialect`
 *  `spring.jpa.hibernate.ddl-auto`用于数据库初始化。我们将值设置为值，以便在数据库中自动创建一个与定义的数据模型对应的表。对模型的任何更改也将触发对表的更新。对于生产，此属性应该是。`update``validate`

– 对于 H2 数据库：

spring.datasource.url=jdbc:h2:mem:testdb
    spring.datasource.driverClassName=org.h2.Driver
    spring.datasource.username=sa
    spring.datasource.password=
     
    spring.jpa.show-sql=true
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
    spring.jpa.hibernate.ddl-auto= update
    
    spring.h2.console.enabled=true
    # default path: h2-console
    spring.h2.console.path=/h2-ui

*  `spring.datasource.url`：用于内存数据库和基于磁盘的数据库。`jdbc:h2:mem:[database-name]``jdbc:h2:file:[path/database-name]`
 *  我们为 H2 数据库配置配置`H2Dialect`
 *  `spring.h2.console.enabled=true`告诉 Spring 启动 H2 数据库管理工具，您可以在浏览器上访问此工具：`http://localhost:8080/h2-console`
 *  `spring.h2.console.path=/h2-ui`用于 H2 控制台的 URL，因此默认 url 将更改为。`http://localhost:8080/h2-console``http://localhost:8080/h2-ui`

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

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

教程具有以下字段：id、标题、级别、描述、已发布、创建于。

**模型**/*教程.java*

package com.bezkoder.spring.jpa.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;

### **定义 JPA 存储库查询方法** ###

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

存储库/*教程存储库.java*

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

在此接口中，我们将编写 JPA 派生查询以从数据库中获取数据。

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

![38b5c7f81a2f4911910072097969fa41.png][]

让我们检查一下基本的查询方法：首先`findAll()`。

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
 List<Tutorial> findAll();
 }

结果：

List<Tutorial> tutorials = new ArrayList<>();
 
 tutorials = tutorialRepository.findAll();
 show(tutorials);
 /*
 Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
 Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
 Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
 Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
 Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
 Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
 Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
 */

### **按字段查找 JPA** ###

我们只需要在后面附加字段，然后在派生查询上添加 bothor关键字以提高可读性（可选）。`by``Is``Equals`

Optional<Tutorial> findById(long id);
 
 List<Tutorial> findByLevel(int level);
 
 List<Tutorial> findByPublished(boolean isPublished);
 
 List<Tutorial> findByLevelIs(int level);
 
 List<Tutorial> findByLevelEquals(int level);

结果：

Tutorial tutorial = tutorialRepository.findById(1).get();
    System.out.println(tutorial);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByPublished(true);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByLevel(3);
    // or
    tutorials = tutorialRepository.findByLevelIs(3);
    // or
    tutorials = tutorialRepository.findByLevelEquals(3);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */

相反，您可以使用或关键字。`Not``IsNot`

List<Tutorial> findByLevelNot(int level);
 
 List<Tutorial> findByLevelIsNot(int level);

结果：

tutorials = tutorialRepository.findByLevelNot(3);
    // or
    tutorials = tutorialRepository.findByLevelIsNot(3);
    show(tutorials);
    /*
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */

### **通过多个列查找 JPA** ###

对于多个字段/多个列，我们可以使用字段/列之间的关键字。  
请注意，您可以根据需要连接任意数量/任意数量。`And``Or``And``Or`

List<Tutorial> findByLevelAndPublished(int level, boolean isPublished);
 
 List<Tutorial> findByTitleOrDescription(String title, String description);

结果：

tutorials = tutorialRepository.findByLevelAndPublished(3, true);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleOrDescription("Hibernate", "Spring Data Description");
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    */

### **JPA 喜欢查询** ###

我们可以使用以下关键字执行 SQL LIKE 查询：

*  `Like`：其中 x.字段像参数
 *  `NotLike`：其中 x.field 不像参数
 *  `StartingWith`：其中 x.field 像 %param （附加`%`)
 *  `EndingWith`：其中 x.field 像参数百分比（带有前缀`%`)
 *  `Containing`：其中 x.field 像 %param% （括在 %）

List<Tutorial> findByTitleLike(String title);
 
 List<Tutorial> findByTitleStartingWith(String title);
 
 List<Tutorial> findByTitleEndingWith(String title);
 
 List<Tutorial> findByTitleContaining(String title);
 
 List<Tutorial> findByTitleContainingOrDescriptionContaining(String title, String description);
 
 List<Tutorial> findByTitleContainingAndPublished(String title, boolean isPublished);

对于不区分大小写的查询，在 SQL 中，我们可以强制将值强制为全部大写或小写字母，然后与查询值进行比较。  
Spring JPA 提供了关键字来使用派生查询来执行此操作。`IgnoreCase`

List<Tutorial> findByTitleContainingIgnoreCase(String title);

结果：

tutorials = tutorialRepository.findByTitleStartingWith("Spring");
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleEndingWith("ot");
    show(tutorials);
    /*
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleContaining("at");
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleContainingIgnoreCase("dat");
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    */
    
    String text = "ot";
    tutorials = tutorialRepository.findByTitleContainingOrDescriptionContaining(text, text);
    show(tutorials);
    /*
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleContainingAndPublished("ring", true);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    */

### **JPA 布尔查询** ###

我们可以使用关键字来制作 JPA 布尔派生查询。`True``False`

List<Tutorial> findByPublishedTrue();
 
 List<Tutorial> findByPublishedFalse();

结果：

tutorials = tutorialRepository.findByPublishedTrue();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByPublishedFalse();
    show(tutorials);
    /*
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */

### **带比较的 JPA 存储库查询** ###

我们可以使用以下关键字执行 SQL 比较查询：

* `Between`：其中 x.字段介于 param\_1 和 param\_2 之间
 * `LessThan`：其中 x.field <参数
 * `LessThanEqual`：其中 x.field <= 参数
 * `GreaterThan`：其中 x.field >参数
 * `GreaterThanEqual`：其中 x.字段 >= 参数

对于JPA查询与比较，我们也可以使用关键字。`Date``After``Before`

* `After`：其中 x.dateField >参数
 * `Before`：其中 x.dateField <参数

List<Tutorial> findByLevelGreaterThan(int level);
 
 List<Tutorial> findByCreatedAtGreaterThanEqual(Date date);
 
 List<Tutorial> findByCreatedAtAfter(Date date);
 
 List<Tutorial> findByLevelBetween(int start, int end);
 
 List<Tutorial> findByLevelBetweenAndPublished(int start, int end, boolean isPublished);
 
 List<Tutorial> findByCreatedAtBetween(Date start, Date end);

结果：

tutorials = tutorialRepository.findByLevelGreaterThan(3);
    show(tutorials);
    /*
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    Date myDate = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-11");
    
    tutorials = tutorialRepository.findByCreatedAtGreaterThanEqual(myDate);
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByCreatedAtAfter(myDate);
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByLevelBetween(3, 5);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByLevelBetweenAndPublished(3, 5, true);
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    Date myDate1 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-04-11");
    Date myDate2 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-11");
    
    tutorials = tutorialRepository.findByCreatedAtBetween(myDate1, myDate2);
    show(tutorials);
    /*
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */

### **带排序的 JPA 存储库查询** ###

我们可以将关键字与（默认）一起使用，并使用派生查询按字段/列进行排序。`OrderBy``Asc``Desc`

List<Tutorial> findByOrderByLevel();
 // same as
 List<Tutorial> findByOrderByLevelAsc();
 
 List<Tutorial> findByOrderByLevelDesc();
 
 List<Tutorial> findByTitleContainingOrderByLevelDesc(String title);
 
 List<Tutorial> findByPublishedOrderByCreatedAtDesc(boolean isPublished);

结果：

tutorials = tutorialRepository.findByOrderByLevel();
    show(tutorials);
    /*
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByOrderByLevelDesc();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleContainingOrderByLevelDesc("at");
    show(tutorials);
    /*
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByPublishedOrderByCreatedAtDesc(true);
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    */

如果要对多个字段进行排序，则需要在类的帮助下将对象作为参数传递。`Sort``Order`

List<Tutorial> findByTitleContaining(String title, Sort sort);
 
 List<Tutorial> findByPublished(boolean isPublished, Sort sort);

结果：

tutorials = tutorialRepository.findByTitleContaining("at", Sort.by("level").descending());
 show(tutorials);
 /*
 Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
 Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
 Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
 */
 
 tutorials = tutorialRepository.findByPublished(false, Sort.by("level").descending());
 show(tutorials);
 /*
 Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
 Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
 Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
 */
 
 List<Order> orders = new ArrayList<Order>();
 orders.add(new Order(Sort.Direction.DESC, "level"));
 orders.add(new Order(Sort.Direction.ASC, "title"));
 
 tutorials = tutorialRepository.findByPublished(true, Sort.by(orders));
 show(tutorials);
 /*
 Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
 Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
 Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
 Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
 */

您可以在以下位置找到实现用于对多个字段/列进行排序的 Rest API 的方法：  
[Spring Data JPA 按多个列排序/排序|Spring Boot Rest API][Spring Data JPA _Spring Boot Rest API]

### **带分页的 JPA 存储库查询** ###

类似，您可以使用对象作为输入参数对派生查询进行分页。我们将此参数添加到方法定义中，作为返回类型。`Sort``Pageable``Page<User>`

Page<Tutorial> findAll(Pageable pageable);
 
 Page<Tutorial> findByTitle(String title, Pageable pageable);

结果：

int page = 0;
    int size = 3;
    
    Pageable pageable = PageRequest.of(page, size);
    
    tutorials = tutorialRepository.findAll(pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleContaining("ring", pageable).getContent();
    show(tutorials);
    /*
    Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    */

您可以在以下位置找到实现用于分页和过滤器的 Rest API 的方法：Spring Boot Rest API：  
[分页和过滤器示例|春季JPA，可分页][JPA 10]

### **具有分页和排序功能的 JPA 存储库查询** ###

`Pageable`也可用于分页和排序与方法的帮助。`PageRequest.of()`

Page<Tutorial> findAll(Pageable pageable);
 
 Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

结果：

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

您可以在以下位置找到实现用于分页和排序的 Rest API 的方法：[Spring Boot Rest API：][Spring Boot Rest API]  
分页和排序示例

### **JPA 删除多行** ###

Spring Data JPA 还支持用于删除多行的派生查询：

@Transactional
    void deleteAllByCreatedAtBefore(Date date);

结果：

Date outdate = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-1");
    
    tutorialRepository.deleteAllByCreatedAtBefore(outdate);
    tutorials = tutorialRepository.findAll();
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */

### **运行 Spring JPA 派生查询项目** ###

让我们打开，我们将在这里实现和自动连线接口来运行 JPA 查询方法。`SpringJpaRepositoryQueryExampleApplication.java``CommandLineRunner``TutorialRepository`

package com.bezkoder.spring.jpa.query;
 
 // import ...
 
 @SpringBootApplication
 public class SpringJpaRepositoryQueryExampleApplication implements CommandLineRunner {
 
 @Autowired
 TutorialRepository tutorialRepository;
 
 public static void main(String[] args) {
 SpringApplication.run(SpringJpaRepositoryQueryExampleApplication.class, args);
 }
 
 @Override
 public void run(String... args) throws Exception {
 // call tutorialRepository methods here
 }
 
 private void show(List<Tutorial> tutorials) {
 tutorials.forEach(System.out::println);
 }
 }

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

今天，我们已经知道如何使用派生查询在 Spring 引导示例中使用 Spring JPA 存储库查询。

您可以使用以下方法继续编写 CRUD Rest API：  
Spring [Boot、Spring Data JPA – Rest CRUD API 示例][Boot_Spring Data JPA _ Rest CRUD API]

如果要为 JPA 存储库编写单元测试：  
[带有 @DataJpaTest 的 JPA 存储库的 Spring 引导单元测试][_DataJpaTest _ JPA _ Spring]

您还可以通过[本教程][Link 9]了解：  
– 如何在 AWS 上部署此 Spring 启动应用程序（免费）。  
– dockerize with[Docker Compose： Spring Boot and][Docker Compose_ Spring Boot and] MySQL 示例  
– 通过[这篇文章][Link 10]  
上传 Excel 文件并将数据存储在 MySQL 数据库中的方法– 通过[这篇文章][Link 11]上传 CSV 文件并将数据存储在 MySQL 中。

祝你学习愉快！再见。

### **延伸阅读** ###

*  [具有 Spring 安全性和 JWT 身份验证的安全 Spring 启动应用程序][Spring _ JWT _ Spring]
 *  [春季数据 JPA 参考文档][JPA 11]
 *  [Spring 引导分页和排序示例][Spring Boot Rest API]

全栈 CRUD 应用程序：  
–Vue + 弹簧引导示例–角度 8 + 弹簧引导示例–角度 10 + 弹簧引导示例–角度 11 + 弹簧引导示例–角度 [12][] \+ 弹簧引导示例–角度 13 + 弹簧引导示例–[角度 14 + 弹簧引导示例][14 _]  
–反应 [\+ 弹簧启动][Link 12][示例][Link 13]

### **源代码** ###

您可以在[Github][] 上找到本教程的完整源代码。

改用本机查询：[Spring JPA 本机查询示例与][Link 1]  
Spring 引导

或者JPQL：  
[Spring JPA@Query JPQL的例子][JPQL]

或 EntityManager：  
[Spring Boot 中的 JPA EntityManager 示例][Spring Boot _ JPA EntityManager]

关联：  
–[JPA 在][JPA 12]春季引导中使用休眠的一对一示例–使用休眠和春季引导的 JPA 一对多示例–[JPA 多对多示例与春季引导][JPA 13]  
  
中的[休眠][Link 14]示例

您可以在以下教程中应用此实现：  
–Spring JPA + H2 示例–Spring JPA + MySQL 示例–Spring JPA + [PostgreSQL][] 示例–Spring [JPA +][JPA 14] Oracle 示例–[Spring][] [JPA + SQL][JPA _ SQL] [Server 示例][Server]

[JPQL]: https://www.bezkoder.com/spring-jpa-query/
[Link 1]: https://www.bezkoder.com/jpa-native-query/
[Link 2]: https://www.bezkoder.com/jpa-repository-query/#Structure_of_Derived_Query_methods
[Spring _ JPA]: https://www.bezkoder.com/jpa-repository-query/#JPA_Repository_Query_example_with_Spring_Boot
[Spring Boot]: https://www.bezkoder.com/jpa-repository-query/#Create_038_Setup_Spring_Boot_project
[Spring _JPA_Hibernate]: https://www.bezkoder.com/jpa-repository-query/#Configure_Spring_Datasource_JPA_Hibernate
[Link 3]: https://www.bezkoder.com/jpa-repository-query/#Create_Entity
[JPA]: https://www.bezkoder.com/jpa-repository-query/#Define_JPA_Repository_Query_methods
[JPA 1]: https://www.bezkoder.com/jpa-repository-query/#JPA_find_by_field
[JPA 2]: https://www.bezkoder.com/jpa-repository-query/#JPA_find_by_multiple_Columns
[JPA 3]: https://www.bezkoder.com/jpa-repository-query/#JPA_Like_Query
[JPA 4]: https://www.bezkoder.com/jpa-repository-query/#JPA_Boolean_Query
[JPA 5]: https://www.bezkoder.com/jpa-repository-query/#JPA_Repository_Query_with_Comparison
[JPA 6]: https://www.bezkoder.com/jpa-repository-query/#JPA_Repository_Query_with_Sorting
[JPA 7]: https://www.bezkoder.com/jpa-repository-query/#JPA_Repository_Query_with_Pagination
[JPA 8]: https://www.bezkoder.com/jpa-repository-query/#JPA_Repository_Query_with_Pagination_and_Sorting
[JPA 9]: https://www.bezkoder.com/jpa-repository-query/#JPA_Delete_multiple_Rows
[Spring JPA]: https://www.bezkoder.com/jpa-repository-query/#Run_Spring_JPA_Derived_Query_project
[Link 4]: https://www.bezkoder.com/jpa-repository-query/#Conclusion
[Link 5]: https://www.bezkoder.com/jpa-repository-query/#Further_Reading
[Link 6]: https://www.bezkoder.com/jpa-repository-query/#Source_Code
[Link 7]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#appendix.query.method.predicate
[f6db87a38a6842dfa0d729cc49487797.png]: https://img-blog.csdnimg.cn/f6db87a38a6842dfa0d729cc49487797.png
[JpaRepository]: https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html
[Link 8]: https://start.spring.io/
[Spring Tool Suite]: https://spring.io/tools
[Intellij]: https://www.jetbrains.com/idea/download/
[38b5c7f81a2f4911910072097969fa41.png]: https://img-blog.csdnimg.cn/38b5c7f81a2f4911910072097969fa41.png
[Spring Data JPA _Spring Boot Rest API]: https://www.bezkoder.com/spring-data-sort-multiple-columns/
[JPA 10]: https://www.bezkoder.com/spring-boot-pagination-filter-jpa-pageable/
[Spring Boot Rest API]: https://www.bezkoder.com/spring-boot-pagination-sorting-example/
[Boot_Spring Data JPA _ Rest CRUD API]: https://www.bezkoder.com/spring-boot-jpa-crud-rest-api/
[_DataJpaTest _ JPA _ Spring]: https://www.bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/
[Link 9]: https://www.bezkoder.com/deploy-spring-boot-aws-eb/
[Docker Compose_ Spring Boot and]: https://www.bezkoder.com/docker-compose-spring-boot-mysql/
[Link 10]: https://www.bezkoder.com/spring-boot-upload-excel-file-database/
[Link 11]: https://www.bezkoder.com/spring-boot-upload-csv-file/
[Spring _ JWT _ Spring]: https://www.bezkoder.com/spring-boot-jwt-authentication/
[JPA 11]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference
[12]: https://www.bezkoder.com/angular-11-spring-boot-crud/
[14 _]: https://www.bezkoder.com/spring-boot-angular-14-crud/
[Link 12]: https://www.bezkoder.com/react-spring-boot-crud/
[Link 13]: https://www.bezkoder.com/angular-10-spring-boot-crud/
[Github]: https://github.com/bezkoder/spring-jpa-repository-query-example
[Spring Boot _ JPA EntityManager]: https://www.bezkoder.com/jpa-entitymanager-spring-boot/
[JPA 12]: https://www.bezkoder.com/jpa-one-to-one/
[JPA 13]: https://www.bezkoder.com/jpa-many-to-many/
[Link 14]: https://www.bezkoder.com/jpa-one-to-many/
[PostgreSQL]: https://bezkoder.com/spring-boot-postgresql-example/
[JPA 14]: https://bezkoder.com/spring-boot-jpa-crud-rest-api/
[Spring]: https://www.bezkoder.com/spring-boot-jpa-h2-example/
[JPA _ SQL]: https://www.bezkoder.com/spring-boot-hibernate-oracle/
[Server]: https://www.bezkoder.com/spring-boot-sql-server/