How to query by age on spring boot specification-CodePudding

@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.

CodePudding user response：

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?