Hi,

is there a good way to clean the whole database through hibernate?

The background is as follows: I want to clean the database every time before starting a new testcase, to make sure the database is empty and in a defined state. Currently I´m doing this through dropping the scheme and restarting the session, but this takes very long. Therefor a CREATE-DROP in the hibernate-configuratoin would not be very suitable as well, as I want to prevent hibernate from doing the mapping everytime.

I tried doing it through the Criteria-API, fetching all java.lang.Objects and performing a session.delete(object) on every object, but this leads to a not-null or transient object exception, although I did it in one transaction with setting the flushMode to "commit".
The other way I tried was doing this through HQL, but this leads to the problem, that I´m working with @CollectionOfElements which I cannot reference through hql (or at least I haven´t found a way to do it), but the HQL does explicitly no cascades.

Any help would be greatly appriciated.

Thomas

Hi Thomas,

It is definitely not a good way to fetch all the objects of your database to delete them. It is time consuming !

I see 2 different ways of doing

First using JDBC (probably the fastest):

1) desactivate constraints on all your tables
2) use JDBC to make a "DELETE FROM" on all the tables of your schema. You can use metadata table to get the list of table to avoid maintaining a list of tables.

Second using HQL:

http://www.objectfirst.com/blog/bulk-de ... ernate-3x/


Hope this help

Tiggy
PS: thx for rating if satisfied with the answer ;)

Tiggy,

thank you for your answer.
I´d prefer to do it with hql, but as I mentioned I´ve got some trouble with deleting the CollectionOfElements. Do you know a solution to that?
The problem is, that the collection is not mapped (because it is not an entity) and therefor can´t be addressed through hql like an entity, but is not cascaded as well.

The problem using pure sql is, that I still have the constraints in the table, which I would like to use in the live-system. I don´t know of any way to disable the constraints using jdbc, is there? Otherwise I cannot use the metadatam, but have to maintain a defined sequence of deletion myself.

Any input on this would be greatly appriciated.

Thomas

You cannot use delete for the collectionOfElements because it has no identifier. It has no sense. In fact an embedded has the same life cycle of its parent. It means that there is implicit cascadeType.DELETE. In other words it is deleted automatically when its parent is deleted.

For the constraints it depends of your database.
With oracle you can easily disable constraints with sql statement, I do not know for others.... but I think it should be possible for most databases.

Tiggy

Tiggy,

you are absolutely right. Anything else but cascade would not make any sense with CollectionOfElements. Unfortunately the delete is not cascaded using hql but only deleting the objects. I´m not sure if this is a bug or if I missed anything.

Nevertheless your hint with using the metadata and disabling referential integrity works perfectly. So I got, what I wanted now.

Thank you very much.
Your credit is sure ;-))

I am happy to see that is working for you,

Good luck for the futture and see you around
Tiggy