org.broadleafcommerce.common.util

Class AbstractUpdateExecutor

java.lang.Object

org.broadleafcommerce.common.util.AbstractUpdateExecutor

public abstract class AbstractUpdateExecutor
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

Field Summary

Fields

Modifier and Type	Field and Description
protected javax.persistence.EntityManager	em

Constructor Summary

Constructors

Constructor and Description
AbstractUpdateExecutor()

Method Summary

Methods

Modifier and Type	Method and Description
protected String	buildInClauseTemplate(int length) Quickly build up the sql IN clause template
protected List<Long[]>	buildRuns(List<Long> ids) This breaks up our IN clause into multiple runs of 800 or less in order to guarantee compatibility across platforms (i.e.
protected int	executeUpdateQuery(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

Field Detail

em

protected javax.persistence.EntityManager em

Constructor Detail

AbstractUpdateExecutor

public AbstractUpdateExecutor()

Method Detail

executeUpdateQuery

protected int executeUpdateQuery(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:

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

buildInClauseTemplate

protected String buildInClauseTemplate(int length)

Quickly build up the sql IN clause template

Parameters:

length -

Returns:

buildRuns

protected List<Long[]> buildRuns(List<Long> ids)

This breaks up our IN clause into multiple runs of 800 or less in order to guarantee compatibility across platforms (i.e. some db platforms will throw a error if there are more than a 1000 entries in an sql IN clause).

Parameters:

ids -

Returns: