'How to write a JPA query to find objects between two dates?

Here's my query

@Query(value = " SELECT * FROM account.statement where `date` between ?1 and ?2")
List<Statement> findAllByDate(String startDate, String endDate);

And this is the error message I get

Caused by: org.hibernate.QueryException: unexpected char: '`' [ SELECT * FROM account.statement where `date` between ?1 and ?2]

The date is the column name. I want to retrieve a list of Statement objects between two dates. The dates in this case are LocalDate objects, like 2000-10-10. I tried using String and a LocalDate type in the parameters, still doesn't work.

I've searched everywhere, on stack overflow and baeldung. I am stuck

javasqlspringjpa


Solution 1:[1]

List<Statement> findByDateBetween(Date start, Date end);

Solution 2:[2]

Turns out this was the correct implementation

@Query(value = "SELECT * FROM account.statement WHERE date > ?1 AND date <= ?2",
nativeQuery = true)
List<Statement> findAllByDate(LocalDate startDate, LocalDate endDate);

Thank you my guy Thomas

Solution 3:[3]

You should be binding LocalDate, not String, to the placeholders:

@Query(value = " SELECT * FROM account.statement WHERE date BETWEEN ?1 and ?2")
List<Statement> findAllByDate(LocalDate startDate, LocalDate endDate);

Solution 4:[4]

@Query("select a from Statement a where a.startDate >= :startDate and a.endDate <= :enddate")
List<Statement> findAllByDate(
@Param("startDate") Date startDate,@Param("endDate") Date endDate);

Solution 5:[5]

while using @Query might solve the problem, but you can also use JpaRepository in this case

  1. First of all you need to add JpaRepository to your repository interface :

    extends JpaRepository<Statement, Long>

  2. use this line and replcae Date with the date column name and use the appropriate type of date:

    List findByDateBetween(LocalDateTime to, LocalDateTime from);

exemple :

    @Repository 
public interface StatementRepository  extends JpaRepository<Statement, Long> {
     
     //where x.startDate between ?1 and ?2
     List<Statement> findByDateBetween(LocalDate to,LocalDate from);
     
     }

for more information you can see the doc

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MevlütÖzdemir
Solution 2 meniman98
Solution 3 Tim Biegeleisen
Solution 4 Pawan.Java
Solution 5

Related Questions