Spring Boot JPA 本机查询示例

男娘i 2023-09-25 13:28 50阅读 0赞

在本教程中，您将了解如何在 Spring 引导中使用 Spring Data JPA 本机查询示例（带参数）。我将向您展示：

*  将 Spring JPA 本机查询与@Query注释一起使用的方法
 *  如何在 Spring 引导中执行 SQL 查询
 *  具有 WHERE 条件的 JPA 选择查询示例

相关文章：  
–Spring JPA @Query Spring Boot 中的 [JPQL][] 示例 – Spring Boot  
中的 JPA [派生查询][Link 1]示例 – Spring Boot 中的 JPA [实体管理器][Link 2]示例–[Spring Boot， Spring Data JPA – Rest CRUD API][Spring Boot_ Spring Data JPA _ Rest CRUD API] 示例–Spring Boot  
分[页和排序][Link 3]示例– [带有多部分文件的][Link 4]  
Spring  
启动文件上传示例  
  
–[Spring Boot Authentication with Spring Security & JWT][Spring Boot Authentication with Spring Security _ JWT]  
–Spring JPA + H2 example–Spring JPA + MySQL example–Spring JPA + [PostgreSQL][] example–Spring JPA + Oracle example–Spring [JPA + SQL Server example][JPA _ SQL Server example]  
  
  
  
– [Spring JPA][] [\+ SQL Server 示例][SQL Server]

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

**内容\[[隐藏][Link 6]\]**

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

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

Spring JPA 同时支持 JPQL 和 Native Query。

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

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();

有关更多详细信息，请访问：  
Spring [JPA @Query Spring Boot 中使用 JPQL 的示例][JPQL]

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

*  在`value`属性中定义 SQL
 *  将`nativeQuery`属性值设置为`true`

@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);

但是我们可以改用`Pageable`对象进行排序。

让我们看看我们如何在 Spring Boot 示例中进行 JPA 本机查询。

### **带有 Spring 引导的 Spring JPA 本机查询示例** ###

–科技：

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

– 项目结构：

![f8647d0eef7f4694b6e0480995f3e789.png][]

让我简要解释一下。

*  `Tutorial`数据模型类对应于实体和表*教程*。
 *  `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 12]（[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`

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

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

教程具有以下字段：id, title, level, description, published, createdAt。

**model**/*Tutorial.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 本机查询方法定义存储库** ###

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

**repository**/*TutorialRepository.java*

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

在此接口中，我们将编写 JPA 本机查询（带参数）以从数据库中获取数据。

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

![6dbb47805e334c26969043eea95fbc4b.png][]

### **JPA 本机查询 选择位置条件示例** ###

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

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
    List<Tutorial> findAll();
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
    List<Tutorial> findByPublished(boolean isPublished);
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.title LIKE %?1%", nativeQuery = true)
    List<Tutorial> findByTitleLike(String title);
    
    @Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
    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 本机查询** ###

Spring 数据 JPA 本机查询大于或等于日期/列：

@Query(value = "SELECT * FROM tutorials t WHERE t.level >= ?1", nativeQuery = true)
    List<Tutorial> findByLevelGreaterThanEqual(int level);
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.created_at >= ?1", nativeQuery = true)
    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 本机查询之间** ###

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

@Query(value = "SELECT * FROM tutorials t WHERE t.level BETWEEN ?1 AND ?2", nativeQuery = true)
    List<Tutorial> findByLevelBetween(int start, int end);
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.created_at BETWEEN ?1 AND ?2", nativeQuery = true)
    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(value = "SELECT * FROM tutorials t WHERE t.published=:isPublished AND t.level BETWEEN :start AND :end", nativeQuery = true)
    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 本机查询顺序（按描述/ASC）** ###

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

@Query(value = "SELECT * FROM tutorials t ORDER BY t.level DESC", nativeQuery = true)
    List<Tutorial> findAllOrderByLevelDesc();
    
    @Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%')) ORDER BY t.level ASC", nativeQuery = true)
    List<Tutorial> findByTitleOrderByLevelAsc(String title);
    
    @Query(value = "SELECT * FROM tutorials t WHERE t.published=true ORDER BY t.created_at DESC", nativeQuery = true)
    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 本机查询排序依据** ###

*Spring Data JPA 目前不支持本机查询的动态排序，因为它必须操作声明的实际查询，这对于本机 SQL 无法可靠地执行此操作。但是，您可以通过自己指定计数查询来使用本机查询进行分页* \- [官方 Spring 文档][Spring]

/* InvalidJpaQueryMethodException
    @Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
    List<Tutorial> findByTitleAndSort(String title, Sort sort);
    */

那么，如何处理动态排序呢？  
我们可以改用`Pageable`对象。例如：

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

结果：

Pageable pageable1 = PageRequest.of(0, 1000, Sort.by("level").descending());
    
    tutorials = tutorialRepository.findByTitleLike("at", pageable1).getContent();
    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=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]
    */
    
    Pageable pageable2 = PageRequest.of(0, 1000, Sort.by("title").descending());
    
    tutorials = tutorialRepository.findByTitleLike("at", pageable2).getContent();
    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=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 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]
    */
    
    Pageable pageable3 = PageRequest.of(0, 1000, Sort.by("level").descending());
    
    tutorials = tutorialRepository.findByPublished(false, pageable3).getContent();
    show(tutorials);
    /*
    Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
    Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
    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(value = "SELECT * FROM tutorials", nativeQuery = true)
    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 Data JPA 本机查询，用于更新实体，同时使用：`@Query``@Transactional``@Modifying`

@Transactional
    @Modifying
    @Query(value = "UPDATE tutorials SET published=true WHERE id=?1", nativeQuery = true)
    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.jpa.query;
    
    // import ...
    
    @SpringBootApplication
    public class SpringJpaNativeQueryExampleApplication implements CommandLineRunner {
    
      @Autowired
      TutorialRepository tutorialRepository;
    
      public static void main(String[] args) {
        SpringApplication.run(SpringJpaNativeQueryExampleApplication.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 Boot示例中使用注释使用Spring JPA Native查询（带参数）。`@Query`

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

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

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

祝你学习愉快！再见。

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

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

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

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

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

改用 JPQL：  
[Spring JPA @Query Spring Boot 中 JPQL 的示例][JPQL]

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

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

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

[JPQL]: https://www.bezkoder.com/spring-jpa-query/
[Link 1]: https://www.bezkoder.com/jpa-repository-query/
[Link 2]: https://www.bezkoder.com/jpa-entitymanager-spring-boot/
[Spring Boot_ Spring Data JPA _ Rest CRUD API]: https://www.bezkoder.com/spring-boot-jpa-crud-rest-api/
[Link 3]: https://www.bezkoder.com/spring-boot-pagination-sorting-example/
[Link 4]: https://www.bezkoder.com/spring-boot-file-upload/
[Spring Boot Authentication with Spring Security _ JWT]: https://www.bezkoder.com/spring-boot-jwt-authentication/
[PostgreSQL]: https://www.bezkoder.com/spring-boot-postgresql-example/
[JPA _ SQL Server example]: https://www.bezkoder.com/spring-boot-hibernate-oracle/
[Spring JPA]: https://www.bezkoder.com/spring-boot-jpa-h2-example/
[SQL Server]: https://www.bezkoder.com/spring-boot-sql-server/
[JPA]: https://www.bezkoder.com/jpa-one-to-one/
[JPA 1]: https://www.bezkoder.com/jpa-many-to-many/
[Link 5]: https://www.bezkoder.com/jpa-one-to-many/
[Link 6]: https://www.bezkoder.com/jpa-native-query/#
[JPQL 1]: https://www.bezkoder.com/jpa-native-query/#JPQL_vs_Native_Query
[Spring _ Spring JPA]: https://www.bezkoder.com/jpa-native-query/#Spring_JPA_Native_Query_example_with_Spring_Boot
[Spring Boot]: https://www.bezkoder.com/jpa-native-query/#Create_038_Setup_Spring_Boot_project
[Spring _JPA_Hibernate]: https://www.bezkoder.com/jpa-native-query/#Configure_Spring_Datasource_JPA_Hibernate
[Link 7]: https://www.bezkoder.com/jpa-native-query/#Create_Entity
[JPA 2]: https://www.bezkoder.com/jpa-native-query/#Define_Repository_for_JPA_Native_Query_methods
[JPA _]: https://www.bezkoder.com/jpa-native-query/#JPA_native_query_Select_with_where_condition_example
[JPA 3]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Greater_Than_or_Equal_To
[JPA 4]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Between
[JPA 5]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_example_with_parameters
[JPA _ASC]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Order_By_DescAsc
[JPA 6]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Sort_By
[JPA 7]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Pagination
[JPA 8]: https://www.bezkoder.com/jpa-native-query/#JPA_Native_Query_Update
[Spring JPA 1]: https://www.bezkoder.com/jpa-native-query/#Run_Spring_JPA_Query_project
[Link 8]: https://www.bezkoder.com/jpa-native-query/#Conclusion
[Link 9]: https://www.bezkoder.com/jpa-native-query/#Further_Reading
[Link 10]: https://www.bezkoder.com/jpa-native-query/#Source_Code
[Link 11]: https://en.wikipedia.org/wiki/Jakarta_Persistence_Query_Language
[f8647d0eef7f4694b6e0480995f3e789.png]: https://img-blog.csdnimg.cn/f8647d0eef7f4694b6e0480995f3e789.png
[JpaRepository]: https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html
[Link 12]: https://start.spring.io/
[Spring Tool Suite]: https://spring.io/tools
[Intellij]: https://www.jetbrains.com/idea/download/
[6dbb47805e334c26969043eea95fbc4b.png]: https://img-blog.csdnimg.cn/6dbb47805e334c26969043eea95fbc4b.png
[Spring]: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.native
[Spring _ _ _DataJpaTest _ JPA]: https://www.bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/
[Link 13]: https://www.bezkoder.com/deploy-spring-boot-aws-eb/
[Docker Compose_ Spring Boot and]: https://www.bezkoder.com/docker-compose-spring-boot-mysql/
[Link 14]: https://www.bezkoder.com/spring-boot-upload-excel-file-database/
[Link 15]: https://www.bezkoder.com/spring-boot-upload-csv-file/
[JPA 9]: 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 16]: https://www.bezkoder.com/react-spring-boot-crud/
[Link 17]: https://www.bezkoder.com/angular-10-spring-boot-crud/
[Github]: https://github.com/bezkoder/spring-jpa-native-query-example
[PostgreSQL 1]: https://bezkoder.com/spring-boot-postgresql-example/
[JPA 10]: https://bezkoder.com/spring-boot-jpa-crud-rest-api/