I need an total budget value. I can getting the answer from sql query but the problem is how to convert sql query to criteriaBuilder in hibernate.Here below the sql query:
SELECT SUM(ab.daily_budget) as todayBudgetTar FROM api_budget ab INNER JOIN api_ad_groups ad on ad.ad_group_id= ab.adgroup_id INNER JOIN api_campaigns c on c.campaign_id =ad.campaign_id INNER JOIN api_user_account ac on ac.user_id=ad.user_id WHERE ad.user_id = 234 AND ad.status=0 AND c.status=1 GROUP by "todayBudgetTar"
If someone knows plz help me.
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object> query = builder.createQuery(Object.class);
Root<Budget> budget = query.from(Budget.class);
Join adsetJoin = budget.join("adsetId");
Join campaignJoin = adsetJoin.join("campaign");
query.multiselect(builder.sum(budget.get("dailyBudget")));
List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(adsetJoin.get("status"), 0));
predicates.add(builder.equal(adsetJoin.get("userId"), userId));
predicates.add(builder.equal(campaignJoin.get("campaignStatus"), CAMPAIGNSTATUS.RUNNING));
if ((dashboardRequest.getStartDate() != null) && (dashboardRequest.getEndDate() != null)) {
Date fromDate = new Date(dashboardRequest.getStartDate());
Date toDate = new Date(dashboardRequest.getEndDate());
predicates.add(builder.between(adsetJoin.get("createdDate"), fromDate, toDate));
}
if (!predicates.isEmpty()) {
Predicate[] pr = new Predicate[predicates.size()];
pr = predicates.toArray(pr);
query.where(pr);
}
query.groupBy(budget.get("dailyBudget"));
List<Object> resultList = session.createQuery(query).getResultList();
List<Long> resultList2=resultList.stream().map(v ->((Number)v).longValue()).collect(Collectors.toList());
return resultList2;