java - 修剪表到JPQL中的500条记录

我正在处理某种类型的缓存，有时我们需要根据last_access_date将表修剪为500条记录(仅保留500条最近访问的行)。

使用“普通” SQL，可以使用以下方法完成:

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

现在，由于JPQL中没有LIMIT或类似ROWNUM的东西，所以我发现的唯一解决方案是在 native SQL中，这是次优的，因为我们在多个DBMS(至少是Oracle和MSSQL)上运行。

另外， setMaxResults() (JPQLs版本的LIMIT)对于DELETE语句似乎无效。

使用JPQL真的没有办法做到这一点吗？

最佳答案

您可以这样做:

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

我不记得删除查询的确切语法，因此您可能必须将:ids放在括号之间，例如:

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

编辑: dkb提出了一种关于注释的更快的解决方案(取决于唯一的日期以确保剩余行数的完美准确性):

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