博客

Spring Boot JPA 三表外连接动态查询

多：级联关系中的多方；一：级联关系中的一方

双向：双向关联；单向：单向关联

业务场景：

• 实体HouseEntity（多，双向）中有实体CommunityEntity（一，双向）

  • 实体CommunityEntity（一，单向）中有实体AddressEntity（一）
    • 实体AddressEntity中的字段areaName表示精确到区/县的地址（如杭州市的江干区/西湖区之类的）
    • 需求：要根据areaName查询实体HouseEntity，因此要使用到三张表的关联

• Dao层继承接口

  @Repository
  public interface HouseDao extends JpaRepositoryImplementation<HouseEntity, Long> {}
  

• Service编写动态查询方法

  /**
   * 动态创建条件
   */
  private Specification<HouseEntity> getWhereClause(final HouseEntity house) {
      return new Specification<HouseEntity>() {
          @Override
          public Predicate toPredicate(Root<HouseEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
              List<Predicate> predicates = new ArrayList<>();
              //地址head查询，三表联查
              if (!EmptyUtils.isEmpty(house.getCommunityEntity())&&!EmptyUtils.isEmpty(house.getCommunityEntity().getAddressHead()) && !EmptyUtils.isEmpty(house.getCommunityEntity().getAddressHead().getAreaName())) {//三个判空操作
                  Join<HouseEntity, CommunityEntity> entityJoin = root.join("communityEntity", JoinType.LEFT);
                  Join<Object, AddressEntity> JoinThird = entityJoin.join("addressHead", JoinType.LEFT);
                  predicates.add(cb.like(JoinThird.get("areaName").as(String.class), "%"+house.getCommunityEntity().getAddressHead().getAreaName()+"%"));
              }
              
              //售价范围，两表联查
              if (!EmptyUtils.isEmpty(house.getPriceType()) && !EmptyUtils.isEmpty(house.getPriceType().getId())) {
                  Join<HouseEntity, DictEntity> join = root.join("priceType", JoinType.LEFT);
                  predicates.add(cb.equal(join.get("id").as(Long.class), house.getPriceType().getId()));
              }
              
              //其它查询条件略······
  
              Predicate[] array = new Predicate[predicates.size()];
              return query.where(predicates.toArray(array)).getRestriction();
          }
      };
  }
  /**
   * 查询方法
   * @param houseEntity
   * @param sort
   * @param page
   * @param pageSize
   * @return
   */
  @Override
  public Page<HouseEntity> findByHouseByPage(HouseEntity houseEntity, Sort sort, Integer page, Integer pageSize) {
  
      try {
          PageRequest pageRequest = PageRequest.of(page - 1, pageSize, sort);
          Specification<HouseEntity> specification = getWhereClause(houseEntity);
          Page<HouseEntity> pageModel = houseDao.findAll(specification, pageRequest);
          return pageModel;
      } catch (Exception e) {
          e.printStackTrace();
          return null;
      }
  }
  

  Controller

  @Autowired
  private HouseService houseService;
  
  @RequestMapping("toSecondList")
  public ModelAndView toSecondListPage(HouseEntity houseSearch, @RequestParam(value = "sortField", defaultValue = "createTime") String sortField, @RequestParam(defaultValue = "DESC") String order) {
      //仅显示关键代码，其余略······
      
      //2.2.设置排序,三个循环防止判断攻击
      Sort sort = null;
      if (order.equalsIgnoreCase("DESC")) {
          sort = Sort.by(Sort.Direction.DESC, sortField);
      } else if (order.equalsIgnoreCase("ASC")) {
          sort = Sort.by(Sort.Direction.ASC, sortField);
      } else {
          sort = Sort.by(Sort.Direction.DESC, sortField);
      }
  
      //3.执行动态查询
      Page<HouseEntity> pageModel = houseService.findByHouseByPage(houseSearch, sort, 1, 10);
      List<HouseEntity> list = pageModel.getContent();
      for (HouseEntity h :
              list) {
          System.out.println(h);
          System.out.println(h.getCommunityEntity().getAddressHead().getAreaName());
      }
  
      //4.返回视图模型
      ModelAndView modelAndView = new ModelAndView("user/SecondHousePage");
      //4.1.分页对象存入域
      modelAndView.addObject("pageModel", pageModel);
  
      return modelAndView;
  }