3

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?

Improve this question
3
  • 1
    What is the purpose of having limit in delete query? Either your data match some criteria and should be deleted - or not. The usage of limit does not make sense. Point 1 is essence of pure evil, there is no need to withdraw data from database, send them over the networks in order to delete them. Commented Aug 5, 2015 at 9:59
  • I have to do it due to user requirements Commented Aug 5, 2015 at 16:49
  • If the purpose is piecewise purging, then you should use real (server side) SQL. Commented Aug 6, 2015 at 8:13

2 Answers 2

Reset to default
2

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I added up vote thanks to the ideas and I had not thought about the second one
Please note, that the second solution can/will break depending on database vendor. For instance, on MS SQL Server, on Quarkus 3.x our application throws an exception when the result list of the first query is greater then 2100: "SqlExceptionHelper] (main) The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request." That effectively makes the limit 2100 or less.
to be fair this was a specific answer for the tags listed and not vendor agnostic.
2

  1. You can Delete Object "session.delete(object)" The reason is that for deleting an object, Hibernate requires that the object is in persistent state. Thus, Hibernate first fetches the object (SELECT) and then removes it (DELETE). you have to have the object id to remove it

  2. you are using Hibernate because it doesn't depend on the Database that you are using so createQuery is a very strong feature for hibernate , in createQuery first hibernate knew Database type (from jar or your dependency)then Map your SQL statement to the best statement to be run on that Database, in session.createSQLQuery you are writing native SQL so it depend on the DBMS you are using "you lost hibernate feature "

Improve this answer

1 Comment

I upvoted but the hibernate feature is not clear in the context of my problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.