[spring-data-jpa]nativeQuery查询带分页

最新推荐文章于 2025-10-31 10:02:22 发布

原创最新推荐文章于 2025-10-31 10:02:22 发布 · 6.3k 阅读

2 ·

CC 4.0 BY-SA版权

java 同时被 2 个专栏收录

46 篇文章

订阅专栏

spring

7 篇文章

订阅专栏

本文介绍如何在SpringDataJPA中使用@Query注解执行本地查询，并通过一个具体例子展示了如何解决动态排序问题。同时，文章还提供了一种实现本地查询分页的方法。

Native queries
The @Query annotation allows to execute native queries by setting the nativeQuery flag to true.

Example 50. Declare a native query at the query method using @Query

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
  }

这是官方给出的使用本地查询的例子，特别说明了目前本地查询并不支持动态排序，但是可以利用本地查询进行分页
Note, that we currently don’t support execution of dynamic sorting for native queries as we’d have to manipulate the actual query declared and we cannot do this reliably for native SQL. You can however use native queries for pagination by specifying the count query yourself:
这是官方给出的本地查询的分页形式

当我使用如下语句，会报错

@Query(value = "SELECT o FROM t_d_order o WHERE o.droom_id = ?1 AND DATE_FORMAT(o.create_time,'%Y-%m') = ?2 AND o.order_status IN ?3 ORDER BY o.create_time DESC",nativeQuery = true)
    Page<OrderModel> pageByDroomIdDate(Long droomId, String datetime, List<Integer> orderStatus, Pageable pageable);

报错信息大致为：

Caused by: org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method public abstract org.springframework.data.domain.Page com.crm.restapi.repository.OrderRepository.pageByDroomIdDate(java.lang.Long,java.lang.String,java.util.List,org.springframework.data.domain.Pageable)

解决办法如下：

@Query(value = "SELECT o FROM t_d_order o WHERE o.droom_id = ?1 AND DATE_FORMAT(o.create_time,'%Y-%m') = ?2 AND o.order_status IN ?3 ORDER BY ?#{#pageable}",nativeQuery = true)
    Page<OrderModel> pageByDroomIdDate(Long droomId, String datetime, List<Integer> orderStatus, Pageable pageable);

在语句order by之后使用占位符！

参考