I have a query like below
select field1,filed2,count(*),
sum(case isNew WHEN 0 THEN 0 ELSE 1 END) as new_count1,
sum(case source WHEN 'SomeValue' THEN 0 ELSE 1 END) as new_count2
from TABLE
where status='processed'
and filed1='filed1Value'
and filed2='field2Value';
The output of this is a single row containing all values I require. I want to achieve the same using queryDSL. I am using the CaseBuilder in the QueryDSL but unable to apply sum on top of it.. Does QueryDsl support that? Any one solved this before??
Expression<Integer> sourceCases = qTable.source.
when(matchedValue1).then(new Integer(1)).
otherwise(new Integer(0));
Expression<Integer> newAccountCases = qTable.isNew.
when(matchedValue2).then(new Integer(1)).
otherwise(new Integer(0));
return queryDslJdbcTemplate.queryForObject(sqlQuery,
new Mapping(qTable.filed1,
qTable.filed2,
qTable.id.count(),
####SUM( (sourceCases) )###,
####SUM( (newAccountCases) )###);
The base class Expression does not allow you to apply aggregations. If you notice, your otherwise clause actually returns NumberExpression. All you need to do is use that type instead of Expression. So your code could become:
NumberExpression<Integer> sourceCases = qTable.source.
when(matchedValue1).then(new Integer(1)).
otherwise(new Integer(0));
NumberExpression<Integer> newAccountCases = qTable.isNew.
when(matchedValue2).then(new Integer(1)).
otherwise(new Integer(0));
return queryDslJdbcTemplate.queryForObject(sqlQuery,
new Mapping(qTable.filed1,
qTable.filed2,
qTable.id.count(),
sourceCases.sum(),
newAccountCases.sum());
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With