现有如下场景,需要根据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;
}
}
在公共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;
}
}
由于在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层调用缓存层:
@Autowired
private 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();
}
Dao层我们进行公共方法的继承和个性化方法的编写:
@Transactional
public interface ARepository extends JpaRepository<A, Long>, JpaSpecificationExecutor<A> {
}
到此,关于spring data jpa多表多条件查询的实现步骤已经讲解完毕,如何大家在实现过程中有疑问可以找我交流。