CODEWITHSUNDEEP
javajpa
user2061913
user2061913

Reputation: 920

Range query in jpa

Hi guys im trying to do a range query in jpa where i can search a DOB between a set range

i get the user to enter the details on a calander and then pass it to this code

public List<User> dobRangeSearch(Date dateOfBirthSearch1, Date dateOfBirthSearch2) {
    Query q = em.createQuery("SELECT u FROM USERS U WHERE u.DATEOFBIRTH BETWEEN "+  dateOfBirthSearch1 + " and " + dateOfBirthSearch2);
    return q.getResultList();

}

but i get the following error:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [SELECT u FROM USERS U WHERE u.DATEOFBIRTH BETWEEN Sun Dec 01 00:00:00 GMT 2013 and Wed Dec 04 00:00:00 GMT 2013]. 
[28, 95] The expression is not a valid conditional expression.

what is the correct way to run this search ?

thanks

EDIT

even though i had created the values using the calander and date functions the records that get put into the database is in the format 

'2013-12-02'

so how can i strip the data from the input to just this ?

Upvotes: 1

Views: 3605

Answers (1)

Lukas Eichler
Lukas Eichler

Reputation: 5913

You putting the Date.toString() output inside your query so either use a DateFormater to get your Date String in the right format your use parameters. 

public List<User> dobRangeSearch(Date dateOfBirthSearch1, Date dateOfBirthSearch2) {
    Query q = em.createQuery("SELECT u FROM USERS U WHERE u.DATEOFBIRTH BETWEEN :date1 and :date2)
      .setParameter("date1", dateOfBirthSearch1)
      .setParameter("date2", dateOfBirthSearch2;
    return q.getResultList();

}

Upvotes: 1

Related Questions