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 Summary

Constructors

Constructor and Description
UpdateExecutor()

Method Summary

Methods

Modifier and Type	Method and Description
static int	executeUpdateQuery(javax.persistence.EntityManager em, String template, Object[] params, org.hibernate.type.Type[] types, List<Long> ids) Perform an update query using a String template and params.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

UpdateExecutor

public UpdateExecutor()

Method Detail

executeUpdateQuery

public static int executeUpdateQuery(javax.persistence.EntityManager em,
                     String template,
                     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)'.

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