CODEWITHSUNDEEP
sqlspring-data-jpa
Khushi
Khushi

Reputation: 333

Spring data jpa, native query Hibernate exception

I want to delete records which are one day older , I am using SQL query in Spring data JPA as below:

@Query(value = "delete from tableName data where data.CREATION_DATE < TRUNC(SYSDATE) - 1", nativeQuery = true)
    void deleteRecordsOlderThanYesterday();

I am getting exception as:

Caused by: org.springframework.orm.jpa.JpaSystemException: Could not extract result set metadata Caused by: org.hibernate.HibernateException: Could not extract result set metadata

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

What is wrong with above code or is there any other way to do it in HQL?

Please let me know.

Upvotes: 3

Views: 1916

Answers (2)

infiniteRefactor
infiniteRefactor

Reputation: 2120

Whenever you are defining queries from SQL Statements (UPDATE, DELETE) You need to use @Modifying annotation (from org.springframework.data.jpa.repository). Try this:

@Modifying
@Query(value = "delete from tableName data where data.CREATION_DATE < TRUNC(SYSDATE) - 1", 
        nativeQuery = true)
void deleteRecordsOlderThanYesterday();

Upvotes: 7

Azzabi Haythem
Azzabi Haythem

Reputation: 2413

you'd better use JpaRepository and use findByStartDateBefore and you dont need to use @Query

 public List<yourResult> findByStartDateBefore(Date date);

for more information you can see spring doc http://docs.spring.io/spring-data/jpa/docs/1.3.4.RELEASE/reference/html/jpa.repositories.html

and this question Object Recovery via a date with spring data

Upvotes: 1

Related Questions