springdataJPA 使用Predicate Join 做四表复杂条件查询(分页)

近期在做项目时,遇到一种表关系,很复杂的分页,在这中间尝试过很多方法都是不行,最终通过尝试找到如下解决方案,希望可以帮到有需要的人!

首先说一下表关系:

设备数据表:

设备表:

用户表:

要实现效果为:通过小区名称 或者楼栋名称区分 设备数据.解决方案如下:

  Specification<RoomTempHum> specification = new Specification<RoomTempHum>() {
            @Override
            public Predicate toPredicate(Root<RoomTempHum> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) throws Exception {
                List<Predicate> list = new ArrayList<>();
                //链接设备
                Join<RoomTempHum, RoomEquipment> roomEquipmentJoin = root.join("roomEquipment", JoinType.LEFT);
                //设备ID
                if (!StringUtils.isEmpty(roomTempHum.getEquipmentId())) {
                    Predicate equipmentId = cb.equal(root.get("equipmentId"), roomTempHum.getEquipmentId());
                    list.add(equipmentId);
                }
                //设备不为null
                if (roomTempHum.getRoomEquipment() != null) {
                    //住户不为null
                    if (roomTempHum.getRoomEquipment().getHouseHold() != null) {
                        if (!StringUtils.isEmpty(roomTempHum.getRoomEquipment().getHouseHoldId())) {
                            Predicate predicate = cb.equal(roomEquipmentJoin.get("houseHold").get("id"), roomTempHum.getRoomEquipment().getHouseHold().getId());
                            list.add(predicate);
                        }
                        //小区不为null
                        if (!StringUtils.isEmpty(roomTempHum.getRoomEquipment().getHouseHold().getCommunity().getId() )) {
                            Predicate id = cb.equal(roomEquipmentJoin.get("houseHold").get("community").get("id"), roomTempHum.getRoomEquipment().getHouseHold().getCommunity().getId()
                            );
                            list.add(id);
                        }
                        //楼栋不为null
                        if (!StringUtils.isEmpty(roomTempHum.getRoomEquipment().getHouseHold().getBuilding().getId())) {
                            Predicate id = cb.equal(roomEquipmentJoin.get("houseHold").get("building").get("id"), roomTempHum.getRoomEquipment().getHouseHold().getBuilding().getId()
                            );
                            list.add(id);
                        }
                    }
                }
                //业务日期筛选
                if (roomTempHum.getStartToEnd() != null && roomTempHum.getStartToEnd().get(0) != null && roomTempHum.getStartToEnd().get(1) != null) {
                    Predicate re_time = cb.between(root.get("reTime").as(String.class), convertDate(roomTempHum.getStartToEnd().get(0)), convertDate(roomTempHum.getStartToEnd().get(1)));
                    list.add(re_time);
                }

                if (list.size() == 0) {
                    return null;
                } else {
                    Predicate[] p = new Predicate[list.size()];
                    return cb.and(list.toArray(p));
                }
            }
        };

上述代码中的核心解决方案是:

第一步:join 链接设备信息表

第二步:条件:通过join.get("houseHold") 获取到用户,再通过join.get("houseHold").get("community")获取到小区 楼栋也是一样的.

第三步:取值:通过 roomTempHum.getRoomEquipment().getHouseHold().getCommunity().getId() 获取到前端传入的值.(注意:roomTempHum为前端传入的值对象!!!)

通过以上三步,就可以拼接完自己想要实现的条件了,以上方法同样适用于N表查询分页!!!

 

最后,希望对有帮助的人,给点个关注!随时交流