Java Criteria query for amount range regardless of amount sign

Question

I want to perform search on db data based on amount range , there are some amount items which are negative as well . But I regardless of any sign I want to apply my criteria on actual amount using javax.persistence.criteria .

E.g. query input : between 1 and 100

Expected output : -99 ,-10 , 33 ,90 100

Can anyone guide me on this ?

Answer 1

Sql Query :

select * from table where abs(cast(amount as decimal(10,2))) between 1 and 700

Criteria Query :

final List<Predicate> predicates = new ArrayList<>();
Predicate matchPrice = cb.between(cb.abs(trxRoot.get("amount").as(BigDecimal.class)), minPrice, toAmount);
predicates.add(matchPrice);
query.where(predicates.toArray(new Predicate[] {}));