Spring JPA @Query JPQL fails with "Unexpected token: DATE"

user_3453898 0
Viewed 761

I am using Spring Data JPA and I would like to query my results and filter them. In SQL, I would write my query (against DB2 database) like so:

SELECT * FROM CAR 
WHERE ACCIDENT_YEAR IS NULL 
  OR BUY_YEAR >= CURRENT_DATE 
ORDER BY CAR_NUMBER

With Spring JPA, I am trying to do same using @Query annotation and JPQL like so:

@Repository
public interface CarRepository extends JpaRepository<CarEntity, Integer> {
    
    @Query("SELECT c FROM CAR c WHERE c.EXPIRY_DATE IS NULL OR c.EXPIRY_DATE >= CURRENT DATE")
    List<CarEntity> findAllNonExpiredCars(Sort sort);
}

, and I could then call this method like:

carRepository.findAllNonExpiredCars(Sort.by("CAR_NUMBER"));

But, when I do Maven > Install, I get following error:

NoViableAltException: unexpected token: DATE

, and

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: DATE near line 1, column 84 [SELECT c FROM CAR c WHERE c.EXPIRY_DATE IS NULL OR c.EXPIRY_DATE >= CURRENT DATE]

How do I write above query?

spring-data-jpa spring-boot java
1 Answers

If you want to define your query using @Query annotation, you can use either JPQL-query (the query definition uses it by default) or native SQL (using nativeQuery = true). SQL works with relational database tables, records and fields, whereas JPQL works with Java classes and objects.

In your case, you mixed both options. You can do it like this:

JPQL:

public interface CarRepository extends JpaRepository<CarEntity, Integer> {
    
    @Query("SELECT c FROM CarEntity c WHERE c.expiryDate IS NULL OR c.expiryDate >= CURRENT_DATE")
    List<CarEntity> findAllNonExpiredCars(Sort sort);
}

Native SQL:

public interface CarRepository extends JpaRepository<CarEntity, Integer> {
    
@Query("SELECT * FROM schema_name.CAR WHERE EXPIRY_DATE IS NULL OR EXPIRY_DATE >= CURRENT_DATE ORDER BY some_field_name",
        nativeQuery = true)
List<CarEntity> findAllNonExpiredCars();
}

Take a look at these links:

Spring Data JPA - Reference Documentation. Query Methods

Spring Data JPA @Query. Baeldung

Types of JPA Queries. Baeldung

user_150000970
Related