Using Hibernate, is it possible to drop a table then recreate the exact same table on the fly.

I can't find anything in the api to drop/create tables. The only references I can find of something similiar in the forums claim I would have to remake the SessionFactory, which is acceptable as a last resort since it won't be done very often.

However, I still need to know how I can make hibernate drop and recreate the tables.

thanks

If you try to drop and create exact the same table then you probably are trying to clean tables from data quickly, if that is the case then you may do standard SQL query:
TRUNCATE TABLE zzzz

Thank you, that is exactly what I am trying to do.
I am using postgres so the TRUNCATE command is valid.

Is there a way I can make Hibernate trigger the truncate?
In other words, is there a way I can make Hibernate run arbitrary sql commands?

session.createQuery("TRUNCATE table ...") and
session.createSQLQuery("TRUNCATE table ...")

seem tied to queries only and keep throwing exceptions on me.

Thanks

Statement st = session.connection().createStatement( );
st.execute("TRUNCATE ....");
st.close();

By the way TRUNCATE is standard SQL command, it is not Postgres specific.

kgignatyev, thank you for all of your help. I am much farther than I was yesterday. And thanks for the tip about TRUNCATE too. I had thought it was postgres specific and not standard SQL. That is good to know.

I have one more question and I have been pounding my head on a wall trying to figure it out. I keep getting this Exception and I am not sure where this transaction block is coming from.

EXCEPTION: java.sql.SQLException: ERROR: TRUNCATE TABLE cannot run inside a transaction block

From everything I can find in the forums and documentation, Hibernate is supposed to require explicit transactions, however I am not using a transaction in the call. I am running this code and the exception is coming from the st.exectue(...) line:

Session session = HibernateUtil.currentSession();
try
{
Statement st = session.connection().createStatement();
st.execute("TRUNCATE TABLE fa1");
st.close();
}
catch (Exception e)
{
e.printstacktrace();
}

There are no other sessions or transactions open througout the course of the code and I have verified it several different ways.

My best guess is that the Statement itself is using an implicit transaction, though I cannot locate anything that specifically states that either.

Is there a way around this exception? Or am I missing something obvious.

Thanks.

Just made a quick test and was able truncate table and did that within transaction. There might be something related to driver/DB/Hibernate versions.

The message kind of make sense because TRUNCATE operation is irrevocable, i.e. even if performed within transaction and transaction is rolled back the data will not be restored.

As for transaction block: I guess HibernateUtils class starts transaction for new session and finishes it on session.close()

Got it working!

Thanks, kgignatyev, for all of your help, and especially for running a test for me. That is fantastic.

We were using a 7.4 postgres driver and thought we were using a 7.4 db as well, but it turns out it was only 7.2.1. So we upgraded. The documentation for the postgres 7.4 says it supports TRUNCATE inside transactions. So, 7.2 must not.

Somewhere a transaction is being (implicitly) created because once I truncate, the db table locks. Unfortunately session.close() does not release the table. I have to create a new transaction and commit it before the table is realeased. But at least it does get released. I will probably just use an explicit transaction around the above code block just to be, well, explicit.

I really appriciate all your help, kgignatyev. Thanks again.