I use Intellij with Spring Boot. In my PSQL Database I have a column named as dob (date of birth) and I want with a @Query annotation extract the person's age from the birthdate and get all the male persons that have age over 60. I have tried to do it but with no result. Do you have any suggestions?
public interface PersonRepo extends JpaRepository<Person, Long> {
Person findByUsername(String username);
@Query("select new com.example.personservice.dto.MalesCovidOverSixty(p.gender,count(p.gender)) "
"from Person p "
"where p.gender='Male' "
"and p.covid='t' "
"and age(p.dob,current_date)>'20' "
"group by p.gender "
"order by count(p.gender) desc")
List<MalesCovidOverSixty>findMalesCovidOverSixty();
}
In Postman it gives me empty brancets []. In Person.class dob is set as LocalDate. I have a male person in my database who is over 20 so I tried this for beginning.
CodePudding user response:
Ok found the answer! I post it just in case someone else has the same problem.
"and extract(year from(age(current_date, p.dob)))>'60' "