I am having a trouble in creating a query with criteria builder. I have an Employee
table with a column SALARY
and salary is being kept in this format: value + currency (example: "1000 EUR"). I am not using @Embeddable
class for this. Instead I'm using hibernate converter to store and read salaries, this is my class for money:
public class Money implements Comparable<Money> {
private BigDecimal amount;
private Currency currency;
@Override
public String toString() {
return getAmount() + " " + getCurrency();
}
public static Money getMoneyFromString(String s) {
String[] strings = s.split(" ");
return new Money(new BigDecimal(strings[0]),Currency.getInstance(strings[1]));
}
@Override
public int compareTo(Money o) {
if (o.getCurrency() == null || !o.getCurrency().equals(this.currency))
throw new IllegalArgumentException();
return this.amount.compareTo(o.getAmount());
}
}
And here is how I am keeping the field of this class in my entity class
@NotNull
@Convert(
converter = MoneyConverter.class
)
@Column(name = "SALARY", length = 63)
private Money salary;
The problem is that I have not idea how can I build a query by using criteria to find employees who's salaries are getting in the range, for example:
I tried like this:
public static Specification<Employee> isSalaryBetween(Money salaryStart, Money salaryEnd) {
return (root, query, criteriaBuilder) -> {
Predicate salaryBetween = criteriaBuilder.between(
root.get(Employee_.salary), salaryStart, salaryEnd);
return criteriaBuilder.and(salaryBetween);
};
}
But this is not returning correct values. So I also tried something like this:
Predicate predicate = builder.between(
root.get("salary").get("amount"),
minSalary.getAmount(),
maxSalary.getAmount()
);
But this trigger the exception:
org.springframework.dao.InvalidDataAccessApiUsageException: Basic paths cannot
be dereferenced
I was also trying to use some kind of functions, but there were no go.
So what other possibilities to find employee with salaries in some range? Is the only way to achieve this - changing the DB schema?
Fixed the problem. Thanks to Alex Chernyshev for explaining me how db stores BigDecimal. To fix the problem I needed to change my Money class, now it looks like this:
@AllArgsConstructor
@NoArgsConstructor
@Embeddable
@Getter
@Setter
@ToString
public class Money {
private Double amount;
}
I removed field for currency because I don't really think that its needed in my project and replaced BigDecimal with Double (for some other reasons I picked non-primitive type, these reasons are not connected to the issue). Also I don't think that having a field for currency would be a problem, because my query looks like this now:
public static Specification<Employee> isSalaryBetween(double salaryStart, double salaryEnd) {
return (root, query, criteriaBuilder) -> {
Expression<Double> salaryAmount = root.get(Employee_.salary).get("amount");
Predicate isSalaryBetween = criteriaBuilder.between(salaryAmount, salaryStart, salaryEnd);
return criteriaBuilder.and(isSalaryBetween);
};
}
I also thought that I can simply use salary field from my metamodel like this:
Predicate isSalaryBetween = criteriaBuilder.between(root.get(Employee_.salary),
salaryStart, salaryEnd);
But it will not work, even if I change method arguments to Money. Criteria doesn't allow you to use custom types for queries, so that's why I needed to get the amount field from the Money class.
Unfortunately I didn't find out yet how to use type-safety field of amount but yet it is working.
I hope this can help someone who will face with some problems like this