首页 新闻 搜索 专区 学院

java srping data

0
悬赏园豆:10 [已解决问题] 解决于 2017-05-30 14:47

各位大侠,spring data jpa怎么实现多张表组合查询?? 知道的大侠请赐教一下,最好给一个 ?

年少不骄横的主页 年少不骄横 | 初学一级 | 园豆:147
提问于:2017-05-30 13:43
< >
分享
最佳答案
0

1.方法一:直接使用spring-data-jpa d的Specification查询即可;

如下:

Specification<AucEvent> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<Predicate>();
            //指定机构
            if (agencyId != null && !agencyId.isEmpty() && !"0".equals(agencyId)) {
                Predicate predicate = cb.equal(root.get("agencyId"), agencyId);
                predicates.add(predicate);
            }
            //模糊查询,活动标题
            if (eventTitle != null && !eventTitle.isEmpty()) {
                Predicate predicate = cb.like(root.get("title"), "%"+eventTitle+"%");
                predicates.add(predicate);
            }
            //指定状态,可能为多个状态,or
            if (eventStatus != null && eventStatus.length > 0) {
                List<Predicate> orPredicates = new ArrayList<Predicate>();
                for(String status:eventStatus) {
                    Predicate predicate = cb.equal(root.get("status"), status);
                    orPredicates.add(predicate);
                }
                if (!orPredicates.isEmpty()) {
                    Predicate predicate = cb.or(orPredicates.toArray(new Predicate[0]));
                    predicates.add(predicate);
                }
            }
            if (!predicates.isEmpty()) {
                return cb.and(predicates.toArray(new Predicate[0]));
            } else {
                return null;
            }
        };
        Page<AucEvent> pageresult = aucEventRepository.findAll(spec, pageable);

 

方法二:如果表之间没有之间联系的话,可以使用原生的SQL查询,如下:

@Query(value = "SELECT  Max(a.first_type_name) as  acuType, "
            + "sum(CASE b.is_deal WHEN 1 THEN 1 ELSE 0 end) as dealNum, "
            + "SUM(CASE b.is_deal WHEN 1 THEN b.deal_price  ELSE 0 end) as  dealPrice, "
            + "sum(CASE b.is_deal WHEN 4 THEN 1 ELSE 0 end) as  regretDeal, "
            + "ROUND((sum(CASE b.is_deal WHEN 4 THEN 1 ELSE 0 end)/count(b.is_deal))*100,2) as regretDealPercent, "
            + "SUM(CASE b.is_deal WHEN 4 THEN s.bail_price ELSE 0 end) as distrainPrice , "
            + "SUM(CASE b.is_settled when 1 THEN b.deal_price ELSE 0 END) AS isSettedPrice, "
            + "SUM(CASE b.is_settled when 0 THEN b.deal_price ELSE 0 END) AS notSettedPrice  "
            + "FROM  auc_brand as b "
            + "LEFT JOIN   auc_lot as a  on a.id=b.auc_id "
            + "LEFT JOIN   settlement_bail as s  on  b.id=s.brand_id  "
            + "WHERE (DATE_FORMAT(a.crt_time,'%Y-%m-%d %H:%i:%S') "
            + "BETWEEN :startDate AND :endDate) AND  a.agency_id  in (:agencyIds) "
            + "GROUP BY  a.first_type_id ",nativeQuery=true)
    List<Object> findByAgencysAndGroupByTypeAucPriceCountReport(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("agencyIds") List<Long> agencyIds);

项目中刚好使用的是spring-data-jpa,不知道有没有解决你的问题,欢迎指正。

收获园豆:10
Mr_伍先生 | 初学一级 |园豆:6 | 2017-05-30 14:11

大侠,谢谢啊! 第二种方式已经调试出来了!

年少不骄横 | 园豆:147 (初学一级) | 2017-05-30 14:47

@年少不骄横: 不客气,相互学习啦

Mr_伍先生 | 园豆:6 (初学一级) | 2017-05-30 14:50
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册