现有如下场景,需要根据A表的check_code字段和B表的store_code、check_result字段组合查询,A表与B表的关联关系为一对多。
为了简化查询参数,我们对查询参数进行了封装,抽出了公共的QueryCondition:
public class QueryCondition<T> { protected int page = 1; protected int limit = 10; public QueryCondition() { }; public QueryCondition(int page, int limit) { this.page = page; this.limit = limit; } public Specification<T> getWhereClause() { return new Specification<T>() { @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> list = new ArrayList<Predicate>(); list.add(cb.equal(root.get("status"), Status.STATUS_ACTIVE)); return cb.and(list.toArray(new Predicate[list.size()])); } }; } public Pageable getPageRequest() { if (page < 1) { page = 1; } return new PageRequest(page - 1, limit); } public Criteria buildPageableCriteria(Criteria c) { int start = (this.getPage() - 1) * this.getLimit(); c.setFirstResult(start); c.setMaxResults(this.getLimit()); return c; } public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getLimit() { return limit; } public void setLimit(int limit) { this.limit = limit; }}
- 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
在公共QueryCondition的基础上,我们封装了个性化的AQueryCondition:
public class AQueryCondition extends QueryCondition<A> { private String checkCode; private String storeCode; public String toListKey() { String key = toCountKey() + "." + page + "." + limit; try { return DigestUtils.md5DigestAsHex(key.getBytes("UTF-8")); } catch (UnsupportedEncodingException e) { } return key; } public String toCountKey() { String key = checkCode.hashCode() + "." + storeCode.hashCode(); try { return DigestUtils.md5DigestAsHex(key.getBytes("UTF-8")); } catch (UnsupportedEncodingException e) { } return key; } public Specification<A> getWhereClause() { return new Specification<A>() { @Override public Predicate toPredicate(Root<A> root, CriteriaQuery<?> query, CriteriaBuilder cb) { Join<A, B> abMap = root.join("bs", JoinType.LEFT); List<Predicate> list = new ArrayList<Predicate>(); if (StringUtils.isNotBlank(checkCode)) { list.add(cb.equal(root.get("checkCode").as(String.class), checkCode)); } if (StringUtils.isNotBlank(storeCode)) { list.add(cb.equal(abMap.get("storeCode").as(String.class), storeCode)); list.add(cb.equal(abMap.get("checkResult").as(String.class), "OK")); } query.groupBy(root.get("pkg")).orderBy(cb.desc(root.get("createTime"))); return cb.and(list.toArray(new Predicate[list.size()])); } }; } public String getCheckCode() { return checkCode; } public void setCheckCode(String checkCode) { this.checkCode = checkCode; } public String getStoreCode() { return storeCode; } public void setStoreCode(String storeCode) { this.storeCode = storeCode; }}
- 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
由于在AQueryCondition我们用到了A和B的左连接关联查询,因此需要我们在A中定义和B的一对多关系。
@OneToMany@JoinColumn(name = "pkg", referencedColumnName = "pkg", insertable = false, updatable = false)private Collection<B> bs = new ArrayList<B>();
到此,基础工作做好了,接着我们看如何在Controller、Service、Dao层进行调用。
Controller层我们通过AQueryCondition接收查询参数:
@RequestMapping("/list.do") public @ResponseBody PageInfo<ADto> list(AQueryCondition cond) { Page<A> page = aService.pageFind(cond); ... long total = appSpiderService.count(cond); return new PageInfo<ADto>(result, total, cond.getPage(), cond.getLimit()); }
在Service层和Dao层我们加了缓存,这步可选,我们这里是Service层调用缓存层:
@Autowiredprivate CachedARepository aRepository;public Page<A> pageFind(AQueryCondition cond) { return aRepository.pageFind(cond);}
缓存层我们调用AQueryCondition的方法进行组合查询:
public Page<A> pageFind(final AQueryCondition cond) { return cache.get(key.getListKey(cond.toListKey()), new CacheGetCallback<Page<A>>() { @Override public Page<A> getObject() throws Exception { return aRepository.findAll(cond.getWhereClause(), cond.getPageRequest()); } @Override public String[] getCacheGroups(Page<A> o) { return new String[] { key.getGrpKey("list") }; } }); } public long count(AQueryCondition cond) { return aRepository.findAll(cond.getWhereClause()).size(); }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
Dao层我们进行公共方法的继承和个性化方法的编写:
@Transactionalpublic interface ARepository extends JpaRepository<A, Long>, JpaSpecificationExecutor<A> {}
到此,关于spring data jpa多表多条件查询的实现步骤已经讲解完毕,如何大家在实现过程中有疑问可以找我交流。