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.
A subselect is a query embedded into another query. It's a powerful feature you probably know from SQL. Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause. Subqueries can return one or multiple records and can use the aliases defined in the outer query.
Query createQuery(String name) - The createQuery() method of EntityManager interface is used to create an instance of Query interface for executing JPQL statement.
The @Query annotation gives you full flexibility over the executed statement, and your method name doesn't need to follow any conventions. The only thing you need to do is to define a method in your repository interface, annotate it with @Query, and provide the statement that you want to execute.
As stated in the comments, JPQL does not support the LIMIT keyword. You can achieve that using the setMaxResults but if what you want is just a single item, then use the getSingleResult - it throws an exception if no item is found.
Try this query (replace t.eventsDate
with e.eventsDate
):
SELECT e FROM Events e WHERE e.eventsDate BETWEEN :startDate AND :endDate
public List<Student> findStudentByReports(Date startDate, Date endDate) {
System.out.println("call findStudentMethd******************with this pattern"
+ startDate
+ endDate
+ "*********************************************");
return em
.createQuery(
"' select attendence from Attendence attendence where attendence.admissionDate BETWEEN : startDate '' AND endDate ''"
+ "'")
.setParameter("startDate", startDate, TemporalType.DATE)
.setParameter("endDate", endDate, TemporalType.DATE)
.getResultList();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With