spring data jpa分页查询示例代码

时间:2021-10-02 04:09:25

最近项目上用就hibernate+spring data jpa,一开始感觉还不错,但是随着对业务的复杂,要求处理一些复杂的sql,就顺便研究了下,把结果记录下,以便日后查看。用到Specification,需要继承JpaSpecificationExecutor接口。(下面代码有的分页从0开始作为第一页,有的从1开始作为作为第一页,我也忘记,请自己测试)

DAO层:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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实现层

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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实现,不用复杂

?
1
2
Pageable pageable = new PageRequest(1, 1);
Page<CameraInfoPO> page = cameraInfoRepo.findByIsEnabled(1, pageable);

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://blog.csdn.net/rshw123456/article/details/51694129