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?
CodePudding user response:
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