Search code examples
javasqloraclehibernatehql

Limit delete query with hibernate

I want to create a new functionality to delete rows from a table programatically with a limit of rows. That could be thousands of elements to be deleted. The database used is Oracle.

The main problem is that HQL does not support something like a limit or rownum for deletes. We only have setMaxResults for select.

The solutions I have thought about are:

  1. To use a select and then, looping over the list removing with delete every single element from the list.
  2. To use session.createSQLQueryto be able to use limit in the query.

Point 1: I want to avoid it, as I don't like having to bring the elements to memory to delete them afterwards, as the elements can be any number (for example 1000000), I have no restrictions in terms of numbers of elements. Is there anything I am missing and I could help me for this solution?

Point 2: I don't know what is the performance difference between session.createSQLQuery and session.createQuery, is there any invonvenience using session.createSQLQuery?

Solution

  • Other options

    You can try to iterate over/load each object and delete it. (seems ugly!!)

    OR

    You can write two separate HQL queries where the results of the first query are fed into another query (which does the actual delete).