最近項目上用就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; @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實現層
|
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; @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實現，不用復雜
|
1
2
|
Pageable pageable = new PageRequest(1, 1); Page<CameraInfoPO> page = cameraInfoRepo.findByIsEnabled(1, pageable); |
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://blog.csdn.net/rshw123456/article/details/51694129
