CriteriaBuilder JPA：子查询中的Multiselect/groupby/maxresult

Question

目前，我正在为客户列表开发一个“过滤器”功能。

我有以下子查询，我不确定是否有办法用CriteriaBuilder构建它。它也可以在没有multiselectb.date的情况下工作，...但是我需要Group By、order By和Limit来选择最小值。结果应该是一个数字。

SELECT (COALESCE(SUM(b.account1),0) + COALESCE(SUM(b.account2),0) + COALESCE(SUM(b.deposits),0)) AS SUM FROM customer_balance b WHERE b.client_nr = '123455'
GROUP BY b.date
ORDER BY SUM
LIMIT 1

例如客户余额表

"client_nr","date","account1","account2","deposits"
"1234567","2018-02-28",204600.0,82500.0,21120.0

更新：这是我当前的代码，但是

Subquery<BigDecimal> CustomerBalanceSubQuery = CustomerQuery.subquery(BigDecimal.class);
Root<CustomerBalance> CustomerBalance = CustomerBalanceSubQuery.from(CustomerBalance.class);

Expression<BigDecimal> currentAccount = criteriaBuilder.sum(CustomerBalance.get("currentAccount"));
Expression<BigDecimal> overnightDeposits = criteriaBuilder.sum(CustomerBalance.get("overnightDeposits"));
Expression<BigDecimal> termDeposits = criteriaBuilder.sum(CustomerBalance.get("termDeposits"));

CustomerBalanceSubQuery.select(criteriaBuilder.sum(termDeposits, criteriaBuilder.sum(overnightDeposits, currentAccount)));
Predicate predicate = criteriaBuilder.equal(CustomerBalance.get("id").get("ClientNr"), Customer.get("ClientNr"));
CustomerBalanceSubQuery.where(predicate);

// adding to main-query
queryPredicateList.add(criteriaBuilder.and(criteriaBuilder.greaterThan(CustomerBalanceSubQuery, BigDecimal.valueOf(liquidity))));

• 如何为我的子查询添加排序依据
• 如何设置maxResult

谢谢！B

Answer 1

与一些同事交谈后，我将查询重构为：

SELECT min（COALESCE（b.current_account，0）COALESCE（b.overnight_deposits，0）COALESCE（b.term_deposits，0））从customer_balanceb哪里b.client_nr='123456'

代码。。。

Subquery<BigDecimal> customerBalanceSubQuery = customerQuery.subquery(BigDecimal.class);
Root<CustomerBalance> customerBalance = customerBalanceSubQuery.from(CustomerBalance.class);

Expression<BigDecimal> currentAccount = customerBalance.get("currentAccount");
Expression<BigDecimal> overnightDeposits = customerBalance.get("overnightDeposits");
Expression<BigDecimal> termDeposits = customerBalance.get("termDeposits");

customerBalanceSubQuery.select(criteriaBuilder.min(criteriaBuilder.sum(termDeposits, criteriaBuilder.sum(overnightDeposits, currentAccount))));
Predicate predicate = criteriaBuilder.equal(customerBalance.get("id").get("clientNr"), customer.get("clientNr"));
customerBalanceSubQuery.where(predicate);

queryPredicateList.add(criteriaBuilder.and(criteriaBuilder.greaterThan(CustomerBalanceSubQuery, BigDecimal.valueOf(liquidity))));

不管怎样，从原始问题中查看查询会很有趣。除了提到的@BillyFrost之外，我不确定是否每个sql查询都可以用criteriaapi构建。