CODEWITHSUNDEEP
javajpajunith2
u4963840
u4963840

Reputation: 173

JPA delete older records than x days and write tests for it with H2

I want to delete data that is older than x days from history table. Each row has a date field. So far I have only managed to do it with native query.

public interface HistoryItemRepository extends PagingAndSortingRepository<HistoryItem, Long> {
  @Modifying
  @Query(
    nativeQuery = true,
    value = "DELETE FROM history_item hi WHERE trunc(hi.timestamp AT TIME ZONE 'EUROPE/HELSINKI') <= trunc(current_timestamp AT TIME ZONE 'EUROPE/HELSINKI') - 7"
  )
  Integer removeOldHistoryItems();
}

The application itself runs on Oracle DB and there this method works fine. The tests however run H2 database and therefore I get org.h2.jdbc.JdbcSQLException Syntax error in SQL statement "DELETE FROM HISTORY_ITEM HI WHERE TRUNC(HI.TIMESTAMP AT[*] TIME ZONE 'EUROPE/HELSINKI') <= TRUNC(CURRENT_TIMESTAMP AT TIME ZONE 'EUROPE/HELSINKI') - 7 "; expected "(, ., [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement: when tests run code that calls this method.

What can I do to be able to test it? Can this query be converted to something that does not require native query?

Upvotes: 0

Views: 622

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

timestamp AT TIME ZONE 'time zone name' is supported since old unsupported H2 1.4.200, it looks like you use some older version that needs to be upgraded.

Please note that IANA (Olson) time zone names are case-sensitive in H2, you need to specify it as 'Europe/Helsinki', otherwise you'll get an unknown time-zone ID exception.

Upvotes: 1

Related Questions