Hibernate Delete object by id

Question

Which is the best method(performance wise) to delete an object if only its id is available.

1. HQL. Will executing this HQL load the SessionContext object into hibernate persistence context ?

   for(int i=0; i<listOfIds.size(); i++){
       Query q = createQuery("delete from session_context where id = :id ");
           q.setLong("id", id);
           q.executeUpdate();
   }
   

2. Load by ID and delete.

   for(int i=0; i<listOfIds.size(); i++){
       SessionContext session_context = (SessionContext)getHibernateTemplate().load(SessionContext.class, listOfIds.get(i));
       getHibernateTemplate().delete(session_context) ;
   }
   

Here SessionContext is the object mapped to session_context table. Or, well off course is there an all together different and better approach ?

Answer 1

Out of the two, the first one is better, where you will save memory. When you want to delete the Entity and you have the id with you, writing a HQL is preferred.

In you case there is a third and better option,

Try the below,

//constructs the list of ids using String buffer, by iterating the List.
String idList = "1,2,3,....."

Query q = createQuery("delete from session_context where id in (:idList) ");
q.setString("idList", idList);
q.executeUpdate();

Now if there are 4 items in the list only one query will be fired, Previously there would be 4.

Note:- For the above to work, session_context should be an independent table.

Answer 2

Btw, say no to that ugly string, there is .setParameterList(), so:

List<Long> idList = Arrays.asList(1L, 2L, 3L);

Query q = createQuery("delete from session_context where id in (:idList) ");
q.setParameterList("idList", idList);
q.executeUpdate();

update I must update on this, in our environment, in the end it turned out, that using setParameterList gives much worse performance, than creating a string manualy and using setString like @ManuPK suggested.

Answer 3

You should also consider caching - first level (session) and second level cache.

The first option is probably the best if the delete is the only or the first operation in transaction.

If you query for some SessionContext objects then call the HQL to delete then all objects in query cache will be evicted, because hibernate doesn't know which to delete. This is not the case with the second approach.

If you use second level cache then it is even more complicated and highly depends on what you do with SessionContext objects.