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

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

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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之后使用占位符!

参考

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值