1

Hi there I am new to javax.persistance. The main problem is that I want to delete old (not the last 10000) records in my database (ordered by date desc). I am not able to do a subquery in my sql-statement I would usually expect:

Logging is an object (log) with some information and the occuredAt field is the date (date+time) when it was written to the database

Database:db2 if it is important

SQL:

DELETE FROM Logging WHERE id NOT IN (SELECT id FROM Logging ORDER BY occuredAt DESC LIMIT 10000)

And this is my code I am trying to execute.

EntityManager em = getEntityManager();
String sql = String.format("DELETE FROM %1$s WHERE id NOT IN (SELECT id FROM %1$s ORDER BY occuredAt DESC LIMIT %2$s)",Logging.class.getName(), 10000);                   
Query qry = em.createQuery(sql);
return qry.executeUpdate();

But this is throwing me an ArgumentException.

Can anybody tell me what I am doing wrong?

Pwnstar
  • 2,333
  • 2
  • 29
  • 52
  • Take a look at this. Looks like that you can't use limit within a subquery with hql. http://stackoverflow.com/questions/2738880/how-to-set-a-limit-to-inner-query-in-hibernate – shalama Dec 22 '16 at 14:55
  • JPA queries are expressed in [Java Persistence Query Language (JPQL)](http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html), not SQL, and certainly not in the specific SQL dialect of the underlying DB. JPQL and SQL are intentionally similar, but not identical. At minimum, your query is problematic in that JPQL does not have a `LIMIT` clause. – John Bollinger Dec 22 '16 at 14:56
  • This isn't valid DB2 SQL syntax; what _is_ valid depends on the DB2 version and platform. – mustaccio Dec 22 '16 at 15:33
  • Can you post the stacktrace? – ujulu Dec 22 '16 at 19:00

2 Answers2

1

You shall do em.createNativeQuery for SQL

em.createQuery is for JP QL (Java Persistence Query Language), not for SQL

fg78nc
  • 4,774
  • 3
  • 19
  • 32
1

modify your query like this

DELETE FROM Logging WHERE id NOT IN (SELECT id FROM Logging ORDER BY occuredAt DESC fetch first 10000 rows only)
Esperento57
  • 16,521
  • 3
  • 39
  • 45