How to solve the problem of nested subquery of QueryDsl in SpringBoot

QueryDsl nested subquery

In my project, all SQL must be performed in JPA’s QueryDsl format for database operations. There is a slightly more complicated one. There is relatively little information on the Internet about nested subqueries, and the subqueries are also very simple.

This is the query format of MyBatis

select dd.crm_province as 省编码,
         dd.province_name as 省份,
         nvl(dd.anncount, 0) as 年化收入,
         nvl(dd.yicansai, 0) as 已参赛人数,
         nvl(ss.weicansai, 0) as 未参赛人数,
         rank() over(order by nvl(dd.anncount, 0) desc)  as 排名
    from (select ll.crm_province,
                 ll.province_name,
                 nvl(sum(ll.annuali_count), 0) anncount,
                 count(distinct ll.staff_id) yicansai
            from tm_match_report_user ll
           where ll.is_close = 1
             and ll.role_id = 00
             and ll.is_login = 1
           group by ll.crm_province, ll.province_name) dd
    left join (select ll.crm_province, count(distinct ll.staff_id) weicansai
                 from tm_match_report_user ll
                where ll.is_close = 1
                  and ll.role_id = 00
                  and ll.is_login = 0
                group by ll.crm_province) ss
      on dd.crm_province = ss.crm_province
   order by dd.anncount desc

This is the QueryDsl format of JPA

 /**
   * 复杂sql查询 -- 双子查询Join
   * @return
   */
  public List<StaffRank> topBind() {
    QMatchReportUser user = new QMatchReportUser("tm_match_report_user");
    QMatchReportUser user1 = new QMatchReportUser("tm_match_report_user");
 
    StringPath dd = Expressions.stringPath("dd");
    StringPath ss = Expressions.stringPath("ss");
 
    SimpleTemplate<String> crm_Province = Expressions.template(String.class, "dd.crm_Province");
 
    SimpleTemplate<String> sscrm_Province = Expressions.template(String.class, "ss.crm_Province");
    SimpleTemplate<String> province_Name = Expressions.template(String.class, "dd.province_Name");
    NumberTemplate<Long> anncount = Expressions.numberTemplate(Long.class, "nvl(dd.anncount, 0)");
    NumberTemplate<Long> yicansai = Expressions.numberTemplate(Long.class, "nvl(dd.yicansai, 0)");
    NumberTemplate<Long> weicansai = Expressions.numberTemplate(Long.class, "nvl(ss.weicansai, 0)");
    NumberTemplate<Integer> template = Expressions
        .numberTemplate(Integer.class, "rank() over(order by nvl(dd.anncount, 0) desc)");
 
    OrderSpecifier order = new OrderSpecifier(Order.DESC,
        Expressions.template(String.class, "dd.anncount"));
 
    SubQueryExpression query = SQLExpressions
        .select(user.crm_Province, user.province_Name,
            user.annuali_Count.sum().as("anncount"),
            user.staff_Id.countDistinct().as("yicansai"))
        .from(user)
        .where(user.is_CLOSE.eq(1), user.role_Id.eq("00"), user.is_Login.eq("1"))
        .groupBy(user.crm_Province, user.province_Name);
 
    SubQueryExpression query1 = SQLExpressions
        .select(user1.crm_Province, user1.staff_Id.countDistinct().as("weicansai"))
        .from(user1)
        .where(user1.is_CLOSE.eq(1), user1.role_Id.eq("00"), user1.is_Login.eq("0"))
        .groupBy(user1.crm_Province);
 
    return factory.select(
        Projections.bean(StaffRank.class,
            crm_Province.as("crm_Province"),
            province_Name.as("province_Name"), anncount.as("anncount"),
            yicansai.as("yicansai"), weicansai.as("weicansai"), template.as("rank")))
        .from(query, dd).leftJoin(query1, ss).on(crm_Province.eq(sscrm_Province)).orderBy(order)
        .fetch();
  }

QMatchReportUser user = new QMatchReportUser("tm_match_report_user");

There should be no need to explain this. QueryDsl will automatically generate an entity class with a capital Q. tm_match_report_user is Table Name.

If you need children's shoes, you can refer to them. They are basically the same.

It should be noted that

 NumberTemplate<Integer> template = Expressions
                   .numberTemplate(Integer.class,
                   "rank() over(order by nvl(dd.anncount, 0) desc)");

can also be written in the placeholder style

NumberTemplate<Integer> template = Expressions
                    .numberTemplate(Integer.class, 
                    "rank() over(order by nvl(sum({0}), 0) desc)",quser.opening_Count);

The above is the detailed content of How to solve the problem of nested subquery of QueryDsl in SpringBoot. For more information, please follow other related articles on the PHP Chinese website!