SQLGrammarException: could not execute statement while trying to delete from db

Question

I'm trying to solve a problem with statement deletion query. Now the implementation looks like this.

 @Transactional
public void deleteStatements(LocalDate expiryDate) {
    int deletedStatements = statementRepository.deleteByIdCreatedDateBefore(expiryDate);
    logger.info("Deleted {} statements.", deletedStatements);
}


@Query(value = "WITH deleted AS (DELETE FROM generated_statements WHERE created_date < :expiryDate RETURNING id) " +
        "SELECT count(*) FROM deleted;", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

Now I'm getting this error: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

I tried adding @Modifying annotation, then I tried removing it, tried with various combinations of @Transactional and @Modifying, still got various errors regarding this deletion like: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet or Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query Now I'm really not sure what is the problem here.

Why do you need the CTE at all? JDBC will automatically return the number of deleted rows and I would expect that an obfuscation layer like JPA can actually return that information. — user330315
– user330315, Commented Apr 6, 2020 at 9:49
@a_horse_with_no_name sorry, didn't quite understand what you meant. — developer1
– developer1, Commented Apr 6, 2020 at 9:53

Marco Behler · Accepted Answer · 2020-04-06 10:14:25Z

1

Replace your delete query with this

@Modifying
@Query(value = "DELETE FROM generated_statements WHERE created_date < :expiryDate", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

or turn it directly into a JPQL/HQL query. No need for a nativeQuery. Example:

@Modifying
@Query(value = "DELETE FROM GeneratedStatement g WHERE g.createDate < :expiryDate")
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

By specifying an int return value, you will automatically get the number of deleted rows.

edited Apr 6, 2020 at 10:14

answered Apr 6, 2020 at 9:57

Marco Behler

3,7442 gold badges23 silver badges22 bronze badges

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

9 Comments

developer1 Over a year ago

And what about annotations, which one should I use, @Modifying, @Transational?

Marco Behler Over a year ago

Yes, you'll need to add \@Modifying on the queries and I updated my answer accordingly. But: You also need to execute your methods inside a \@Transaction. Typically your repository would be called from a service method, and you'd make that \@Transactional, you can however also make your repository methods \@Transactional, too.

developer1 Over a year ago

And with @Modifying wouldn't I get a

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

exception because @Modifying can't return ResultSet? Because I got this error before because of this.

developer1 Over a year ago

So I could just leave the @Transactional annotation on the method that calls the statementRepository method, as it is in my question, yes?

Marco Behler Over a year ago

As for your second question (\@transactional), yes. For the first one: Try having \@Query, \@Modify and try it without a native query for now. It should actually work

|

Artsiom · Accepted Answer · 2020-04-06 10:17:29Z

0

Considering all this complex logic is to get number of deleted rows you can try just:

@Modifying
@Query("DELETE FROM generated_statements WHERE created_date < :expiryDate")
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

or just using keywords(if your entity has createdDate attribute)

int deleteByCreatedDateBefore(LocalDate expiryDate);

edited Apr 6, 2020 at 10:17

answered Apr 6, 2020 at 10:09

Artsiom

1267 bronze badges

Comments

damjad · Accepted Answer · 2020-05-03 22:32:42Z

0

@Modifying
@Query(value = "DELETE FROM generated_statements WHERE created_date < ?1", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

edited May 3, 2020 at 22:32

damjad

1,2801 gold badge15 silver badges26 bronze badges

answered May 3, 2020 at 11:21

jas Wang

1

1 Comment

damjad Over a year ago

Try adding more details, please.

Collectives™ on Stack Overflow

SQLGrammarException: could not execute statement while trying to delete from db

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related