jpa,querydsl,jpaquery,sqlquery 实现左连接临时表,casewhen，及stringtemplate用法_越沂的博客-爱代码爱编程

@Service
public class StatisticalCommonServiceImpl implements StatisticalCommonService, ISqlEntity {

  public SQLQuery getAssetDocByDocTypesAndSendTimeOrEHospitalTime2(LocalDateTime sendstarttime, LocalDateTime sendendtime,
                                                                     LocalDateTime entrystarttime, LocalDateTime entryendtime, List<Long> departIds,
                                                                     Long userid, Boolean isNursePush, Boolean isConfirmed,
                                                                     Boolean isDoctorSend, String docType, String grade1, String grade2,
                                                                     String otherDocType, String otherGrade, String preMeasure) {

        Map<String, List<Long>> docGitlogMap = this.getConfigDocGitlog();
        //case when jpa用法
        CaseBuilder.Cases<String, StringExpression> expression1 = new CaseBuilder()
                .when(filterDocTypeAndGrade1(docType, grade1, grade2, docGitlogMap)).then(docType);
        CaseBuilder.Cases<String, StringExpression> expression = new CaseBuilder()
                .when(filterDocTypeAndGrade1(otherDocType, otherGrade, null, docGitlogMap)).then(otherDocType);
        //临时表路径及表别名
        final StringPath xxx = Expressions.stringPath("xxx");

        final StringPath yyy = Expressions.stringPath("yyy");

        //构建SQLQuery无法获取的函数查询
        StringTemplate vte = Expressions.stringTemplate("group_concat({0})", Expressions.stringPath(xxx, "vte"));
        StringTemplate blooding = Expressions.stringTemplate("group_concat({0})", Expressions.stringPath(xxx, "blooding"));

        StringExpression stringExpression = null;
        SQLQuery query1 = sqlQueryFactory.selectDistinct(
                getPatVisitA().id.as("visitid"), getPatVisitA().visittime.as("visittime"),
                getPatVisitA().entryhospital.as("entryhospital"), getPatVisitA().departmentid.as("departmentid"), getPatScoreResultA().groupno,
                expression1.otherwise(stringExpression).as("vte"),
                expression.otherwise(stringExpression).as("blooding"))
                .from(getPatScoreResult().as("patScoreResult"))
                .leftJoin(getPatVisit(), getPatVisitA()).on(getPatVisitA().id.eq(getPatScoreResultA().visitid))
                .leftJoin(getPatVisitExtra(), getPatVisitExtraA()).on(getPatVisitA().id.eq(getPatVisitExtraA().visitid))
                .leftJoin(getDocInfo(), getDocInfoA()).on(getDocInfoA().id.eq(getPatScoreResultA().docid))
                .leftJoin(getGitDocInfo(), getGitDocInfoA()).on(getGitDocInfoA().id.eq(getDocInfoA().parent));
        basicWhereFilter1(query1, departIds, null, null, null, isConfirmed, isDoctorSend, isNursePush);
        //医生推送时间筛选
        if (sendendtime != null && sendstarttime != null) {
            query1.where(getPatScoreResultA().sendtime.between(sendstarttime, sendendtime));
        }
        //入院时间或就诊时间筛选
        if (entrystarttime != null && entryendtime != null) {
            query1.where(getPatVisitA().entryhospital.between(entrystarttime, entryendtime).or(getPatVisitA().visittime.between(entrystarttime, entryendtime)));
        }
        //预防方式筛选
        if (StringUtils.isNotEmpty(preMeasure)) {
            BooleanExpression combooleanTemplate = Expressions.booleanTemplate(" JSON_EXTRACT({0}, '$.title')={1} ", getPatScoreResultA().advice, preMeasure);
            query1.where(getPatScoreResultA().advice.isNotEmpty().and(getPatScoreResultA().advice.isNotNull()).and(combooleanTemplate));
        }

        //连接临时表及查询临时表字段用法
        SQLQuery query2 = sqlQueryFactory.select(
                Expressions.numberPath(Long.class, xxx, "visitid").max().as("visitid"), Expressions.dateTimePath(LocalDateTime.class, xxx, "visittime").max().as("visittime"),
                Expressions.dateTimePath(LocalDateTime.class, xxx, "entryhospital").max().as("entryhospital"), Expressions.numberPath(Long.class, xxx, "departmentid").max().as("departmentid"),
                vte.as("vte"), blooding.as("blooding")
        ).from(query1, xxx).groupBy(Expressions.numberPath(Long.class, xxx, "groupno"));

        SQLQuery<TTaskPatient> query3 = sqlQueryFactory.select(Projections.bean(TTaskPatient.class,
                Expressions.numberPath(Long.class, yyy, "visitid"), Expressions.dateTimePath(LocalDateTime.class, yyy, "visittime"),
                Expressions.dateTimePath(LocalDateTime.class, yyy, "entryhospital"), Expressions.numberPath(Long.class, yyy, "departmentid")
        )).from(query2, yyy)
                .where(Expressions.stringPath(yyy, "vte").isNotNull()
                        .and(Expressions.stringPath(yyy, "blooding").isNotNull()));

        return query3;
    }
}

public interface ISqlEntity {

    default QPatScoreResult getPatScoreResult() {
        return new QPatScoreResult("pat_score_result");
    }


    default QPatVisit getPatVisit() {
        return new QPatVisit("pat_visit");
    }

    default QPatVisit getPatVisitA() {
        return new QPatVisit("patVisit");
    }

//PatScoreResult实体表达式路径
    default QPatScoreResult getPatScoreResultA() {
        return new QPatScoreResult("patScoreResult");
    }

    default QPatVisitExtra getPatVisitExtra() {
        return new QPatVisitExtra("pat_visit_extra");
    }

    default QPatVisitExtra getPatVisitExtraA() {
        return new QPatVisitExtra("patVisitExtra");
    }

    default QDocInfo getDocInfo() {
        return new QDocInfo("doc_info");
    }

    default QDocInfo getDocInfoA() {
        return new QDocInfo("docInfo");
    }

    default QDocInfo getGitDocInfo() {
        return new QDocInfo("doc_info");
    }

    default QDocInfo getGitDocInfoA() {
        return new QDocInfo("gitDocInfo");
    }

}