spring data jpa分页查询
来源:互联网 发布:nginx 页面状态码修改 编辑:程序博客网 时间:2024/05/21 17:28
最近项目上用就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;@Repositorypublic 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;@Repositorypublic 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);
1 0
- spring data jpa 分页查询
- spring data jpa分页查询
- spring data jpa 分页查询
- Spring jpa data多条件分页查询
- spring data jpa 条件分页查询
- Spring Data Jpa --分页、排序查询
- Spring Data JPA自定义查询,分页,排序
- Spring Data Jpa实现分页查询
- JPA 分页查询 (Spring Data Jpa 5)
- spring data jpa 分页
- Spring Data JPA 查询
- Spring Data Jpa 查询
- spring-data-jpa + SpringBoot + bootstrapTable 后端分页 模糊查询
- Spring Data Jpa本地查询(带分页方式)
- spring data jpa 1.9/1.10 复杂sql分页查询
- Spring Data JPA查询关键字
- spring data jpa 构建查询
- Spring Data JPA 动态查询
- laravel中pipeline中间件调用
- Java反射(二)----- 动态加载类
- 设计模式之抽象工厂(C++实现)
- linux 中断机制的处理过程
- Java(六) final用法实例讲解
- spring data jpa分页查询
- HDOJ 2089 不要62
- Lambda Functions in C++11
- 面试题:求任意时间分针和时针之间的度数
- 大数据生态之开源工具简介
- 用FileWriter 和FileReader实现文本文件的复制
- php 简单配置命令
- AVFoundation 编程指南 mark to memorize
- Eclipse快捷键