The best way to update a millions lines in Java Batch

user_5461890 0
Viewed 232

I devloppe a batch with juste main method in legacy project with : java 7, hibernate and Spring using mysql database.

In this batch I want to update a several lines in a table that have more than 50 millions lines. When I start the batch each day I have to update at least 10000 lines.

So, what is the best way to update the line without lock the table in mysql ?

Juste do one query like this :

update table items set is_achive = true where id in (id1,id2,id3....id10000)

Or use a for loop like this :

for(item p : ItemsList){
   update table item  set is_achive = true where id = p.id
}
hibernate spring mysql java
3 Answers

This depends how you determine the list of rows that need updating. If you query the database to determine the list, it's probably best just to use a DML statement like:

UPDATE Item i SET i.achive = true WHERE ...

If your concern is locking i.e. the amount of time rows are locked, you can use batching by using a cursor e.g. some id of the data source.

SELECT id FROM ... WHERE id >= :start AND ...
ORDER BY id
OFFSET 100 -- use a batch size that suites your needs
LIMIT 1 -- use a batch size that suites your needs

The limit and for update can be implemented by using a query

Integer end = entityManager.createQuery("SELECT id FROM ... WHERE id >= :start AND ... ORDER BY id")
    .setParameter("start", previousEnd)
    .setFirstResult(100) // batch size
    .setMaxResults(1)
    .getResultList().stream().findFirst().orElse(null);

Then do a query like this

UPDATE Item i SET i.achive = true WHERE i.id BETWEEN :start AND :end

or if the end is null i.e. the last batch use

UPDATE Item i SET i.achive = true WHERE i.id >= :start
user_4124460

Use Hibernate Criteria builder:

    CriteriaBuilder cb = this.em.getCriteriaBuilder();

    // create update
    CriteriaUpdate<Order> update = cb.createCriteriaUpdate(Order.class);

    // set the root class
    Root e = update.from(Order.class);

    // set update and where clause
    update.set("amount", newAmount);
    update.where(cb.greaterThanOrEqualTo(e.get("amount"), oldAmount));

    // perform update
    this.em.createQuery(update).executeUpdate();

https://thorben-janssen.com/criteria-updatedelete-easy-way-to/

user_58993840
user_126699450
Related