spring data jpa分页查询示例代码
作者:码农的天空 时间:2023-01-09 14:51:50
最近项目上用就hibernate+spring data jpa,一开始感觉还不错,但是随着对业务的复杂,要求处理一些复杂的sql,就顺便研究了下,把结果记录下,以便日后查看。用到Specification,需要继承JpaSpecificationExecutor接口。(下面代码有的分页从0开始作为第一页,有的从1开始作为作为第一页,我也忘记,请自己测试)
DAO层:
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
@Repository
public interface CameraInfoRepo extends JpaRepository<CameraInfoPO, String>, JpaSpecificationExecutor<CameraInfoPO> {
@Query("select c from CameraInfoPO c where c.deviceInfo.id = ?1")
public List<CameraInfoPO> findCamerasByDeviceId(String listDeviceInfoId);
//更新之后不清空缓存,在一个事务里查询到旧数据(hibernate)
@Modifying
@Query(value = "update CameraInfoPO c set c.isEnabled = 1 where c.id = ?1")
public void updateEnabled(String cameraId);
//更新之后清空缓存,不保留旧对象(hibernate)
@Modifying(clearAutomatically = true)
@Query(value = "update CameraInfoPO c set c.isEnabled = 0 where c.id = ?1")
public void updateUnEnabled(String cameraId);
//带条件的分页查询
public Page<CameraInfoPO> findByIsEnabled(Integer isEnabled, Pageable pageable);
}
DAO实现层
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Repository;
@Repository
public class CameraInfoRepoImpl {
@PersistenceContext
private EntityManager em;
@Resource
private CameraInfoRepo cameraInfoRepo;
public Page<CameraInfoPO> findCameraInfoByPage(Pageable pageable, Integer isEnabled) {
//CriteriaBuilder,用来构建CritiaQuery的构建器对象
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
//CriteriaQuery,它包含着查询语句的条件各个部分,比如:select 、from、where、group by、order by等
CriteriaQuery<CameraInfoPO> criteriaQuery = criteriaBuilder.createQuery(CameraInfoPO.class);
//查询根,用于获取查询实例的属性,通过CriteriaQuery的from方法获取
Root<CameraInfoPO> rootFrom = criteriaQuery.from(CameraInfoPO.class);
//查询条件
List<Predicate> predicates = new ArrayList<Predicate>();
if (null != isEnabled) {
Predicate predicate = criteriaBuilder.equal(rootFrom.get("isEnabled").as(Integer.class), isEnabled);
predicates.add(predicate);
}
//格式化参数
criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));
//默认按照id排序(从小到大)
criteriaQuery.orderBy(criteriaBuilder.asc(rootFrom.get("id")));
//SQL查询对象
TypedQuery<CameraInfoPO> createQuery = em.createQuery(criteriaQuery);
//分页参数
Integer pageSize = pageable.getPageSize();
Integer pageNo = pageable.getPageNumber();
//计数查询结果条数
TypedQuery<CameraInfoPO> createCountQuery = em.createQuery(criteriaQuery);
// 实际查询返回分页对象
int startIndex = pageSize * pageNo;
createQuery.setFirstResult(startIndex);
createQuery.setMaxResults(pageable.getPageSize());
Page<CameraInfoPO> pageRst =
new PageImpl<CameraInfoPO>(createQuery.getResultList(), pageable, createCountQuery.getResultList().size());
return pageRst;
}
//制造查询条件结果(建议存放map)
private Specification<CameraInfoPO> getWhereClause(final Integer isEnabled) {
return new Specification<CameraInfoPO>() {
public Predicate toPredicate(Root<CameraInfoPO> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
Predicate predicate = cb.conjunction();
if (null != isEnabled) {
predicate = cb.equal(r.get("isEnabled").as(Integer.class), isEnabled);
}
return predicate;
}
};
}
//单表根据查询条件的分页
public Page<CameraInfoPO> findCameraInfoByPageForJpa(Pageable pageable, Integer isEnabled) {
Specification<CameraInfoPO> spec = getWhereClause(isEnabled);
Page<CameraInfoPO> pageRst = cameraInfoRepo.findAll(spec, pageable);
return pageRst;
}
}
还有另外一种就更简单了,如果只是根据表里面的一个或者几个字段,可以直接写jpa实现,不用复杂
Pageable pageable = new PageRequest(1, 1);
Page<CameraInfoPO> page = cameraInfoRepo.findByIsEnabled(1, pageable);
来源:http://blog.csdn.net/rshw123456/article/details/51694129
标签:spring,data,jpa
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Android封装实现短信验证码的获取倒计时
2023-06-28 22:57:23
![](https://img.aspxhome.com/file/2023/9/82399_0s.gif)
Java抽象类和接口的区别详情
2023-05-23 20:09:59
Android刮刮卡功能具体实现代码
2021-08-06 04:12:24
![](https://img.aspxhome.com/file/2023/0/88670_0s.jpg)
Java代理模式实例详解【静态代理与动态代理】
2023-04-27 08:18:51
![](https://img.aspxhome.com/file/2023/9/103829_0s.png)
Android的Launcher启动器中添加快捷方式及小部件实例
2022-10-26 13:05:14
![](https://img.aspxhome.com/file/2023/4/137144_0s.jpg)
SpringBoot 自定义starter yaml提示失效问题及解决方法
2022-08-03 14:58:42
![](https://img.aspxhome.com/file/2023/5/70945_0s.jpg)
android 下载时文件名是中文和空格会报错解决方案
2023-05-25 04:45:19
一篇超详细的SpringBoot整合MybatisPlus的文章
2023-02-26 11:04:35
![](https://img.aspxhome.com/file/2023/9/116119_0s.png)
IDEA中创建maven项目引入相关依赖无法下载jar问题及解决方案
2021-06-09 12:51:53
![](https://img.aspxhome.com/file/2023/5/88505_0s.png)
C#使用DevExpress中的XtraCharts控件实现图表
2022-12-21 10:14:10
![](https://img.aspxhome.com/file/2023/7/102167_0s.png)
Android中一种巧妙的drawable.xml替代方案分享
2021-12-06 04:18:35
![](https://img.aspxhome.com/file/2023/9/138829_0s.png)
Android实现记事本功能(26)
2023-02-17 22:30:23
![](https://img.aspxhome.com/file/2023/0/137780_0s.png)
解决@PathVariable对于特殊字符截断的问题
2021-10-10 08:19:40
聊聊@RequestBody和Json之间的关系
2023-11-27 03:31:45
了解Java虚拟机JVM的基本结构及JVM的内存溢出方式
2023-02-20 03:08:51
![](https://img.aspxhome.com/file/2023/8/63288_0s.png)
java控制台输出图书馆管理系统
2022-06-13 01:29:29
![](https://img.aspxhome.com/file/2023/4/61284_0s.jpg)
Java线程生命周期及转换过程
2023-12-27 21:22:15
![](https://img.aspxhome.com/file/2023/6/122956_0s.webp)
一文告诉你为什么要重写hashCode()方法和equals()方法
2021-09-05 07:05:55
C#基于Socket实现简单聊天室功能
2023-05-09 01:17:32
SpringBoot整合screw实现数据库文档自动生成的示例代码
2023-11-29 05:30:15
![](https://img.aspxhome.com/file/2023/9/60579_0s.png)