Spring boot jpa 多表关联查询

效果图:


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

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用法 demo
if (!StringUtils.isEmpty(comName)) {
    Path comNamePath = root.get("companyView").get("chiname");
    p = criteriaBuilder.like(comNamePath, "%" + comName + "%");
    conditions.add(p);
}
// 条件关联查询 demo
if (!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);
    }
}

发布了106 篇原创文章 · 获赞 258 · 访问量 33万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览