hibernate and delete all

Question

What is the best way to delete all rows in a table in Hibernate?

If I iterate over a collection and call session.delete() it's not performing to my knowledge.

If I use another option session.createQuery("delete ...") it doesn't affect persistence context.

When I should use these methods if there is no better variant?

score 28 · Answer 1 · answered Aug 16 '10 at 11:02

28

You can use HQL for truncate table

public int hqlTruncate(String myTable){
    String hql = String.format("delete from %s",myTable);
    Query query = session.createQuery(hql)
    return query.executeUpdate();
}

answered Aug 16 '10 at 11:02

Damian Leszczyński - Vash

30,365
9
60
95

This is the best way to do it – Shervin Asgari Aug 16 '10 at 11:18
10

You have to write the name of java class file in query not table. – Muhammad Imran Tariq Apr 25 '13 at 10:43
4

Does not cascade deletes and thus may violate fk constraints. – atamanroman Feb 03 '14 at 12:19
5

This is not a truncate. – Steve Swinsburg Sep 17 '15 at 23:59

Bozho · Accepted Answer · 2010-08-16T12:01:34.297

if you don't have anything to cascade, use the HQL delete DELETE FROM enityName
if you have cascades, iterate the collection and delete each one individually.

The problem lies in the fact that hibernate handles cascades internally, rather than leaving this to the database. So sending a query won't trigger the internal cascades, hence you will have inconsistencies / orphans.

If performance is so crucial (after all it's not everyday that one truncates a table), then you can have more than 1 HQL delete for each cascade - i.e. handling the cascades manually.

score 10 · Answer 3 · answered Aug 16 '10 at 11:01

10

String stringQuery = "DELETE FROM tablename";
Query query = session.createQuery(stringQuery);
query.executeUpdate();

answered Aug 16 '10 at 11:01

thelost

6,638
4
28
44

why is it better to use createQuery than iterate over the collection and call session.delete()? Couldn't I have problem with persistence context? – feiroox Aug 16 '10 at 11:03
3

@feiroox: It is better because when you iterate over Collection and call delete, it will delete one and one row. This is a big performance issue. However, like Bozho says, if you have cascades, you would perhaps want to do this like that. – Shervin Asgari Aug 16 '10 at 11:20
1

As mentioned above this doesn't handle cascades. I don't know if it is possible to emphasize that more strongly. I once wasted a lot of time because I assumed that running the HQL would handle cascades. It IS Hibernate Query Language after all. But, sadly, it doesn't. You have to do a session.delete() but this does issue a SQL statement for each individual record which IS very inefficient. – Jack Holt Sep 05 '14 at 20:30

hibernate and delete all

3 Answers3

Linked