CODEWITHSUNDEEP
javajpacriteriabuilder
Alexandre DONAZZAN
Alexandre DONAZZAN

Reputation: 18

How to do addition ("+") between fields with CriteriaBuilder with JPA?

I'm trying to use criteria builder to add several field together and then apply a sql function. I try to sum it and with only two field, it is possible but when i have a third one, i cannot apply the "+" easily.

 select.add(cb.min(cb.sum(functionListToAdd.get(0), functionListToAdd.get(1))));
 cq.multiselect(select);
 em.createQuery(cq).getResultList();

This case works but i need more parameter and the sum function doesn't allowed more than 2 Expression (parameter).

Do you know how to sum field like the example below with CriteriaBuilder API ?

To illustrate what i want i did a request sql which work (i remove some code to be more readable)

SQL Example of what i want :

select
    min(**b1_0.ecpi + s1_0.ecpi + l1_0.ecpi**),
    max(b1_0.ecpi + s1_0.ecpi + l1_0.ecpi),
    cast('units' as text) 
from
    facility.facility b1_0,
    facility.cost s1_0 ,
    facility.shipping l1_0

Thanks a lot to help me because i'm locked because of a simple "+".

Upvotes: 0

Views: 119

Answers (2)

Alexandre DONAZZAN
Alexandre DONAZZAN

Reputation: 18

in order to do your advice i share a function which work for me.

You just have to put a real Expression in sumExpression as third parameter.

  Expression initExpression = functionListToAdd.remove(functionListToAdd.size()-1);
  select.add(cb.min(sumExpression(cb, functionListToAdd, initExpression)));

private Expression sumExpression(CriteriaBuilder cb, List<Expression> functionListToAdd, Expression expression) {
    if (functionListToAdd.size() <= 1) {
        return cb.sum(functionListToAdd.get(0), expression);
    } else {
        Expression expressionTemp = functionListToAdd.remove(functionListToAdd.size()-1);
        return cb.sum(expressionTemp, sumExpression(cb, functionListToAdd, expression));
    }
}

Upvotes: 0

gagarwa
gagarwa

Reputation: 1502

You can always try compounding statements:

cb.sum(functionListToAdd.get(0), cb.sum(functionListToAdd.get(1), functionListToAdd.get(2)))

Upvotes: 0

Related Questions