Spring data jpa, native query Hibernate exception

Question

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.

infiniteRefactor · Accepted Answer · 2017-04-21 15:17:38Z

7

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();

answered Apr 21, 2017 at 15:17

infiniteRefactor

2,14017 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khushi Over a year ago

@infiniteRefactor...thanks for your answer, i think u r right and i will accept ur answer after checking ur answer on my sys

Community · Accepted Answer · 2017-05-23 12:34:12Z

1

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

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Apr 21, 2017 at 14:55

Azzabi Haythem

2,4237 gold badges31 silver badges32 bronze badges

1 Comment

Khushi Over a year ago

Hello Azzabi, I am able to delete using deleteByCreationDateBefore , thanks

Collectives™ on Stack Overflow

Spring data jpa, native query Hibernate exception

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related