Spring boot 多表关联查询

返回值：

如图：，返回结果中，根据股票代码关联查询了股票名称，这边spring boot 有自带jpa的写法，下一篇，介绍roo.join();这边介绍下hibernate的写法，

Controller:

 /**
    * 查询最新的标的券得分接口
    * @return
    */
@GetMapping(value = "/allScore")
public Result find(@PageableDefault(value = 15,sort = {"securityScore"},direction = Sort.Direction.DESC)Pageable pageable
                ,@RequestParam(value = "code",required = false) String code){
   Grid grid = svService.findAllScore(pageable,code);
   return ResultUtil.success(grid);
}

Service:

/**
 * 查询最新标的券的得分
 * @return
 */
public Grid findAllScore(Pageable pageable,String code){
    Sort sort = new Sort(Sort.Direction.DESC,"stopTime");
    PageRequest pr = new PageRequest(0,1,sort);
    //findByStopTimeIsNotNull方法就是排除了stoptime字段为空的情况，在用PageRequest排序
    Page<SvScoreRecord> currentScore = svScoreRecordRepository.findByStopTimeIsNotNull(pr);
    String recordId = currentScore.getContent().get(0).getId();
    Page<SvScoreStock> page = null;
    if (StringUtils.isEmpty(code)) {
        page = svScoreRepository.findAllScore(recordId,pageable);
        if (page.getSize() <= 0){
            throw new ServiceException(ResultEnum.NO_FACTOR.getCode(),"查询不到当前股票最新的分");
        }
    } else {
        page = svScoreRepository.findAllScoreByCode(code,recordId,pageable);
        if (page.getSize() <= 0){
            throw new ServiceException(ResultEnum.NO_FACTOR.getCode(),"查询不到所有股票最新的分");
        }
    }
    Grid grid = new Grid(null,page.getTotalPages());
    List list1 = new ArrayList();
    list1.add(0,"tradingcode");
    list1.add(1,"secuabbr");
    list1.add(2,"securityScore");
    list1.add(3,"financeConversionRate");
    list1.add(4,"tradingPledgeRate");
    grid.setIds(list1);
    List list2 = new ArrayList();
    list2.add(0,"股票代码");
    list2.add(1,"股票名称");
    list2.add(2,"标的券得分");
    list2.add(3,"融资融券基准折算率");
    list2.add(4,"股票质押率");
    grid.setHds(list2);
    grid.setRows(page.getContent());
    return grid;
}

Repository:

@Query("select s.tradingcode as tradingcode, s.recordId as recordId, s.securityScore as securityScore," +
        " s.financeConversionRate as financeConversionRate, s.tradingPledgeRate as tradingPledgeRate," +
        " t.secuabbr as secuabbr from SV_SCORE s left join s.stock t where s.tradingcode = ?1 and s.recordId = ?2")
Page<SvScoreStock> findAllScoreByCode(String code,String recordId,Pageable pageable);

@Query("select s.tradingcode as tradingcode, s.recordId as recordId, s.securityScore as securityScore," +
        " s.financeConversionRate as financeConversionRate, s.tradingPledgeRate as tradingPledgeRate," +
        " t.secuabbr as secuabbr from SV_SCORE s left join s.stock t where s.recordId = ?1")
Page<SvScoreStock> findAllScore(String recordId,Pageable pageable);

下一篇，root.join()，http://blog.csdn.net/qq_34117825/article/details/71123876