Hibernate运行不需要的更新语句 [英] Hibernate runs unwanted update statement

问题描述

 字符串sqlSelect = 
select r.id_roster as id， + 
count（roster_cat.id_category），+ 
sum（当roster_cat.id_category IN（：categoryIds）then 1 else 0 end时）作为计数器+ 
从roster r + 
内部连接roster_sa_categories roster_cat+ 
on r.id_roster = roster_cat.id_roster+ 
其中r.day =：dayToLookFor+ 
和r。 （roster_cat.id_category）=：nrCategories+ 
和count（roster_cat.id_category）= counterid_shop =：idShop+ 
 ; 
 
查询selectRostersQuery = entityManager.createNativeQuery（sqlSelect）; 
 
 selectRostersQuery.setParameter（categoryIds，Arrays.asList（categoryIds））; 
 selectRostersQuery.setParameter（dayToLookFor，day.toString（））; 
 selectRostersQuery.setParameter（idShop，shopId）; 
 selectRostersQuery.setParameter（nrCategories，categoryIds.length）; 
 
列表<整数> rosterIds = new ArrayList<>（）; 
 
列表< Object> result =（List< Object>）selectRostersQuery.getResultList（）; 
  

由于某种原因，Hibernate在执行select之前选择进行更新，这实际上干扰了我的数据

  Hibernate：/ * update domain.Roster * / update roster set day =？，employee_count = ?, interval_end =？，interval_start =？，id_shop =？其中id_roster =？ 
 Hibernate：/ * update roster * / update roster set day =？，employee_count = ?, interval_end =？，interval_start =？，id_shop =？其中id_roster =？ 
 Hibernate：/ *动态原生SQL查询* /选择r.id_roster作为id，count（roster_cat.id_category），sum（roster_cat.id_category IN（？）then 1 else 0 end）内部连接roster_sa_categories 
 r.id_roster = roster_cat.id_roster roster_cat其中r.day =？和r.id_shop =？由r.id_roster组计数（roster_cat.id_category）=？和计数（roster_cat.id_category）=计数器
  

任何帮助将不胜感激，谢谢

解决方案

您所描述的正是Hibernate的 FlushMode.AUTO 隐含的含义。

在执行查询时，持久化上下文（1LC）中的任何修改都将在执行查询之前自动刷新，从而确保数据库匹配返回的结果那是通过内存中的修改缓存的。

如果查询将返回您看到更新的实体，那么您应该重新评估操作，确保查询在更新之前触发，以避免刷新操作，根据持久化上下文中的实体数量，这可能非常昂贵。

如果您绝对确信您查看的更改不会被相关查询返回，您可以通过手动设置刷新模式强制查询不导致刷新： p>

 查询查询= session.createQuery（...）; 
 query.setFlushMode（FlushMode.COMMIT）; 
列表结果= query.list（）; 
  

但是，只有在确定查询不会将未提交的更改读取为这可能会导致很多问题，并导致长时间的调试会话，以了解为什么更改会被应用程序无意中丢失。

I have native query to run :

String sqlSelect =
"select r.id_roster as id, " +
                    "count(roster_cat.id_category), " +
            "       sum(case when roster_cat.id_category IN ( :categoryIds)                       then 1 else 0 end) as counter " +
            "from roster r " +
            "inner join roster_sa_categories roster_cat " +
                "on r.id_roster = roster_cat.id_roster " +
            "where r.day = :dayToLookFor " +
                "and r.id_shop = :idShop " +
            "group by r.id_roster " +
            "having count(roster_cat.id_category) = :nrCategories " +
                "and count(roster_cat.id_category) = counter" ;

    Query selectRostersQuery = entityManager.createNativeQuery(sqlSelect);

    selectRostersQuery.setParameter("categoryIds", Arrays.asList(categoryIds));
    selectRostersQuery.setParameter("dayToLookFor", day.toString());
    selectRostersQuery.setParameter("idShop", shopId);
    selectRostersQuery.setParameter("nrCategories", categoryIds.length);

    List<Integer> rosterIds =  new ArrayList<>();

    List<Object> result = (List<Object>) selectRostersQuery.getResultList();

For some reason Hibernate choses to do an update before executing the select and it is really interfering with my data

     Hibernate: /* update domain.Roster */ update roster set day=?,     employee_count=?, interval_end=?, interval_start=?, id_shop=? where id_roster=?
     Hibernate: /* update Roster */ update roster set day=?, employee_count=?,  interval_end=?, interval_start=?, id_shop=? where id_roster=?
     Hibernate: /* dynamic native SQL query */ select r.id_roster as id,   count(roster_cat.id_category),sum(case when roster_cat.id_category IN ( ?) then 1 else 0 end) as counter from roster r inner join roster_sa_categories 
roster_cat on r.id_roster = roster_cat.id_roster where r.day = ? and r.id_shop = ? group by r.id_roster having count(roster_cat.id_category) = ? and count(roster_cat.id_category) = counter

Any help would be appreciated,Thank you

解决方案

What you describe is precisely what Hibernate's FlushMode.AUTO implies.

Any modifications in the Persistence Context (1LC) at the time a query is executed will be automatically flushed prior to executing the query, guaranteeing that the results returned by the database match that which was cached by in-memory modifications.

If the query is going to return entities that you're seeing the update for, then you should likely re-evaluate your operations, making sure that the query fires prior to the update to avoid the flush operation, which can be quite expensive depending on the volume of entities in your Persistence Context.

If you are absolutely sure that the changes you're seeing flushed won't be returned by the query in question, you can always force the query not to cause a flush by setting the flush mode manually:

Query query = session.createQuery( ... );
query.setFlushMode( FlushMode.COMMIT );
List results = query.list();

But only do this if you're sure that the query wouldn't then be reading uncommitted changes as this can cause lots of problems and lead to long debug sessions to understand why changes are being inadvertantly lost by your application.

这篇关于Hibernate运行不需要的更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！