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

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>() {@Overridepublic 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;
  }
  

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！