How to bulk delete records using temporary tables in Hibernate?

Question

I have a question. Where did these methods go?

Dialect.supportsTemporaryTables();
Dialect.generateTemporaryTableName();
Dialect.dropTemporaryTableAfterUse();
Dialect.getDropTemporaryTableString();

I've tried to browse git history for Dialect.java, but no luck. I found that something like MultiTableBulkIdStrategy was created but I couldn't find any example of how to use it.

To the point...I have legacy code (using hibernate 4.3.11) which is doing batch delete from multiple tables using temporary table. In those tables there may be 1000 rows, but also there may be 10 milion rows. So just to make sure I don't kill DB with some crazy delete I create temp table where I put (using select query with some condition) 1000 ids at once and then use this temp table to delete data from 4 tables. It's running in while cycle until all data based on some condition is not deleted. Transaction is commited after each cycle.

To make it more complicated this code has to run on top of: mysql, mariadb, oracle, postgresql, sqlserver and h2.

It was done using native SQL, with methods mentioned above. But not I can't find a way how to refactor it.

My first try was to create query using nested select like this: delete from TABLE where id in (select id from TABLE where CONDITION limit 1000) but this is way slower as I have to run select query multiple times for each delete and limit is not supported in nested select in HQL.

Any ideas or pointers?

Thanks.

Answer 1

The methods were present in version 4.3.11 but removed in version 5.0.0. It seems a bit unusual that they were removed rather than deprecated - the background is on this Jira ticket.

To quote from this:

Long term, I think the best approach is to remove the Dialect method intended to support table tabled in a piecemeal fashion and to make MultiTableBulkIdStrategy be a fully self-contained contract.

The methods were removed in this commit.

So it seems that getDefaultMultiTableBulkIdStrategy() is the intended replacement for these methods - but I'm not entirely clear on how, as it currently has no Javadoc. Guess you could try to work it out from the source code ...or if all else fails, perhaps try to contact Steve Ebersole, who implemented the change?