SpringData JPA 关于分页@Query 的问题
关于springdata jpa 分页问题相信很多小伙伴都遇到过,只要表中数量到达分页条件就会报错
废话少说直接上代码:
@Query(nativeQuery = true, value = "select a.*,c.username from fm_admin_voucher a LEFT JOIN t_userrole b on a.user_id = b.userid " +
"LEFT JOIN t_admin c ON a.user_id = c.userid " +
"where a.isdelete=false " +
"AND b.roleid in (10,11) " +
"AND if(?1!='',a.user_id=?1, 1=1) " +
"AND if(?2!='',a.createtime>=?2,1=1) " +
"AND if(?3!='',a.createtime<=?3,1=1) " +
"AND if(?4!='',c.mark=?4,1=1) " +
"AND if(?5!='',a.type in (301,302,303),1=1) " +
"AND if(?6!='',b.roleId=?6,1=1) " ,
countQuery = "select count(a.id) from fm_admin_voucher a LEFT JOIN t_userrole b on a.user_id = b.userid " +
"LEFT JOIN t_admin c ON a.user_id = c.userid " +
"where a.isdelete=false " +
"AND b.roleid in (10,11) " +
"AND if(?1!='',a.user_id=?1, 1=1) " +
"AND if(?2!='',a.createtime>=?2,1=1) " +
"AND if(?3!='',a.createtime<=?3,1=1) " +
"AND if(?4!='',c.mark=?4,1=1) " +
"AND if(?5!='',a.type in (301,302,303),1=1) " +
"AND if(?6!='',b.roleId=?6,1=1) ")
Page<Map> executiveList(Long adminId, Date startTime, Date endTime, String mark, String type, Long roleId, Pageable pageable);
其中
1.@Query 这个是jpa自定定义sql语句时用到的注解
2.nativeQuery代表value中的语句为 sql语句,而非hql语句
3. countQuery代表当前分页的总页数,如果不设置这个参数相信你的分页一定不顺利。
4.该语句中还有一些条件判断等,要学习的小伙伴自行研究