Executing custom queries against custom tables is an historical problem affecting a lot of ecommerce projects implemented with several big ecommerce frameworks (Oracle, IBM, SAP).
On WebSphere Commerce the developers have used during a lot of years (and they are still using) an undocumented API (ServerJDBCHelperAccessBean) to access custom tables data and IBM it self suggests to use it on developerworks.
This widely used API has another important inconvenient: it cannot be used to manage custom results caching.
But starting from the cumulative fix #2 for v7 Fixpack 9 (JR53438), we can finally use an optimized and documented API to query our Commerce tables: WCDataCache.
IBM extended the existing Commerce data cache API with a very powerful new method: executeParametrizedQuery.
This method should replace the correspondent ServerJDBCHelperAccessBean call allowing the developers to cache custom query results and associate them a specific dependencyId or a TTL to invalidate them when needed.
This new API has to be used in conjunction with the new data cache settings coming with FixPack 9: maxTimeToLiveForAutoCacheEntries and autoCacheableTableNames.
It’s time to refactor your code then and cache without fears your custom queries.