We had a client who needed to display entire product categories (several hundred products in all) in a set of drop down list boxes on their Elastic Path Commerce storefront homepage. The data that had to be loaded was quite minimal, just the product codes, display names and a few localized attributes.
In this type of situation, using the default product loader to retrieve this information would not be the best approach. This is due to the fact that each Product domain object contains a large amount of data such as prices, skus, attributes, inventory and recommendations etc. Loading all these details requires a significant number of queries to be run on the database so applying this approach to a homepage with hundreds of products would result in extremely poor performance.
For this customer, a better solution was to create a set of lightweight product display classes, and have these mapped directly to some custom JPA native queries. These queries were tailored to return only the specific product details needed and therefore avoid loading any unnecessary information. To further reduce the number of database trips, we also introduced a timed cache in the storefront controllers which would store frequently accessed catalog items for a set period of time before being refreshed.
The combination of these two techniques reduced their page response times from tens of seconds (using the default product loader) to under a few seconds even in the worst case scenario where a stale cache had to be refreshed. Since most of the time the information would be available in the cache, the amount of database overhead was kept to a minimum.
If your storefront scenario has similar requirements, then the following code examples may also be useful for your project.
Using Native SQL Queries with JPA
To get all product codes and product names in a particular category, you can use the EntityManager's createNativeQuery method to create a native query with a WHERE clause that passes in the language string and a specific category UID.
final String productNamesByCatSql =
"SELECT tp.code, tpldf.display_name AS displayName"
+ " FROM tproduct AS tp"
+ " INNER JOIN tproductldf AS tpldf"
+ " ON tpldf.product_uid = tp.uidpk"
+ " INNER JOIN tproductcategory AS tpc"
+ " ON tp.uidpk = tpc.product_uid"
+ " WHERE tpldf.LOCALE = ?1"
+ " AND tpc.category_uid = ?2";
long categoryUid = getCategoryUid(request);
long start = System.currentTimeMillis();
// create a native SQL query
final Query query = entityManager.createNativeQuery(productNamesByCatSql, ProductDisplayBeanImpl.class);
// retrieve a simplified product list for a given category
final List<ProductDisplayBean> custProducts = (List<ProductDisplayBean>)
query.setParameter(1, shoppingCart.getLocale().getLanguage())
.setParameter(2, new Long(categoryUid))
.getResultList();
if (LOG.isDebugEnabled()) {
long elapsedTimeMillis = System.currentTimeMillis() - start;
LOG.debug("No. of products returned = " + custProducts.size() + ", elapsed time (ms) = " + elapsedTimeMillis);
for (ProductDisplayBean productBean : custProducts) {
LOG.debug("[" + productBean.getCode() + ":" + productBean.getDisplayName() + "]");
}
}
Like JPQL, native queries can be named for easy reuse using the @NamedNativeQuery annotation, however it's probably a better idea to externalize all the native query strings into an *orm.xml file so that the SQL can be updated without having to change the class files. If you do that, you'll have to change all the createNativeQuery()calls to createNamedQuery().
A very simple display bean containing only the product name and code is needed just to pass the information to the storefront.
public interface ProductDisplayBean {
public String getCode();
public void setCode(String code);
public String getDisplayName();
public void setDisplayName(String displayName);
}
public class ProductDisplayBeanImpl implements ProductDisplayBean {
public static final long serialVersionUID = 5000000001L;
private String code;
private String displayName;
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getDisplayName() {
return displayName;
}
public void setDisplayName(String displayName) {
this.displayName = displayName;
}
}
Caching Frequently Accessed Items
Once you've retrieved a set of items from the catalog, you can easily cache these results at the application layer using either a third party solution, such as Ehcache (http://ehcache.sourceforge.net), or your own caching mechanism. Elastic Path 6.1 already provides some out-of-the-box caching classes that you can use in your own code. We normally use our SimpleTimeoutCache class to hold a list of frequently retrieved products that don't need to be updated very often. The following code sample shows how you could integrate SimpleTimeoutCache with your product retrieval service class.
private static final long CACHE_TIMEOUT = 30000; // cache expires after 30 seconds
private final SimpleTimeoutCache<String, List<Product>> productsCache = new SimpleTimeoutCache<String, List<Product>>(CACHE_TIMEOUT);
...
String storeCode = shoppingCart.getStore().getCode();
List<StoreProduct> products = productsCache.get(storeCode);
// if the store code does not exist in the cache, then get it from the database
// and update the cache
if (products == null) {
final IndexSearchResult productResults = retrieveProducts(browsingRequest, category);
List<Long> productUids = getProductsUsingPageNumber(pageNumber, storeCode, productResults);
products = getProductRetrieveStrategy().retrieveProducts(productUids,
shoppingCart, productLoadTuner);
productsCache.put(storeCode, products);
}
The timeout value can be adjusted for your requirements. A larger value will reduce the number of trips to the database but the trade-off is a longer wait time for catalog product changes to appear.