原生的sql中使用的函数,具体的sql实现如下:
select tt.extend_id,
tt.extend_name,
wm_concat(distinct tt.extend_code),
sum(rr.annuali_count),
count(distinct rr.staff_id)
from tm_duct_whole rr
left join tm_duct_extend_data tt
on rr.industry_type = tt.extend_code
where tt.extend_type = 3
and rr.is_close = 0
and rr.is_login = 1
and rr.industry_type is not null
group by tt.extend_id, tt.extend_name
转化为jpa实现的编程,借助模板使用函数。具体的操作如下所示:
//编译生成的实体类
QTmDuctWhole tmDuctWhole = QTmDuctWhole.tmDuctWhole;
QBasicData basicData = QBasicData.basicData;
//求和
SimpleTemplate<Double> annualiCount = Expressions
.template(Double.class, "round(sum(ANNUALI_COUNT))");
//进行字符串拼接
SimpleTemplate<String> extendCode = Expressions
.template(String.class, "to_char(wm_concat(distinct EXTEND_CODE))");
//计算总人数
SimpleTemplate<Integer> userCount = Expressions
.template(Integer.class, " count(distinct staff_id)");
List<TrackBean> industryList = secondaryJpaSqlQuery()
.select(Projections.bean(TrackBean.class,
basicData.extendId.as("extendId"),
basicData.extendName.as("extendName"),
extendCode.as("extendCode"),
annualiCount.as("annualiCount"),
userCount.as("count")))
.from(tmDuctWhole).leftJoin(basicData)
.on(tmDuctWhole.industryType.eq(basicData.extendCode))
.where(basicData.extendType.eq(3)
.and(tmDuctWhole.isClose.eq(0))
.and(tmDuctWhole.isLogin.eq(1))
.and(tmDuctWhole.industryType.isNotEmpty()))
.groupBy(basicData.extendId, basicData.extendName)
.fetch();