Alright to truncate database tables when also using Hibernate?

Question

Is it OK to truncate tables while at the same time using Hibernate to insert data?

We parse a big XML file with many relationships into Hibernate POJO's and persist to the DB.

We are now planning on purging existing data at certain points in time by truncating the tables. Is this OK?

It seems to work fine. We don't use Hibernate's second level cache. One thing I did notice, which is fine, is that when inserting we generate primary keys using Hibernate's @GeneratedValue where Hibernate just uses a key value one greater than the highest value in the table - and even though we are truncating the tables, Hibernate remembers the prior value and uses prior value + 1 as opposed to starting over at 1. This is fine, just unexpected.

Note that the reason we do truncate as opposed to calling delete() on the Hibernate POJO's is for speed. We have gazillions of rows of data, and truncate is just so much faster.

Answer 1

Depends on your application. If deleting rows in the database is okey, then truncate is okey, too.

As far as you don't have any Pre- or PostRemove listeners on your entities, there should be no problems.

On the other hand... is it possible that there are still entities loaded in an EntityManager at truncate time, or is this a writeonly table (like a logging table). In this case you won't have any problem at all.

Answer 2

We are now planning on purging existing data at certain points in time by truncating the tables. Is this OK?

If you're not using the second level cache and if you didn't load Entities from the table you're going to truncate in the Session, the following should work (assuming it doesn't break integrity constraints):

Session s = sf.openSession();
PreparedStatement ps = s.connection().prepareStatement("TRUNCATE TABLE XXX");
ps.executeUpdate();

And you should be able to persist entities after that, either in the same transaction or another one.

Of course, such a TRUNCATE won't generate any Hibernate event or trigger any callback, if this matters.

(...) when inserting we generate primary keys using Hibernate's @GeneratedValue (...)

If you are using the default strategy for @GeneratedValue (i.e. AUTO), then it should default to a sequence with Oracle and a sequence won't be reseted if you truncate a table or delete records.

---

We truncate tables like jdbcTemplate.execute("TRUNCATE TABLE abc")

This should be equivalent (you'll end-up using the same underlying JDBC connection than Hibernate).

What sequence would Hibernate use for the inserts?

AFAIK, Hibernate generates a default "hibernate_sequence" sequence for you if you don't declare your own.

I thought it was just doing a max(field) + 1 on the table?

I don't think so and the fact that Hibernate doesn't start over from 1 after the TRUNCATE seems to confirm that it doesn't. I suggest to activate SQL logging to see the exact statements performed against your database on INSERT.

The generator we specify for @GeneratedValue is just a "dummy" generator (doesn't correspond to any sequence that we've created).

I'm not 100% sure but if you didn't declare any @SequenceGenerator (or @TableGenerator), I don't think that specifying a generator changes something.