Spring Data Jpa本地查询(带分页方式)
来源:互联网 发布:opensuse和ubuntu 编辑:程序博客网 时间:2024/05/22 12:32
Spring Data Jpa本地查询(带分页方式)
在利用spring data jpa开发的时候为了解决一些复杂的查询需求这时候我们需要引入本地查询nativeQuery
参照官方的例子
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:
这是官方给出的本地查询的分页形式
注意:如果你用这种方式在进行分页查询时是会报错的(测试数据库为Oracle,其他数据库暂未做测试)
Example 51. Declare native count queries for pagination at the query method using @Query
public interface UserRepository extends JpaRepository<User, Long> { @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1", countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1", nativeQuery = true) Page<User> findByLastname(String lastname, Pageable pageable);}
我的代码如下
@Query(value = "select * from secondleveldesc sd left join secondlevel s on s.code=sd.code ", countQuery = "select count(*) from secondleveldesc", nativeQuery = true) Page<SecondleveldescEntity> findAll(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.material.cltx.dao.SecondlevelDescDao.findAll(org.springframework.data.domain.Pageable)
通过各种搜索最终找到一种解决办法
@Query(value = "select * from secondleveldesc sd left join secondlevel s on s.code=sd.code ORDER BY ?#{#pageable}",countQuery = "select count(*) from secondleveldesc",nativeQuery = true) Page<SecondleveldescEntity> findAll(Pageable pageable);
在查询语句后面加上
ORDER BY ?#{#pageable}”
这样就能顺利的利用本地查询进行分页了
- Spring Data Jpa本地查询(带分页方式)
- spring data jpa 分页查询
- spring data jpa分页查询
- spring data jpa 分页查询
- JPA 分页查询 (Spring Data Jpa 5)
- Spring jpa data多条件分页查询
- spring data jpa 条件分页查询
- Spring Data Jpa --分页、排序查询
- Spring Data JPA自定义查询,分页,排序
- Spring Data Jpa实现分页查询
- spring data JPA 中带查询条件的分页in的用法
- 【spring data jpa】带有条件的查询后分页和不带条件查询后分页实现
- 【spring data jpa】带有条件的查询后分页和不带条件查询后分页实现
- spring data jpa 分页
- Spring Data JPA 查询
- Spring Data Jpa 查询
- spring-data-jpa + SpringBoot + bootstrapTable 后端分页 模糊查询
- spring data jpa 1.9/1.10 复杂sql分页查询
- 大整数型运算
- Java笔记之Java语言基础(运算符的运用)
- bzoj 1415 聪聪和可可 (期望dp)
- 结构体E 老质价比
- Flask-分开Models解决循环引用
- Spring Data Jpa本地查询(带分页方式)
- java基础【2】
- 结构体F 选夫婿1
- Java 学习笔记 03 杂乱笔记
- 计算机系统概述-测验题
- Java笔记之计算机基础知识$java语言基础
- 【自适应Simpson积分】hdu 1724 Ellipse
- 链表A 顺序建立链表
- codevs 3287||NOIP 2013 货车运输 最大生成树+倍增 解题报告