条件分页 代替离线查询

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;

    @Action("courierAction_pageQuery")
    public String pageQuery() throws Exception {
        Pageable pageable = new PageRequest(page-1, rows);
        final String courierNum = model.getCourierNum();
        final String company = model.getCompany();
        final String type = model.getType();
        final Standard standard = model.getStandard();
        
        //相当于DetchedCriteria对象.通过specification封装过滤条件
        Specification<Courier> specification = new Specification<Courier>() {
            
            @Override
            public Predicate toPredicate(Root<Courier> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                //root:根实体   query:排序,封装条件  CriteriaBuilder:Predicate断言工厂,产生Predicate对象
                //添加过滤条件:添加快递员编号条件
                //p1:实体中属性  p2:条件
                List<Predicate> list = new ArrayList<>();
                if(StringUtils.isNotBlank(courierNum)){
                    Predicate p1 = cb.equal(root.get("courierNum").as(String.class), courierNum);
                    list.add(p1);
                }
                if(StringUtils.isNotBlank(company)){
                    Predicate p2 = cb.equal(root.get("company").as(String.class), company);
                    list.add(p2);
                }
                if(StringUtils.isNotBlank(type)){
                    Predicate p3 = cb.equal(root.get("type").as(String.class), type);
                    list.add(p3);
                }
                //sql : select * from T_COURIER t inner join t_Standard s on t.c_standard_id = s.c_id
//                where s.c_name = '标准一(100公斤)';
                //JPQL: from Courier c inner join c.standard s where s.name = "";
                if(standard!=null && StringUtils.isNotBlank(standard.getName())){
                    //返回关联对象
                    Join<Object, Object> join = root.join("standard");
                    Predicate p4 = cb.equal(join.get("name").as(String.class), standard.getName());
                    list.add(p4);
                }
                if(list.size()==0){
                    return null;
                }
                //list集合转为数组
                Predicate[] restrictions = new Predicate[list.size()];
                restrictions = list.toArray(restrictions);
                return cb.and(restrictions);
            }
        };
        Page<Courier> page = courierService.findAll(specification, pageable);
        
        Map<String, Object> map = new HashMap<>();
        map.put("total", page.getTotalElements());
        map.put("rows", page.getContent());
        
        //将fixedares集合属性排除掉,不转json
        JsonConfig jsonConfig = new JsonConfig();
        jsonConfig.setExcludes(new String[]{"fixedAreas"});
        
        String json = JSONObject.fromObject(map, jsonConfig).toString();
        
        ServletActionContext.getResponse().setContentType("text/json;charset=utf-8");
        ServletActionContext.getResponse().getWriter().write(json);
        return NONE;
    }




Dao:
public interface CourierDao extends JpaRepository<Courier, Integer>, JpaSpecificationExecutor<Courier> {

}

 

posted on 2017-08-14 23:42  0001  阅读(355)  评论(0编辑  收藏  举报