Hibernate Java Tips for Updating the Performance of All Table Fields

Question

I have such a requirement.

protected Integer[] updateFullTable(final Class clazz){
    final ProjectionList projectionList=Projections.projectionList().add(Projections.property("id"),"id");
    final Criteria criteria=session.createCriteria(clazz)
    .add(Restrictions.eq("typeOfOperation",1))
    .add(Restrictions.eq("performUpdate",true));
    criteria.setProjection(projectionList);
    final List idsList=criteria.list();
    final Integer[]ids = transformObjectArrayIntoIntegerArray(idList);
    //NOW WE UPDATE THE ROWS IDS. 
    final Query query=session.createQuery("update "+clazz.getName()+" set activeRegister=true and updateTime=:updateTime where id in (:ids)")
    .setParameter("updateTime",new Date())
    .setParameterList("ids",ids);
    query.executeUpdate();          
    return transform;
}

As you guys can see that I need to update all the rows in the table, once I request all the row identifiers, and then apply the update to these identifiers in a separate query, but there are a lot of records in the tables, sometimes it takes between 30 seconds to 10 minutes depends on the table .

I changed this code to only one update like this.

final Query query=session.createQuery("update "+clazz.getName()+" set activeRegister=true and updateTime=:updateTime where typeOfOperation=1 and performUpdate=true");

And with this single request, I avoid the first request, but I can no longer return the affected identifiers. But later change a was required

final StringBuilder logRevert;

Added option.

To store updated identifiers for applying direct reverse update to the database, if necessary.

. , ids - , , .

.

• HQL.
• namedQuery
• SqlQuery
• , []

- .

-

query.executeUpdate();   // RETURNS THE COUNT OF THE AFFECTED ROWS

......

, .

UPDATE

@dmitry-senkovich rawSQL, , .

https://stackoverflow.com/questions/44641851/java-hibernate-org-hibernate-exception-sqlgrammarexception-could-not-extract-re

Answer 1

?

SET @ids = NULL;

UPDATE SOME_TABLE
SET activeRegister = true, updateTime = :updateTime
WHERE typeOfOperation = 1 and performUpdate = true
      AND (SELECT @ids := CONCAT_WS(',', id, @ids));

SELECT @ids;

Answer 2

updateTime -

Date updateTime = new Date(); // time from update

select id from clazz.getName() where updateTime=:updateTime and  activeRegister=true and typeOfOperation=1 and performUpdate=true

Answer 3

- . "" ROLLBACK (- ROLLBACK, UPDATE, UPDATE ).

, UPDATE.

, . , SHOW CREATE TABLE, " " ...

, (), , ( " " ). ,

• (TEXT, BLOB ..) - .
• - , .
• SELECT - .

- JOINing .