CODEWITHSUNDEEP
javaspring-bootspring-data-jpa
Eyasu Tewodros
Eyasu Tewodros

Reputation: 275

How to query by age on spring boot specification

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

Upvotes: 2

Views: 2125

Answers (3)

bangeboss
bangeboss

Reputation: 121

If you are using PostgreSQL as your database, then you can use the age function in the criteria builder.

public class SearchSpecification implements Specification<Users> {
    
    private Integer ageLimit;

    
    @Override
    public Predicate toPredicate(Root<UserActualDatum> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        final ArrayList<Predicate> predicates = new ArrayList<>();
        if(ageLimit!=null){
            predicates.add(
                    builder.lessThanOrEqualTo(
                            builder.function("date_part", Integer.class, builder.literal("YEAR"),
                                    builder.function("age", String.class, builder.currentTimestamp(), root.get("birthDate"))
                            ), ageLimit
                    )
            );
        }
        return builder.and(predicates.toArray(new Predicate[predicates.size()]));
    }
}

Notice that we have used the date_part function to extract the number of years (as an integer) returned from the age function before applying the lessThanOrEqualTo method.

Upvotes: 0

Mohammad Hammadi
Mohammad Hammadi

Reputation: 793

You can use CriteriaBuilder functions to achieve this easily as follow:

Full code would be like:

return (Root<Vessel> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> {
        return criteriaBuilder.equal(criteriaBuilder.diff(
                                        criteriaBuilder.function("date_part", Integer.class, criteriaBuilder.literal("YEAR"), criteriaBuilder.currentTime()),
                                        criteriaBuilder.function("date_part", Integer.class, criteriaBuilder.literal("YEAR"), root.get("birthDate"))
                                     ),
                               searchedValue
        );
    };

In order to get the current year from Postgres (Expression 1):

criteriaBuilder.function("date_part", Integer.class, criteriaBuilder.literal("YEAR"), criteriaBuilder.currentTime());
// Result example: 2023

To get birthdate Year (Expression 2):

criteriaBuilder.function("date_part", Integer.class, criteriaBuilder.literal("YEAR"), root.get("birthDate"))
// Result example: 2000

Then get the age using diff:

criteriaBuilder.diff(Expression 1, Expression 2)
// example: criteriaBuilder.diff(2023, 2000)

Here diff() gets the difference between the two years: 2023 -2000 = 23

Then Apply the equal()

criteriaBuilder.equal(calculatedAge, searchedAge)

Upvotes: 0

Eyasu Tewodros
Eyasu Tewodros

Reputation: 275

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?

Upvotes: 1

Related Questions