©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>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
PredicatetoPredicate(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; }