How change many records in an ORM database quickly?

Question

Situation: I need to change many records in database (10 000 records, in example), using ORMLite DAO. All records change only in one table, in one column and changing records, which have specified id.

Question: how update many records in database at once, using ORMLite DAO?

---

Now I update records, using this code:

imagesDao.update(imageOrmRecord);

But updating records in cycle very slow (100 records\sec).

I think that real update records, using SQL-code, but this is undesirable...

Answer 1

SQL is a set-oriented language. The whole point of an ORM is to abstract this away into objects. So when you want to update a bunch of objects, you have to go through these objects. (You have run into the object-relational impedance mismatch; also read The Vietnam of Computer Science.)

ORMLite gives you a backdoor to execute raw SQL:

someDao.executeRaw("UPDATE ...");

But if your only problem is performance, this is likely to be caused by the auto-commit mode, which adds transaction overhead to each single statement. Using callBatchTasks() would fix this.

Answer 2

Question: how update many records in database at once, using ORMLite DAO?

It depends a bit on what updates you are making. You can certainly use the UpdateBuilder which will make wholesale updates to objects.

UpdateBuilder<Account, String> updateBuilder = accountDao.updateBuilder();
// update the password to be "none"
updateBuilder.updateColumnValue("password", "none");
// only update the rows where password is null
updateBuilder.where().isNull(Account.PASSWORD_FIELD_NAME);
updateBuilder.update();

Or something like:

// update hasDog boolean to true if dogC > 0
updateBuilder.updateColumnExpression("hasDog", "dogC > 0");

You should be able to accomplish a large percentage of the updates that you would do using raw SQL this way.

But if you need to make per-entity updates then you will need to do dao.update(...) for each one. What I'd do then is to do it in a transaction to make the updates go faster. See this answer.