Is it possible to include COUNT into multiselect using TypedQuery in java 7?-CodePudding

SQL (that works in database):

SELECT ffm.REPORTING_PERIOD, count(DISTINCT ffm.FI_MESSAGE_ID), count(ffa.FI_ACCOUNT_ID) FROM RAZV.FINTAEOI2_FI_MESSAGE ffm
JOIN razv.FINTAEOI2_FI_MESSAGE_FI ffmf ON ffm.FI_MESSAGE_ID = ffmf.FI_MESSAGE_ID
JOIN razv.FINTAEOI2_FI_ACCOUNT ffa ON ffmf.FI_MESSAGE_FI_ID = ffa.FI_MESSAGE_FI_ID
WHERE ffm.REPORTING_PERIOD = '2020-12-31'
GROUP BY ffm.REPORTING_PERIOD

There will be 6 possible WHERE statements so I decided to use TypedQuery to simplify conditions appending.

Problem is; How can I include count into TypedQuery select and is it even possible? If not, what would be the best way except making multiple queries? If only way is multiple queries, how do I fill the dto?

This is my query where I should include count.

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<ReportListDto> cq = cb.createQuery(ReportListDto.class);
        
        Root<FiMessage> fromFIMessage = cq.from(FiMessage.class);
        Join<FiMessage, FiMessageFi> fiMessageFi = fromFIMessage.join("fintaeoi2FiMessageFis",  JoinType.INNER);
        Join<FiMessageFi, FiAccount> fiAccount = fiMessageFi.join("fintaeoi2FiAccounts", JoinType.INNER); 
 
        List<Predicate> conditions = new ArrayList<Predicate>();


`**TypedQuery<ReportListDto> query = entityManager.createQuery(
cq.multiselect(
fromFIMessage.get("reportingPeriod"), 
fromFIMessage.get("fiMessageId"), 
fiAccount.get("fiAccountId"))
.where(conditions.toArray(new Predicate[] {})));**

List<ReportListDto> result = query.getResultList();
`

result of above is 
[results without count included](https://i.stack.imgur.com/MO7Zk.png)

but i need 

[result ash it should be with applying count in typedquery](https://i.stack.imgur.com/gqnC9.png)

So reporting period is fine and I need distinct count for messageId and count for accountId.

Thank you for your answers.

CodePudding user response：

The answer is

TypedQuery<ReportListDto> query = entityManager.createQuery(cq.multiselect(fromFIMessage.get("reportingPeriod"), cb.countDistinct(fromFIMessage.get("fiMessageId")), cb.count(fiAccount.get("fiAccountId")))
.where(conditions.toArray(new Predicate[] {})));