Spring boot jpa 多表关联查询

来源：互联网发布：js geturlparameter 编辑：程序博客网时间：2024/06/15 13:57

效果图：

如图，根据条件查询主表信息，并关联获取详细数据，并实现简单的排序功能，不多说，上代码：

model：

/** * @author xj * @date 2017/5/3 13:31 */@Getter@Setter@Entity(name = "BOND_RATE")public class BondRate {    @Id    private String id;    private String bondId;    private String rateStaff;    private String postStatus;    private String internalRate;    private String rateView;    private String attachmentId;    private Date rateTime;    @ManyToOne(fetch = FetchType.LAZY)    @JoinColumn(name = "bondId", insertable = false, updatable = false)    @JsonIgnore    @NotFound(action = NotFoundAction.IGNORE)    private Bond bond;}

Controller：

/** * 根据条件查询债券评级的接口 * * @param bondId * @param bondName * @param rateStaff * @param postStatus * @param startTime * @param endTime * @param pageable * @return * @throws ParseException */@GetMapping(value = "/allBondRate")public Result findAllBondRate(@RequestParam(value = "bondId", required = false) String bondId,                              @RequestParam(value = "bondName", required = false) String bondName,                              @RequestParam(value = "rateStaff", required = false) String rateStaff,                              @RequestParam(value = "postStatus", required = false) String postStatus,                              @RequestParam(value = "startTime", required = false) String startTime,                              @RequestParam(value = "endTime", required = false) String endTime,                              @RequestParam(value = "internalRate", required = false) String[] internalRate,                              @PageableDefault(value = 15, sort = {"rateTime"}, direction =                                      Sort.Direction.DESC) Pageable pageable) throws ParseException {    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");    if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {        startTime = startTime + " 00:00:00";        endTime = endTime + " 23:59:59";    }    Date sTime = null;    Date eTime = null;    if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {        sTime = sf.parse(startTime);        eTime = sf.parse(endTime);    }    Grid grid = bondService.findAllBondRate(bondId, bondName, rateStaff, postStatus, sTime, eTime, internalRate, pageable);    return ResultUtil.success(grid);}

Service：

/** * 根据条件查询债券评级的方法 * * @param bondId * @param bondName * @param rateStaff * @param postStatus * @param startTime * @param endTime * @param pageable * @return */public Grid findAllBondRate(final String bondId, String bondName, final String rateStaff, final String postStatus,                            final Date startTime, final Date endTime, final String[] internalRate, Pageable pageable) {    /**     *  内部评级字段评级排序，特殊处理     */    if (pageable.getSort().getOrderFor("internalRate") != null) {        Sort.Order order = null;        if ("DESC".equals(pageable.getSort().getOrderFor("internalRate").getDirection().toString())) {            order = new Sort.Order(Sort.Direction.ASC, "internalRate");        } else {            order = new Sort.Order(Sort.Direction.DESC, "internalRate");        }        Sort sort = new Sort(order);        pageable = new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), sort);    }    Specification<BondRate> specification = new Specification<BondRate>() {        @Override        public Predicate toPredicate(Root<BondRate> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {            Join<BondRate, Bond> bondJoin = root.join("bond", JoinType.LEFT);            ArrayList<Predicate> conditions = new ArrayList();            if (!StringUtils.isEmpty(bondId)) {                Path comIdPath = root.get("bondId");                Predicate p1 = cb.equal(comIdPath, bondId);                conditions.add(p1);            }            if (!StringUtils.isEmpty(rateStaff)) {                Path rateStaffPath = root.get("rateStaff");                Predicate p2 = cb.equal(rateStaffPath, rateStaff);                conditions.add(p2);            }            if (!StringUtils.isEmpty(postStatus)) {                Path postStatusPath = root.get("postStatus");                Predicate p3 = cb.equal(postStatusPath, postStatus);                conditions.add(p3);            }            if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {                Path rateTimePath = root.get("rateTime");                Predicate p4 = cb.between(rateTimePath, startTime, endTime);                conditions.add(p4);            }            if (internalRate != null && internalRate.length > 0) {                Path internalRatePath = root.get("internalRate");                CriteriaBuilder.In<String> in = cb.in(internalRatePath);                for (int i = 0; i < internalRate.length; i++) {                    in.value(internalRate[i]);                }                conditions.add(in);            }            Predicate[] p = new Predicate[conditions.size()];            return cb.and(conditions.toArray(p));        }    };    Page<BondRate> page = bondRateRepository.findAll(specification, pageable);    Grid grid = new Grid(null, (int) page.getTotalElements());    List list1 = new ArrayList();    list1.add("bondName");    list1.add("bondType");    list1.add("internalRate");    list1.add("rateStaff");    list1.add("rateTime");    list1.add("postStatus");    grid.setIds(list1);    List list2 = new ArrayList();    list2.add("债券名称");    list2.add("债券类别");    list2.add("内部评级");    list2.add("评级人");    list2.add("评级时间");    list2.add("状态");    grid.setHds(list2);    grid.setRows(getBondRateList(page.getContent(), null));    return grid;}/** * 根据list返回对应要求的list形式 * * @param list * @param attachmentList * @return */private List<Map> getBondRateList(List<BondRate> list, List<Attachment> attachmentList) {    List result = new ArrayList();    for (BondRate cr : list) {        Map m = new HashMap();        m.put("id", cr.getId());        m.put("bondId", cr.getBondId());        if (cr.getBond() != null) {            m.put("bondName", cr.getBond().getSecuabbr());            m.put("bondType", cr.getBond().getSecucategory());        }        m.put("internalRate", cr.getInternalRate());        m.put("rateStaff", cr.getRateStaff());        m.put("rateTime", cr.getRateTime());        m.put("postStatus", cr.getPostStatus());        m.put("rateView", cr.getRateView());        //判断是根据ID查询还是全部查询        if (attachmentList != null && attachmentList.size() > 0) {            List list1 = new ArrayList();            for (int i = 0; i < attachmentList.size(); i++) {                Map map = new HashMap();                map.put("uid", attachmentList.get(i).getId());                map.put("name", attachmentList.get(i).getAttachmentName());                list1.add(map);            }            m.put("attachment", list1);        }        result.add(m);    }    return result;}

// like用法 demoif (!StringUtils.isEmpty(comName)) {    Path comNamePath = root.get("companyView").get("chiname");    p = criteriaBuilder.like(comNamePath, "%" + comName + "%");    conditions.add(p);}

// 条件关联查询 demoif (!StringUtils.isEmpty(industry)) {    List<Industry> list = industryRepository.findByThird(industry);    if (list != null && !list.isEmpty()) {        String industryId = list.get(0).getId();        Path industryIdPath = root.get("industryId");        Predicate p2 = criteriaBuilder.equal(industryIdPath, industryId);        conditions.add(p2);    } else {        throw new ServiceException(ResultEnum.NO_INDUSTRY);    }}

11 2