@Entity
public class Users {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "full_name", nullable = false, length = 50)
private String fullName;
@Column(name = "current_location", nullable = false)
private String currentLocation;
@Column(name = "gender", nullable = false, length = 6)
private String gender;
@Column(name = "birth_date", nullable = false)
private Timestamp birthDate;
}
I user the following to filter user by gender
public class SearchSpecification implements Specification<Users> {
private List<SearchCriteria> list;
public SearchSpecification() {
this.list = new ArrayList<>();
}
public void add(SearchCriteria criteria) {
list.add(criteria);
}
@Override
public Predicate toPredicate(Root<UserActualDatum> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
predicates.add(builder.like(root.<String>get(criteria.getKey()), (String) criteria.getValue()));
return builder.and(predicates.toArray(new Predicate[0]));
}
}
But I want to filter Users by age, I have birthDate column which store date of birth of the user. It is possible to calculate age in postgres database using AGE() function but when I come to Spring Specification Query I can not calculate age from date of birth and filter by calculate age.
I add age column , we can get age with out any calculation
@Entity
public class Users {
...
@Formula("date_part('year',AGE(current_date,birth_date))")
@Column(name = "age")
private Integer age;
}
CREATE FUNCTION ON POSTGRES WHCIH CALCULATE AGE
CREATE OR REPLACE FUNCTION get_age( birthday timestamp )
RETURNS integer
AS $CODE$
BEGIN
RETURN date_part('year',age(birthday));
END
$CODE$
LANGUAGE plpgsql IMMUTABLE;
AND I MODIFY THE TABLE BY ADDING age column which will be calculated from birth_date
CREATE TABLE Users (
....
birth_date timestamp not null,
age text GENERATED ALWAYS AS (get_age(birth_date)) stored
);
Now I can filter by age , age as an integer value which derived from birth_date
When we come on Spring boot specification query now this work
public class SearchSpecification implements Specification<Users> {
private List<SearchCriteria> list;
public SearchSpecification() {
this.list = new ArrayList<>();
}
public void add(SearchCriteria criteria) {
list.add(criteria);
}
@Override
public Predicate toPredicate(Root<UserActualDatum> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
predicates.add(builder.lessThanOrEqualTo(
root.<Integer>get(criteria.getKey()), (Integer) criteria.getValue()));
return builder.and(predicates.toArray(new Predicate[0]));
}
}
Resource : How to compute a derived age attribute in postgresql?