有关更新所有表字段性能的Java Hibernate技巧 [英] Java Hibernate tips about update all table fields performance

问题描述

我有这样的要求.

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;
}

你们看到我有时需要更新表中的所有行，然后查询所有行ID，然后在单独的查询中将更新应用于这些ID，但是表中有很多记录，有时 30秒到10分钟取决于表格.

As you guys can see I need to update all rows in a table sometime I query all the rows ids and later apply the update to those ids in a separate query but the tables has a lot of records sometimes takes between 30 seconds to 10 minutes depends of the table.

我已将此代码更改为仅一个这样的更新.

I have change 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");

对于唯一的查询，我避免了第一个查询，但是我不能再返回受影响的ID.但是后来的要求是更改一个

And with that only query I avoid the first query but I cannot not longer return the affected Ids. But later the requirement was change a

final StringBuilder logRevert;

已添加参数.

需要存储哪些更新的ID，以便在数据库中应用直接反向更新.

Which needs to store the updated ids to apply a direct reverse update into the DB if required.

但是随着我的更新，我无法再获得ID.我的问题是，如何使用存储过程或数据库或休眠模式中的某些变通方法来获取或返回受影响的ID，我的意思是仅使用一个查询或增强的代码即可获得第一个行为.

But with my update i cannot get the Ids not longer. My question is how can I get or return the affected ids using a stored procedure or some workaround in the DB or hibernate I mean get the first behaviour with only one query or a enhanced code..

任何提示.

我尝试过

1. 使用条件
2. 使用HQL.
3. 使用namedQuery
4. 使用SqlQuery
5. 不使用转换器返回原始对象[]

但是时间仍然很高.

我想要类似的东西

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

但是我需要受影响的ID.

But I need the affected Ids......

很抱歉，问题很简单.

Sorry if the question is simple.

更新

使用@ dmitry-senkovich可以使用rawSQL进行操作，但不能使用休眠方式在此处进行单独的问题.

With @dmitry-senkovich I could do it using rawSQL but not with hibernate a separated question was made here.

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

推荐答案

以下解决方案如何?

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;

这篇关于有关更新所有表字段性能的Java Hibernate技巧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！