JPQL SELECT between date statement

java sql jpa jpql
Mamadou picture Mamadou · Mar 18, 2011 · Viewed 116.3k times · Source

I would like to convert this SQL statement to a JPQL equivalent.

SELECT * FROM events
WHERE events_date BETWEEN '2011-01-01' AND '2011-03-31';

This correctly retrieves the information from the table events.

In my Events entity 

   @Column(name = "events_date")  
   @Temporal(TemporalType.DATE)  
   private Date eventsDate;

So far this is what I have but it is not working.

public List<Events> findAllEvents(Date startDate, Date endDate) {    
  List<Events> allEvents = entityManager.createQuery(
    "SELECT e FROM Events e WHERE t.eventsDate BETWEEN :startDate AND :endDate")  
  .setParameter("startDate", startDate, TemporalType.DATE)  
  .setParameter("endDate", endDate, TemporalType.DATE)  
  .getResultList();
  return allEvents ;  
}

What am I doing wrong? Thanks.

Answer

Matt Handy picture Matt Handy · Mar 18, 2011

Try this query (replace t.eventsDate with e.eventsDate):

SELECT e FROM Events e WHERE e.eventsDate BETWEEN :startDate AND :endDate

Related questions

JPA Native Query select and cast object

I have got an Object Admin which extends User. By default both Objects are in the table User_ of my Derby Database (included fields from Admin). Normally I'd select an User like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<User&…

Read More
JPA Select latest instance for each item

Let's say I have a Meeting entity. Each meeting has a single attendee and a meeting date. Within my meeting table I may have multiple meetings for each attendee, with different dates for each. I need a JPA query that …

Read More
How to call a stored procedure from Java and JPA

I am writing a simple web application to call a stored procedure and retrieve some data. Its a very simple application, which interacts with client's database. We pass employee id and company id and the stored procedure will return employee …

Read More