如何使用CriteriaQuery转换指定的MySQL SQL查询语句

Got it, let's walk through converting your MySQL query to a CriteriaQuery implementation step by step. I'll assume you have a JPA entity SummaryFee mapped to your summaryfee table—tweak field names (like camelCase vs snake_case) if they don't match your actual entity setup.

Step 1: Define Basic Parameters #

First, set up the fixed values from your original query:

import javax.persistence.criteria.*;
import java.math.BigDecimal;
import java.time.LocalDate;

// Adjust date type if your entity uses String instead of LocalDate
LocalDate targetDateA = LocalDate.parse("2019-06-30");
LocalDate targetDateB = LocalDate.parse("2020-06-30");
String cbGroup = "CORPORATE BANKING 1";

EntityManager entityManager = // Get your EntityManager instance
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

Step 2: Build Subquery A (2019-06-30 Summary) #

This matches the first subquery in your SQL, which aggregates fees for the 2019 date:

// Subquery for 2019 data (alias 'a' in original SQL)
CriteriaQuery<Tuple> subQueryA = criteriaBuilder.createTupleQuery();
Root<SummaryFee> rootA = subQueryA.from(SummaryFee.class);

subQueryA.multiselect(
    rootA.get("customer"),
    rootA.get("cbgroup"),
    rootA.get("cbdept"),
    rootA.get("buc"),
    rootA.get("customerGroup"), // Maps to customer_group in DB
    rootA.get("product"),
    rootA.get("valuta"),
    criteriaBuilder.sum(rootA.get("amount")).alias("a_amount")
)
.where(
    criteriaBuilder.equal(rootA.get("date"), targetDateA),
    criteriaBuilder.equal(rootA.get("cbgroup"), cbGroup)
)
.groupBy(rootA.get("customer"));

Step 3: Build Subquery B (2020-06-30 Summary) #

This is the second subquery, aggregating fees for the 2020 date:

// Subquery for 2020 data (alias 'b' in original SQL)
CriteriaQuery<Tuple> subQueryB = criteriaBuilder.createTupleQuery();
Root<SummaryFee> rootB = subQueryB.from(SummaryFee.class);

subQueryB.multiselect(
    rootB.get("customer"),
    criteriaBuilder.sum(rootB.get("amount")).alias("b_amount")
)
.where(
    criteriaBuilder.equal(rootB.get("date"), targetDateB),
    criteriaBuilder.equal(rootB.get("cbgroup"), cbGroup)
)
.groupBy(rootB.get("customer"));

Step 4: Build the Main Query #

Now we'll join the two subqueries, calculate the percentage difference, apply filters, sort, and limit results:

// Main query
CriteriaQuery<Tuple> mainQuery = criteriaBuilder.createTupleQuery();

// Treat subqueries as roots in the main query
Root<Tuple> subRootA = mainQuery.from(subQueryA);
Root<Tuple> subRootB = mainQuery.from(subQueryB);

// Join condition: match customers from both subqueries
Predicate joinCondition = criteriaBuilder.equal(subRootA.get("customer"), subRootB.get("customer"));

// Calculate percentage: (b.b_amount - a.a_amount)
Expression<BigDecimal> aAmount = subRootA.get("a_amount").as(BigDecimal.class);
Expression<BigDecimal> bAmount = subRootB.get("b_amount").as(BigDecimal.class);
Expression<BigDecimal> percentage = criteriaBuilder.subtract(bAmount, aAmount).alias("percentage");

// Filter: only include results where percentage is negative
Predicate filterCondition = criteriaBuilder.lessThan(percentage, BigDecimal.ZERO);

// Sort logic: prioritize customers NOT containing "Transaksi Nasabah", then sort by percentage ascending
Order sortByCustomer = criteriaBuilder.selectCase()
    .when(criteriaBuilder.like(subRootA.get("customer").as(String.class), "%Transaksi Nasabah%"), 1)
    .otherwise(0)
    .asc();
Order sortByPercentage = criteriaBuilder.asc(percentage);

// Assemble the main query
mainQuery.multiselect(
    subRootA.get("customer").alias("customer"),
    subRootA.get("cbgroup").alias("cbgroup"),
    subRootA.get("cbdept").alias("cbdept"),
    subRootA.get("buc").alias("buc"),
    subRootA.get("customerGroup").alias("customer_group"),
    subRootA.get("product").alias("product"),
    subRootA.get("valuta").alias("valuta"),
    aAmount.alias("a_amount"),
    bAmount.alias("b_amount"),
    percentage
)
.where(criteriaBuilder.and(joinCondition, filterCondition))
.orderBy(sortByCustomer, sortByPercentage)
.setMaxResults(5);

// Execute the query
List<Tuple> results = entityManager.createQuery(mainQuery).getResultList();

Optional: Use a Custom DTO Instead of Tuple #

If you prefer type-safe results over Tuple, create a DTO class:

public class FeeComparisonResult {
    private String customer;
    private String cbgroup;
    private String cbdept;
    private String buc;
    private String customerGroup;
    private String product;
    private String valuta;
    private BigDecimal aAmount;
    private BigDecimal bAmount;
    private BigDecimal percentage;

    // Constructor must match the order of fields in mainQuery.multiselect
    public FeeComparisonResult(String customer, String cbgroup, String cbdept, String buc,
                               String customerGroup, String product, String valuta,
                               BigDecimal aAmount, BigDecimal bAmount, BigDecimal percentage) {
        this.customer = customer;
        this.cbgroup = cbgroup;
        this.cbdept = cbdept;
        this.buc = buc;
        this.customerGroup = customerGroup;
        this.product = product;
        this.valuta = valuta;
        this.aAmount = aAmount;
        this.bAmount = bAmount;
        this.percentage = percentage;
    }

    // Add getters for all fields
}

Then modify the main query to use this DTO:

CriteriaQuery<FeeComparisonResult> mainQuery = criteriaBuilder.createQuery(FeeComparisonResult.class);

// ... Keep subRootA, subRootB, joinCondition, percentage, filterCondition, sort logic the same

mainQuery.select(criteriaBuilder.construct(FeeComparisonResult.class,
    subRootA.get("customer"),
    subRootA.get("cbgroup"),
    subRootA.get("cbdept"),
    subRootA.get("buc"),
    subRootA.get("customerGroup"),
    subRootA.get("product"),
    subRootA.get("valuta"),
    aAmount,
    bAmount,
    percentage
))
.where(criteriaBuilder.and(joinCondition, filterCondition))
.orderBy(sortByCustomer, sortByPercentage)
.setMaxResults(5);

List<FeeComparisonResult> results = entityManager.createQuery(mainQuery).getResultList();

Notes #

• If your SummaryFee entity uses String for dates instead of LocalDate, adjust the target date variables to String instead of parsing to LocalDate.
• Ensure the field names in rootA.get("fieldName") match exactly with your entity's field names (camelCase is standard for JPA entities).
• If your amount field is a Double instead of BigDecimal, change the type casting accordingly.

内容的提问来源于stack exchange，提问作者peri purnama