CASE WHEN in querydsl generate from list

Question

I would like to create a NumberExpression for the SELECT clause in QueryDsl using a CASE WHEN THEN construct for retrieving session number (business data). Currently, I'm having this kind of method that create SQL in String which is bad for many reasons.

    private static StringBuilder getSelectStatement(
        UUID jobInstanceUuid,
        LocalDateTime jobInstanceStartDateTime,
        List<CotCandidate> cotCandidates
    ) {
        StringBuilder selectStatement = new StringBuilder();
        selectStatement.append("SELECT ");
        selectStatement.append(getCaseOfSessionNumber(cotCandidates));
        // skipped other fields for clarity
        selectStatement.append(getFromStatement());
       // skipped where, having, groupBy statements for clarity
       selectStatement.append(" ");

        return selectStatement;
    }

    private static String getCaseOfSessionNumber(List<CotCandidate> cotCandidates) {
        StringBuilder caseSessionNumber = new StringBuilder();
        caseSessionNumber.append("CASE ");
        cotCandidates.forEach(cot -> {
            caseSessionNumber.append(" WHEN (P.SETTLEMENT_SYSTEM = ");
            caseSessionNumber.append(toSqlString(cot.getSettlementSystem()));
            caseSessionNumber.append(" AND P.PAYMENT_MODE = ");
            caseSessionNumber.append(toSqlString(cot.getPaymentMode()));
            caseSessionNumber.append(" ) THEN ");
            caseSessionNumber.append(cot.getSessionNumb());
            caseSessionNumber.append(" ");
        });
        caseSessionNumber.append("END");
        return caseSessionNumber.toString();
    }

And with querydsl I would like to achived smth like that:

    void getJobExecutionQuery(List<CotCandidate> cotCandidates) {
        QPaymentOrderEntity order = QPaymentOrderEntity.paymentOrderEntity;
        JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);
        List<Tuple> tuple = queryFactory
            .from(order)
            .select(
                getSessionNumb(cotCandidates, order),
            )
            .fetch();
    }

    // This doesn't work cause without otherwise() in the end it return Case.Builder.Cases
    private NumberExpression<Integer> getSessionNumb(List<CotCandidate> cotCandidates, QPaymentOrderEntity order) {
        return cotCandidates.stream()
            .map( cot ->
            Expressions.cases()
                    .when(order.settlementSystem.eq(cot.getSettlementSystem())
                        .and(order.paymentMode.eq(cot.getPaymentMode())))
                    .then(cot.getSessionNumb())
            ).collect(?);
    }

The problem is that I don't know how to build dynamicly case when then : 
To be like that: 

            NumberExpression<Integer> sessionNumber = new CaseBuilder()
            .when(
                order.settlementSystem.eq(cotCandidates.get(0).getSettlementSystem())
                .and(order.paymentMode.eq(cotCandidates.get(0).getPaymentMode())))
            .then(cotCandidates.get(0).getSessionNumb())
            .when(
                order.settlementSystem.eq(cotCandidates.get(n).getSettlementSystem())
                    .and(order.paymentMode.eq(cotCandidates.get(n).getPaymentMode())))
            .then(cotCandidates.get(n).getSessionNumb())
            .otherwise(-1);

Answer 1

The problem is that a CASE statement consists of three parts:

1. Initial case (CaseBuilder)
2. The middle part (CaseBuilder.Cases)
3. The final part (CaseBuilder.Cases#otherwise(...))

Unfortunately these builder types provide no common interface which basically throws your options for a fluent reducer out of the window. The initial case always has to be dealt with inidivually:

QPaymentOrderEntity order = QPaymentOrderEntity.paymentOrderEntity;
CaseBuilder caseBuilder = Expressions.cases();
CotCandidate candidate = candidates.get(0);
CaseBuilder.Cases<Integer, NumberExpression<Integer>> intermediateBuilder = caseBuilder.when(order.paymentMode.eq(candidate.getPaymentMode())
        .and(order.settlementSystem.eq(candidate.getSettlementSystem()))).then(candidate.getSessionNumb());

for (int i = 1; i < candidates.size(); i++) {
    candidate = candidates.get(i); 
    intermediateBuilder = intermediateBuilder.when(order.paymentMode.eq(candidate.getPaymentMode())).then(candidate.getSessionNumb());
}

NumberExpression<Integer> finalExpression = intermediateBuilder.otherwise(-1);

The loop is technically a foldLeft operation. Unfortunately that can't be expressed in a Stream easily, as the Stream API only provides the parallelizable reduce operation. Two different CaseWhen builders can't be combined, and therefore this operation is not parallelizable. For a more detailed answer on foldLeft versus reduce, see https://stackoverflow.com/a/24316429/2104280 .

---

On a side note:

Aren't you reinventing the wheel here? From the properties on QPaymentOrderEntity it seems that PaymentMode and SettlementSystem are managed types. The query would probably be a lot easier to just join the CotCandidates and get the sessionNumb from there;

query().from(order).innerJoin(QCotCandidate.cotCandidate)
   .on(QCotCandidate.cotCandidate.settlementSystem.eq(order.settlementSystem)
       .and(QCotCandidate.paymentMode.settlementSystem.eq(order.paymentMode))
   .select(order, QCotCandidate.cotCandidate.sessionNumb)

Anyways, it CotCandiate might not be a managed entity. In which case you'll need a VALUES clause, which JPQL by default hasn't. (You might want to consider the blaze-persistence-querydsl integration for that).