CODEWITHSUNDEEP
javahibernatejpajpql
Evg
Evg

Reputation: 160

Jpql and hibernate age difference

I want to write a method that searches for people at a given age with an interval of 5 years. That is, if the age is set to 30 years, then the method should bring all people from 25 to 35 years old.

  @Entity
@NamedQueries({
        @NamedQuery(name = "Person.findByNameUndSurname", query = "SELECT c FROM Person c WHERE c.name = ?1 and c.surname = ?1"),
        @NamedQuery(name = "Person.findByAge", query = "SELECT c FROM Person c WHERE c.age = ?1")
})
    Person{
    name,age und get,set
    }

public class PersonMenager extends AbstractRepository {

---
    public List findPersonByAge(int age){
            return entityManager.createQuery("SELECT c from Person c WHERE c.age = ?1 and c.age between age-5 and  age+5").getResultList();
        }
}

I don’t understand how I can use the age input

Upvotes: 0

Views: 275

Answers (2)

areus
areus

Reputation: 2947

The query would be:

SELECT c from Person c WHERE c.age between :age -5 and  :age + 5

Then you need to call query.setParameter("age", age).

For a more flexible query if would use:

SELECT c from Person c WHERE c.age between :startAge and  :endAge

And to set de parameters:

query.setParameter("startAge", age - 5);
query.setParameter("endAge", age + 5);

Upvotes: 1

Alan Hay
Alan Hay

Reputation: 23246

Pass 2 parameters - lower and upper - otherwise you are limited to only ever being able to query for +/- 5 years. What if you wanted +/- 3?

public class PersonMenager extends AbstractRepository {

    public List findPersonByAge(int lowerBound, int upperBound){
            return entityManager.createQuery("SELECT c from Person c 
                WHERE c.age between ?1 and ?2).getResultList();
        }
}

Storing age is probably also not a very good idea as it is not a fixed value (unlike say date of birth).

Upvotes: 1

Related Questions