Package org.broadleafcommerce.common.util

Class UpdateExecutor

java.lang.Object
org.broadleafcommerce.common.util.UpdateExecutor
public class UpdateExecutor extends Object
The purpose for this class is to provide an alternate approach to an HQL UPDATE query for batch updates on Hibernate filtered entities (such as sandboxable and multi-tenant entities).

This class takes an interesting approach to the use of update queries. To explain, a bit of background is required. First, Hibernate will create a temporary table and fill it will ids to use in a where clause when it executs an HQL UPDATE query. However, it will only create this temporary table when the target entity has Hibernate filters applied (i.e. sandboxable or multi-tenant entities). When creating this temporary table, a ‘insert into select’ is used to populate the values. It is my understanding that this ends up creating some locks on the original table. Because of these locks, we were seeing some instances of deadlocks during concurrent admin usage. The key was to avoid the temporary table creation. We did this by first selecting for ids (so that the filters were still honored) and then using a simple, native sql statement to execute the update on entities matching those ids. The native sql needs to be basic enough that it’s portable across platforms.

This class is responsible for building the native sql based on a template String. It does it in a way using a standard parameterized query (rather than string concatenation) to avoid the possibility of any sql injection exploit.

This implementation has the added benefit of breaking up large IN clauses into smaller chunks to avoid maximum IN clause lengths enforced by some database platforms.
Author:
Jeff Fischer

  • Constructor Details

    • UpdateExecutor

      public UpdateExecutor()

  • Method Details

    • executeUpdateQuery

      @Deprecated public static int executeUpdateQuery(jakarta.persistence.EntityManager em, String template, Object[] params, org.hibernate.type.Type[] types, List<Long> ids)
      Deprecated.
      Highly recommended not to use this method. This method results in global L2 cache region clearing. Use executeUpdateQuery(EntityManager, String, String, Object[], Type[], List) instead.
      Perform an update query using a String template and params. Note, this is only intended for special usage with update queries that have an IN clause at the end. This implementation uses Hibernate Session directly to avoid a problem with assigning NULL values. The query should be written in native SQL.

      An example looks like: 'UPDATE BLC_SNDBX_WRKFLW_ITEM SET SCHEDULED_DATE = ? WHERE WRKFLW_SNDBX_ITEM_ID IN (%s)'
      Parameters:
      em - The entity manager to use for the persistence operation
      template - the overall update sql template. The IN clause parameter should be written using 'IN (%s)'.
      params - any other params that are present in the sql template, other than the IN clause. Should be written using '?'. Should be in order. Can be null.
      types - the Type instances that identify the types for the params. Should be in order and match the length of params. Can be null.
      ids - the ids to include in the IN clause.
      Returns:
      the total number of records updated in the database

    • executeUpdateQuery

      public static int executeUpdateQuery(jakarta.persistence.EntityManager em, String template, String tableSpace, Object[] params, org.hibernate.type.Type[] types, List<Long> ids)
      Perform an update query using a String template and params. Note, this is only intended for special usage with update queries that have an IN clause at the end. This implementation uses Hibernate Session directly to avoid a problem with assigning NULL values. The query should be written in native SQL.

      An example looks like: 'UPDATE BLC_SNDBX_WRKFLW_ITEM SET SCHEDULED_DATE = ? WHERE WRKFLW_SNDBX_ITEM_ID IN (%s)'
      Parameters:
      em - The entity manager to use for the persistence operation
      template - the overall update sql template. The IN clause parameter should be written using 'IN (%s)'.
      tableSpace - optionally provide the table being impacted by this query. This value allows Hibernate to limit the scope of cache region invalidation. Otherwise, if left null, Hibernate will invalidate every cache region, which is generally not desirable. An empty String can be used to signify that no region should be invalidated.
      params - any other params that are present in the sql template, other than the IN clause. Should be written using '?'. Should be in order. Can be null.
      types - the Type instances that identify the types for the params. Should be in order and match the length of params. Can be null.
      ids - the ids to include in the IN clause.
      Returns:
      the total number of records updated in the database

    • executeTargetedCacheInvalidation

      public static void executeTargetedCacheInvalidation(jakarta.persistence.EntityManager em, Class<?> entityType, List<Long> ids)
      Parameters:
      em -
      entityType -
      ids -