lbat lbat - 3 months ago 24
SQL Question

querydsl - list condition

that's my code :

final JPAQuery query = new JPAQuery(entityManager);
final List<Tuple> myList = query
.from(QPerson.person)
.groupBy(QPerson.person.name)
.where(myBooleanExpression) //myBooleanExpression declared at the top of code
.list(QPerson.person.name,
QPerson.person.cash.sum());


What i want to do is to list :


  • name (without condition)

  • sum(cash) ONLY IF (condition), ELSE (..)



Any suggestion on how to do it?

Answer

You can use an SQL CASE in your case, like:

final JPAQuery query = new JPAQuery(entityManager);
final List<Tuple> myList = query
    .from(QPerson.person)
    .groupBy(QPerson.person.name)
    .where(myBooleanExpression)
    .list(QPerson.person.name, 
          new CaseBuilder()
              .when(yourOtherBooleanExpression).then(QPerson.person.cash.sum())
              .otherwise(yourOtherNumericExpressionOrLiteral));
  • yourOtherBooleanExpression should be a Predicate (like BooleanExpression)
  • yourOtherNumericExpressionOrLiteral should be a NumberExpression<T> or T itself, where T is the base type of QPerson.person.cash.