This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Let's say I have 1000 records in the database already, with IDs automatically generated by Hibernate incrementally when the objects being persisted into the database. However, there is no guarantee that the first record has an ID=1, it could be any numeric value.
Now for example if I want to delete the first 400 records, how should I address this operation in Hibernate in an effective fashion?
Well here is the thing, you don't know if the 400th record id is 400 or 546 or 426 or 476, because they might have skipped to. So you probably will have to find out if there is a "TOP" type query in the database that you are using, or you are going to have to do some kind of other query or looping through the record to figure out what the ID is for the 400th record.
The object model has a constraint which is an individual object(record) deletion is not allowed and supported. Hence the circumstance you described will never appear unless Hibernate does not assign ID incrementally to records.
Furthermore , I think looping through all the records is a very expensive solution in the database. I would agree with Sheethal suggustion to use a native SQL query as "delete from ... between".