The context

Assume you are dealing with a hibernate operation which requires to copy a lot of objects in the database. For instance you are having 2 objects: budget and entries and you want to duplicate the budget and all it’s entries for some operation. There are several way to do this. Check original article.

First approach

In hibernate usual approach this is done most of the time as something similar to:

//duplicate the oldBudgetVersion
//load the old entries
query = session.createQuery("from BudgetEntry as e where e.version.id = :budgetVersionId");
query.setParameter("budgetVersionId", budgetVersion.getId());
List entries = query.list();
for (Iterator i = entries.iterator(); i.hasNext();) {
        BudgetEntry entry = (BudgetEntry) i.next();
    BudgetEntry newEntry = new BudgetEntry();
    newEntry.setAmount(entry.getAmount());
    //set all the other valies
    session.save(newEntry);
}
session.flush();

This is probably ok for a small number of entries but it will become not so efficient for more entries. Consider the number of sql’s:

    * load the old budgetVersion (1)

    * save the new budgetVersion (1)

    * load the old entries (1) and the associated objects which might need referencing (n*?)

    * save the new entries (n)

It can sum up to 400 sql’s for around 200 entries.

Second approach

Even if there are not many examples hibernate supports DML style syntax. For our case it can be implemented as:

query = session.createQuery("insert into BudgetEntry(version, structure, amount, ....) select " +
            "v, e.structure, e.amount, ... from BudgetEntry e, BudgetVersion v where e.version.id = rigBudgetVersionId and v.id = :newBudgetVersionId");
query.setParameter("newBudgetVersionId", newBudgetVersion.getId());
query.setParameter("origBudgetVersionId", budgetVersion.getId());
query.executeUpdate();
session.flush();

which sums up to only 1 sql. Note that there a bit of a trick in the join to actually make the new objects link to the proper parent but it’s worth every effort.

Conclusion

In my simple test with around 800 entries the first approach took a bit over 5 minutes while the second a bit under 5 seconds.