在之前的文章中，您已经知道如何使用JPQL和本机查询通过注释从数据库中检索数据。今天，我将向您展示如何使用派生查询方法在 Spring 引导中实现 Spring Data JPA 存储库查询：@Query
- 派生查询方法的结构
- 配置 Spring 引导应用程序以使用不同的数据库
- JPA 按字段、列名、多列查找
- 用于分页和排序的 JPA 查询方法
内容
派生查询方法的结构
通常，派生查询方法具有 2 个元素：主语（操作）和谓词（条件）。
- 主语：是引入子句（，，例如），它可能包含结果限制关键字（如 asor/）的进一步表达式（介于//和之间）。
find…Byexists…Bycount…ByfindexistscountByDistinctTopFirst - 谓语：放在主语之后。它可以是实体属性（与 / 连接），后跟一个或多个关键字 （,,,...）。
AndOrStartingWithEndingWithContainingIgnoreCase
例如：
<span style="color:#000000"><span style="background-color:#fafafa !important"><code class="language-java"><span style="color:#000000">List</span><span style="color:#999999"><</span><span style="color:#000000">Tutorial</span><span style="color:#999999">></span> <span style="color:#dd4a68">findByTitleContainingIgnoreCase</span><span style="color:#999999">(</span><span style="color:#000000">String</span> title<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#000000">List</span><span style="color:#999999"><</span><span style="color:#000000">Tutorial</span><span style="color:#999999">></span> <span style="color:#dd4a68">findTop3ByTitleContainingAndPublished</span><span style="color:#999999">(</span><span style="color:#000000">String</span> title<span style="color:#999999">,</span> <span style="color:#0077aa">boolean</span> isPublished<span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span></span>您可以在查询方法主题关键字和查询方法谓词关键字中找到完整列表。
使用 Spring 引导的 JPA 存储库查询示例
–科技：
- 爪哇 8
- Spring Boot 2.6.3 （带有 Spring Data JPA）
- MySQL/PostgreSQL/H2 （嵌入式数据库）
- Maven 3.8.1
– 项目结构：
让我简要解释一下。
Tutorial数据模型类对应于实体和表教程。TutorialRepository是一个为派生查询方法扩展JpaRepository的接口。它将被自动连接。SpringBootQueryExampleApplicationSpringBootQueryExampleApplication是哪个实现。我们将在这里运行查询方法。SpringBootApplicationCommandLineRunnerTutorialRepository- 在application.properties中配置Spring Datasource，JPA和Hibernate。
- pom.xml包含 Spring Boot 和 MySQL/PostgreSQL/H2 数据库的依赖项。
创建和设置 Spring Boot 项目
使用 Spring Web 工具或您的开发工具（Spring Tool Suite、Eclipse、Intellij）创建 Spring Boot 项目。
然后打开pom.xml并添加以下依赖项：
<!-- web for access H2 database UI -->
<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= updatespring.datasource.username&spring.datasource.password属性与数据库安装相同。- Spring Boot 使用 Hibernate 进行 JPA 实现，我们配置 MySQL 或 PostgreSQL
MySQL5InnoDBDialectPostgreSQLDialect spring.jpa.hibernate.ddl-auto用于数据库初始化。我们将值设置为值，以便在数据库中自动创建一个与定义的数据模型对应的表。对模型的任何更改也将触发对表的更新。对于生产，此属性应该是。updatevalidate
– 对于 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-uispring.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-consolespring.h2.console.path=/h2-ui用于 H2 控制台的 URL，因此默认 url 将更改为。http://localhost:8080/h2-consolehttp://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 存储库查询方法
让我们创建一个存储库来与数据库进行交互。
在存储库包中，创建扩展的接口。TutorialRepositoryJpaRepository
存储库/教程存储库.java
package com.bezkoder.spring.jpa.query.repository;
import com.bezkoder.spring.jpa.query.model.Tutorial;
public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
}在此接口中，我们将编写 JPA 派生查询以从数据库中获取数据。
假设我们已经有了这样的教程表：
让我们检查一下基本的查询方法：首先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关键字以提高可读性（可选）。byIsEquals
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]
*/相反，您可以使用或关键字。NotIsNot
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
对于多个字段/多个列，我们可以使用字段/列之间的关键字。
请注意，您可以根据需要连接任意数量/任意数量。AndOrAndOr
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 布尔派生查询。TrueFalse
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查询与比较，我们也可以使用关键字。DateAfterBefore
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 存储库查询
我们可以将关键字与（默认）一起使用，并使用派生查询按字段/列进行排序。OrderByAscDesc
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]
*/如果要对多个字段进行排序，则需要在类的帮助下将对象作为参数传递。SortOrder
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
带分页的 JPA 存储库查询
类似，您可以使用对象作为输入参数对派生查询进行分页。我们将此参数添加到方法定义中，作为返回类型。SortPageablePage<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 存储库查询
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：
分页和排序示例
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.javaCommandLineRunnerTutorialRepository
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 示例
如果要为 JPA 存储库编写单元测试：
带有 @DataJpaTest 的 JPA 存储库的 Spring 引导单元测试
您还可以通过本教程了解：
– 如何在 AWS 上部署此 Spring 启动应用程序（免费）。
– dockerize withDocker Compose： Spring Boot and MySQL 示例
– 通过这篇文章
上传 Excel 文件并将数据存储在 MySQL 数据库中的方法– 通过这篇文章上传 CSV 文件并将数据存储在 MySQL 中。
祝你学习愉快！再见。
延伸阅读
全栈 CRUD 应用程序：
–Vue + 弹簧引导示例–角度 8 + 弹簧引导示例–角度 10 + 弹簧引导示例–角度 11 + 弹簧引导示例–角度 12 + 弹簧引导示例–角度 13 + 弹簧引导示例–角度 14 + 弹簧引导示例
–反应 + 弹簧启动示例
源代码
您可以在Github 上找到本教程的完整源代码。
改用本机查询：Spring JPA 本机查询示例与
Spring 引导
或者JPQL：
Spring JPA@Query JPQL的例子
或 EntityManager：
Spring Boot 中的 JPA EntityManager 示例
关联：
–JPA 在春季引导中使用休眠的一对一示例–使用休眠和春季引导的 JPA 一对多示例–JPA 多对多示例与春季引导
中的休眠示例
您可以在以下教程中应用此实现：
–Spring JPA + H2 示例–Spring JPA + MySQL 示例–Spring JPA + PostgreSQL 示例–Spring JPA + Oracle 示例–Spring JPA + SQL Server 示例
491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
