Prune table to 500 records in JPQL

Question

I work on some kind of cache and from time to time, we need to prune the table to 500 records, based on a last_access_date (only keep the 500 recently accessed rows).

With "plain" SQL, this could be done with:

DELETE FROM records WHERE id not in 
    (SELECT id FROM records ORDER BY last_access_date DESC LIMIT 500)

Now as there is no LIMIT or something like ROWNUM in JPQL, the only solution I found was in native SQL, which is suboptimal, because we run on multiple DBMS (at least Oracle and MSSQL).

Also, setMaxResults() (JPQLs version of LIMIT) doesn't seem valid for DELETE statements.

Is there really no way to to this with JPQL?

Answer 1

You could do this:

String sql = "SELECT x.id FROM records x ORDER BY x.last_access_date DESC";
TypedQuery<Long> query = em.createQuery(sql, Long.class);

List<Long> ids = query.setMaxResults(500).getResultList();

String delete = "DELETE FROM records x where x.id not in :ids";
em.createQuery(delete).setParameter("ids", ids).executeUpdate();

I don't recall the exact syntax for the delete query, so you may have to put the :ids between parenthesis, like:

String delete = "DELETE FROM records x where x.id not in (:ids)";

---

Edit: dkb proposed a faster solution on the comments (depends on unique dates for perfect accuracy on the amount of remaining rows):

String sql = "SELECT x.last_access_date FROM records x ORDER BY x.last_access_date DESC";

//If you're not using calendar, change to your specific date class
TypedQuery<Calendar> query = em.createQuery(sql, Calendar.class);

Calendar lastDate = query.setFirstResult(499).setMaxResults(1).getSingleResult();

String delete = "DELETE FROM records x where x.last_access_date < :lastDate";
em.createQuery(delete).setParameter("lastDate", lastDate, TemporalType.DATE).executeUpdate();

Answer 2

For performance reasons, it is imperative not to do the additional client round trip just to load 500 ID values and then send them to the server again. Instead, I would suggest one of two approaches:

Use vendor specific SQL

You're currently supporting only 2 RDBMS. It should be manageable to write the two separate SQL statements. In this case, since you're using Oracle and SQL Server only, you can pull this off with standard SQL, in fact:

DELETE FROM records 
WHERE id NOT IN ( 
  SELECT id 
  FROM records 
  ORDER BY last_access_date DESC 
  OFFSET 0 ROWS -- SQL Server needs this
  FETCH FIRST 500 ROWS ONLY
)

If you don't do this too frequently and can live with the temporary inconsistency, you might even be able to implement a much faster solution:

Oracle

CREATE TABLE temp AS 
SELECT * 
FROM records 
ORDER BY last_access_date DESC
FETCH FIRST 500 ROWS ONLY;

TRUNCATE TABLE records;

INSERT INTO records 
SELECT * FROM temp;

DROP TABLE temp;

SQL Server

SELECT TOP 500 *
INTO temp
FROM records
ORDER BY last_access_date DESC;

TRUNCATE TABLE records;

INSERT INTO records
SELECT * FROM temp;

DROP TABLE temp;

Use a SQL builder

For more complex vendor agnostic SQL, you might want to look into using a SQL builder like jOOQ. Other alternatives may exist.

Disclaimer: I work for the vendor.