如何在 Spring Boot 中使用 JPA 和 JPQL 进行自定义查询示例

绝地灬酷狼 2023-09-25 13:25 32阅读 0赞

在本教程中，您将了解如何在 Spring Boot 示例中使用 Spring JPA @Query进行自定义查询。我将向您展示：

*  使用JPQL（Java持久性查询语言）的方法
 *  如何在 Spring 引导中执行 SQL 查询
 *  具有 WHERE 条件的 JPA 选择查询示例

**内容**

*  [JPQL 与本机查询][JPQL]
 *  [Spring JPA @Query Spring Boot 的示例][Spring JPA _Query Spring Boot]
 *  [创建和设置 Spring Boot 项目][Spring Boot]
 *  [配置 Spring 数据源、JPA、Hibernate][Spring _JPA_Hibernate]
 *  [创建实体][Link 1]
 *  [使用 JPA 自定义查询方法定义存储库][JPA]
 *  [JPA 选择查询，其中条件示例][JPA 1]
 *  [JPA 查询大于或等于][JPA 2]
 *  [JPA 查询之间][JPA 3]
 *  [带参数的 JPA 查询示例][JPA 4]
 *  [JPA 查询顺序（按描述/ASC）][JPA _ASC]
 *  [JPA 查询排序依据][JPA 5]
 *  [JPA 查询分页][JPA 6]
 *  [JPA 查询更新][JPA 7]
 *  [运行 Spring JPA 查询项目][Spring JPA]
 *  [结论][Link 2]
 *  [延伸阅读][Link 3]
 *  [源代码][Link 4]

### **JPQL 与本机查询** ###

Spring JPA 同时支持 JPQL 和 Native Query。

Jakarta Persistence Query Language（JPQL;以前称为Java Persistence *Query Language）是一种独立于平台的面向对象的查询语言，定义为Jakarta Persistence（JPA;以前称为Java Persistence API）规范的一部分* \- [维基百科][Link 5]

JPQL 的灵感来自 SQL，其查询在语法上类似于 SQL 查询，但针对存储在关系数据库中的 JPA 实体对象进行操作，而不是直接使用数据库表。

以下是使用 JPQL 和`@Query`注释的自定义查询示例：

@Query("SELECT t FROM Tutorial t")
    List<Tutorial> findAll();
    
    @Query("SELECT t FROM Tutorial t WHERE t.published=true")
    List<Tutorial> findByPublished();

JPQL 仅支持 SQL 标准的子集。如果要进行复杂的查询，请查看本机 SQL 查询。  
这是如何在 Spring Boot 中执行 SQL 查询的方法，带有`@Query`注释：

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
    List<Tutorial> findAllNative();
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.published=true", nativeQuery = true)
    List<Tutorial> findByPublishedNative();

您需要注意：  
– Spring Data JPA 不会将查询调整为数据库的特定 SQL 方言，因此请确保提供的语句受 RDBMS 支持。  
– Spring Data JPA目前不支持本机查询的动态排序，因为它必须操作声明的实际查询，这对于本机SQL无法可靠地执行。

例如，我们不能在以下方法中使用动态排序：

// JPQL: ok
    @Query("SELECT * FROM tutorials t WHERE t.title LIKE %?1%")
    List<Tutorial> findByTitleAndSort(String title, Sort sort);
    
    // Native query: throw InvalidJpaQueryMethodException
    @Query(value = "SELECT * FROM tutorials t WHERE t.title LIKE %?1%", nativeQuery = true)
    List<Tutorial> findByTitleAndSortNative(String title, Sort sort);

有关更多详细信息，请访问：  
[使用 Spring 引导的 JPA 本机查询示例][Spring _ JPA]

让我们看看我们如何在 Spring Boot 中使用 JPA 和 JPQL 示例进行自定义查询。

### **Spring JPA @Query Spring Boot 的示例** ###

–科技：

*  Java 8
 *  Spring Boot 2.6.3 (with Spring Data JPA)
 *  MySQL/PostgreSQL/H2 (embedded database)
 *  Maven 3.8.1

– 项目结构：

![965c9fe26c3342b6af00257fa398dac7.png][]

让我简要解释一下。

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

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

使用 Spring Web 工具或您的开发[工具][Link 6]（[Spring Tool Suite][]、Eclipse、NetBeans、[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 `MySQL5InnoDBDialect`或 PostgreSQL `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`。

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

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

教程有六个字段：id, title, level, description, published, createdAt。

**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;

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

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

**repository**/*TutorialRepository.java*

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

在此接口中，我们将编写 JPA Select 查询（带有 where 条件）以从数据库中获取数据。  
我将向您展示如何在 Spring Boot 中使用 JPQL 查询示例。

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

![992e5ed72fca407799e49f418403a017.png][]

### **JPA 选择查询，WHERE****条件示例** ###

让我们使用`@Query`注释来创建带有 SELECT 和 WHERE 关键字的 Spring JPA 查询。

@Query("SELECT t FROM Tutorial t")
    List<Tutorial> findAll();
    
    @Query("SELECT t FROM Tutorial t WHERE t.published=?1")
    List<Tutorial> findByPublished(boolean isPublished);
    
    @Query("SELECT t FROM Tutorial t WHERE t.title LIKE %?1%")
    List<Tutorial> findByTitleLike(String title);
    
    @Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
    List<Tutorial> findByTitleLikeCaseInsensitive(String title);

结果：

List<Tutorial> tutorials = new ArrayList<>();
    
    tutorials = tutorialRepository.findAll();
    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]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 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]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByPublished(true);
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    */
    
    tutorials = tutorialRepository.findByTitleLike("ata");
    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=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */
       
    tutorials = tutorialRepository.findByTitleLikeCaseInsensitive("dat");
    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=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */

### **JPA 查询大于或等于** ###

春季数据 JPA 查询大于或等于日期/列：

@Query("SELECT t FROM Tutorial t WHERE t.level >= ?1")
    List<Tutorial> findByLevelGreaterThanEqual(int level);
    
    @Query("SELECT t FROM Tutorial t WHERE t.createdAt >= ?1")
    List<Tutorial> findByDateGreaterThanEqual(Date date);

结果：

tutorials = tutorialRepository.findByLevelGreaterThanEqual(3);
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Tut#7 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.findByDateGreaterThanEqual(myDate);
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, 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=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    */

### **JPA BETWEEN查询** ###

春季数据JPA查询 日期/列之间：

@Query("SELECT t FROM Tutorial t WHERE t.level BETWEEN ?1 AND ?2")
    List<Tutorial> findByLevelBetween(int start, int end);
    
    @Query("SELECT t FROM Tutorial t WHERE t.createdAt BETWEEN ?1 AND ?2")
    List<Tutorial> findByDateBetween(Date start, Date end);

结果：

tutorials = tutorialRepository.findByLevelBetween(3,5);
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, 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.findByDateBetween(myDate1, myDate2);
    show(tutorials);
    /*
    Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    */

### **带参数的 JPA 查询示例** ###

在上面的例子中，我们使用位置参数：**参数**由它们在查询中的位置引用（定义后跟一个数字（？1，？2，...）。弹簧数据JPA将自动替换同一位置的每个参数的值。`?`

绑定值的另一种方法是**命名参数**。命名参数以参数名称开头 （,, ...）。`:``:title``:date`

例如：

@Query("SELECT t FROM Tutorial t WHERE t.published=:isPublished AND t.level BETWEEN :start AND :end")
    List<Tutorial> findByLevelBetween(@Param("start") int start, @Param("end") int end, @Param("isPublished") boolean isPublished);

结果：

tutorials = tutorialRepository.findByLevelBetween(3, 5, true);
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    */

### **JPA 查询**Order By Desc/Asc ###

Spring 数据 JPA 查询顺序按列进行筛选的示例：

@Query("SELECT t FROM Tutorial t ORDER BY t.level DESC")
    List<Tutorial> findAllOrderByLevelDesc();
    
    @Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%')) ORDER BY t.level ASC")
    List<Tutorial> findByTitleOrderByLevelAsc(String title);
    
    @Query("SELECT t FROM Tutorial t WHERE t.published=true ORDER BY t.createdAt DESC")
    List<Tutorial> findAllPublishedOrderByCreatedDesc();

结果：

tutorials = tutorialRepository.findAllOrderByLevelDesc();
    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=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, 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]
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, 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]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleOrderByLevelAsc("at");
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findAllPublishedOrderByCreatedDesc();
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 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]
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    */

### **JPA 查询**Sort By ###

使用带有过滤的`Sort`类的 Spring 数据 JPA 查询示例：

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
    List<Tutorial> findByTitleAndSort(String title, Sort sort);
    
    @Query("SELECT t FROM Tutorial t WHERE t.published=?1")
    List<Tutorial> findByPublishedAndSort(boolean isPublished, Sort sort);

结果：

tutorials = tutorialRepository.findByTitleAndSort("at", Sort.by("level").descending());
    show(tutorials);
    /*
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, 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]
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByTitleAndSort("at", Sort.by("createdAt").descending());
    show(tutorials);
    /*
    Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, 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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
    Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
    */
    
    tutorials = tutorialRepository.findByPublishedAndSort(false, Sort.by("level").descending());
    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]
    Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
    */

### **JPA 查询分页** ###

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

@Query("SELECT t FROM Tutorial t")
    Page<Tutorial> findAllWithPagination(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]
    */

### **JPA 查询更新** ###

Spring 数据 JPA 查询，用于更新实体，同时使用：`@Query``@Transactional``@Modifying`

@Transactional
    @Modifying
    @Query("UPDATE Tutorial t SET t.published=true WHERE t.id=?1")
    int publishTutorial(Long id);

结果：

tutorialRepository.deleteAll();
    
    Date date1 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-03-11");
    Date date2 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-04-26");
    Date date3 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-19");
    
    tutorialRepository.save(new Tutorial("Spring Data", "Tut#1 Description", 3, false, date1));
    tutorialRepository.save(new Tutorial("Java Spring", "Tut#2 Description", 1, false, date1));
    tutorialRepository.save(new Tutorial("Hibernate", "Tut#3 Description", 3, false, date2));
    tutorialRepository.save(new Tutorial("Spring Boot", "Tut#4 Description", 2, false, date2));
    tutorialRepository.save(new Tutorial("Spring Data JPA", "Tut#5 Description", 3, false, date3));
    tutorialRepository.save(new Tutorial("Spring Batch", "Tut#6 Description", 4, false, date3));
    tutorialRepository.save(new Tutorial("Spring Security", "Tut#7 Description", 5, false, date3));
    
    List<Tutorial> tutorials = new ArrayList<>();
    
    tutorials = tutorialRepository.findAll();
    show(tutorials); // published = false for all
    
    tutorialRepository.publishTutorial(tutorials.get(0).getId());
    tutorialRepository.publishTutorial(tutorials.get(2).getId());
    tutorialRepository.publishTutorial(tutorials.get(4).getId());
    
    tutorials = tutorialRepository.findByPublished(true);
    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=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 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]
    */

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

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

package com.bezkoder.spring.query;
    
    // import ...
    
    @SpringBootApplication
    public class SpringBootQueryExampleApplication implements CommandLineRunner {
    
      @Autowired
      TutorialRepository tutorialRepository;
    
      public static void main(String[] args) {
        SpringApplication.run(SpringBootQueryExampleApplication.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);
      }
    }

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

今天我们已经知道如何使用 JPQL 在 Spring Boot 示例中使用 Spring JPA `@Query注解`进行自定义查询。

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

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

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

祝你学习愉快！再见。

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

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

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

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

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

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

或派生查询：  
Spring [Boot 中的 Spring JPA 派生查询示例][Boot _ Spring JPA]

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

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

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

[JPQL]: https://www.bezkoder.com/spring-jpa-query/#JPQL_vs_Native_Query
[Spring JPA _Query Spring Boot]: https://www.bezkoder.com/spring-jpa-query/#Spring_JPA_Query_example_with_Spring_Boot
[Spring Boot]: https://www.bezkoder.com/spring-jpa-query/#Create_038_Setup_Spring_Boot_project
[Spring _JPA_Hibernate]: https://www.bezkoder.com/spring-jpa-query/#Configure_Spring_Datasource_JPA_Hibernate
[Link 1]: https://www.bezkoder.com/spring-jpa-query/#Create_Entity
[JPA]: https://www.bezkoder.com/spring-jpa-query/#Define_Repository_with_JPA_Custom_Query_methods
[JPA 1]: https://www.bezkoder.com/spring-jpa-query/#JPA_Select_query_with_where_condition_example
[JPA 2]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Greater_Than_or_Equal_To
[JPA 3]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Between
[JPA 4]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_example_with_parameters
[JPA _ASC]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Order_By_DescAsc
[JPA 5]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Sort_By
[JPA 6]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Pagination
[JPA 7]: https://www.bezkoder.com/spring-jpa-query/#JPA_Query_Update
[Spring JPA]: https://www.bezkoder.com/spring-jpa-query/#Run_Spring_JPA_Query_project
[Link 2]: https://www.bezkoder.com/spring-jpa-query/#Conclusion
[Link 3]: https://www.bezkoder.com/spring-jpa-query/#Further_Reading
[Link 4]: https://www.bezkoder.com/spring-jpa-query/#Source_Code
[Link 5]: https://en.wikipedia.org/wiki/Jakarta_Persistence_Query_Language
[Spring _ JPA]: https://www.bezkoder.com/jpa-native-query/
[965c9fe26c3342b6af00257fa398dac7.png]: https://img-blog.csdnimg.cn/965c9fe26c3342b6af00257fa398dac7.png
[JpaRepository]: https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html
[Link 6]: https://start.spring.io/
[Spring Tool Suite]: https://spring.io/tools
[Intellij]: https://www.jetbrains.com/idea/download/
[992e5ed72fca407799e49f418403a017.png]: https://img-blog.csdnimg.cn/992e5ed72fca407799e49f418403a017.png
[Boot_Spring Data JPA _ Rest CRUD API]: https://www.bezkoder.com/spring-boot-jpa-crud-rest-api/
[Spring _ _ _DataJpaTest _ JPA]: https://www.bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/
[Link 7]: https://www.bezkoder.com/deploy-spring-boot-aws-eb/
[Docker Compose_ Spring Boot and]: https://www.bezkoder.com/docker-compose-spring-boot-mysql/
[Link 8]: https://www.bezkoder.com/spring-boot-upload-excel-file-database/
[Link 9]: https://www.bezkoder.com/spring-boot-upload-csv-file/
[Spring _ JWT _ Spring]: https://www.bezkoder.com/spring-boot-jwt-authentication/
[JPA 8]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference
[Spring]: https://www.bezkoder.com/spring-boot-pagination-sorting-example/
[12]: https://www.bezkoder.com/angular-11-spring-boot-crud/
[14 _]: https://www.bezkoder.com/spring-boot-angular-14-crud/
[Link 10]: https://www.bezkoder.com/react-spring-boot-crud/
[Link 11]: https://www.bezkoder.com/angular-10-spring-boot-crud/
[Github]: https://github.com/bezkoder/spring-jpa-query-example
[Boot _ Spring JPA]: https://www.bezkoder.com/jpa-repository-query/
[Spring Boot _ JPA EntityManager]: https://www.bezkoder.com/jpa-entitymanager-spring-boot/
[JPA 9]: https://www.bezkoder.com/jpa-one-to-one/
[JPA 10]: https://www.bezkoder.com/jpa-many-to-many/
[Link 12]: https://www.bezkoder.com/jpa-one-to-many/
[PostgreSQL]: https://bezkoder.com/spring-boot-postgresql-example/
[JPA 11]: https://bezkoder.com/spring-boot-jpa-crud-rest-api/
[Spring 1]: 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/