Spring Data JPA 实现带条件查询带group by的分页查询

DAO层:

@Repository
public interface DeviceMapper extends JpaRepository<DeviceManager, String>,JpaSpecificationExecutor<DeviceManager> {
	//带条件的分页查询        
    public Page<DeviceManager> findByIfUsed(Integer ifUsed, Pageable pageable); 
}

Service实现层

//单表根据查询条件的分页  
	@Override
	public Object queryTotalList(Pageable pageable) {

		Specification<DeviceManager> spec = getWhereClause();  

		Page<DeviceManager> pageRst = deviceMapper.findAll(spec, pageable);  
		
		Map<String,Object> resMap = new HashMap<>();
		//Page的遍历
		if(pageRst.iterator().hasNext()) {
			String category = pageRst.iterator().next().getCategory();
			if(resMap.containsKey(category)) {
				resMap.put(category, (Integer)resMap.get(category)+1);
			}else {
				resMap.put(category, 1);
			}
		}
		resMap.put("list", pageRst);
		return resMap;
	}

	//制造查询条件结果(建议存放map)  
	private Specification<DeviceManager> getWhereClause() {  
		return new Specification<DeviceManager>() {  
			public Predicate toPredicate(Root<DeviceManager> r, CriteriaQuery<?> q, CriteriaBuilder cb) {  
				Predicate predicate = cb.conjunction();  
					//添加查询条件
					predicate = cb.equal(r.get("ifUsed").as(Integer.class), 0);  
					//添加group by
					q.groupBy(r.get("category"));
				return predicate;  
			}  
		};  
	}

Controller层

@GetMapping("/device_list")
	public Object queryList(@RequestParam(value = "page", defaultValue = "1") Integer page,
            @RequestParam(value = "size", defaultValue = "10") Integer pageSize) {
		Pageable pageable = new PageRequest(page,pageSize);
		return deviceService.queryTotalList(pageable);
	}

学习代码~~