How to solve the problem of nested subquery of QueryDsl in SpringBoot-javaTutorial-php.cn

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

Copy after login

This is the QueryDsl format of JPA

/** * 复杂sql查询 -- 双子查询Join * @return */ public List 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 crm_Province = Expressions.template(String.class, "dd.crm_Province"); SimpleTemplate sscrm_Province = Expressions.template(String.class, "ss.crm_Province"); SimpleTemplate province_Name = Expressions.template(String.class, "dd.province_Name"); NumberTemplate anncount = Expressions.numberTemplate(Long.class, "nvl(dd.anncount, 0)"); NumberTemplate yicansai = Expressions.numberTemplate(Long.class, "nvl(dd.yicansai, 0)"); NumberTemplate weicansai = Expressions.numberTemplate(Long.class, "nvl(ss.weicansai, 0)"); NumberTemplate 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(); }

Copy after login

QMatchReportUser user = new QMatchReportUser("tm_match_report_user");

Copy after login

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 template = Expressions .numberTemplate(Integer.class, "rank() over(order by nvl(dd.anncount, 0) desc)");

Copy after login

can also be written in the placeholder style

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

Copy after login

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!