A hard requirement in our project is to minimize database I/O to a table which is updated infrequently (At most once per day) but read often. We are using Websphere 5 and DB2 version7. Our application can tolerate stale data but unnecessary DB queries must be avoided. Caching seems to be the perfect solution (cache the table and expire the cache every N hours), and by looking at CMP caching commit option A in J2EE specs, one would assume that an easy solution exists. According to the J2EE specs Commit Option A, provided the entity bean has exclusive access to the underlying table, caches the data at load time and avoids further queries on the table. However, in practice every call to the finder methods (findBy.../findAll…) results in a DB query regardless of the commit option used (we have verified this using DB2 Event Monitor). Option C makes the situation even worse by running an extra query each time one of the getter methods is called. Is this behavior normal or am I overlooking something?

We have coupled all the entity beans with a session bean (Façade pattern), and the business method in the session bean looks like this:


public String[] listCitiesInProvince(String province) throws ...
{
.
.
.
try
{
.
.
.
ServiceLocator ejbLocator = new ServiceLocator ();
tablelocalHome =(TableLocalHome)ejbLocator.getLocalHome(Constants.TABLE_EJBPATH);


Collection cityCollection =
(Collection) tablelocalHome.findAllCities(province);

Iterator cityIterator = cityCollection.iterator();

String[] cityList = new String[cityCollection.size()];
while (recipientIterator.hasNext())
{
TableLocal tableLocal = (TableLocal) cityIterator.next();


if (tableLocal!= null)
{
cityList[i] = tableLocal.getCity();
System.out.println(cityList[i] + " is in " + state);
i++;
}
}
.
.
.

The Results with this approach:
With Option A: one query per call to listCitiesInProvince.(Unacceptable)
With Option C: one query per call to listCitiesInProvince + one query per call to getCity (Unacceptable)

Thanks,
Habib

I have come up with a workaround that solves our problem but I haven't seen it used anywhere else and would like to have some input. The design is as follows:

1- Define the cityCollection as a class variable for the session bean class and initialize it once in the ejbCreate by calling the finder method:

public class CitiesBean implements javax.ejb.SessionBean
.
.
.
private Collection cityCollection = null;
 .
 .
 .
 
 public void ejbCreate() throws javax.ejb.CreateException
 {
  .
  .
  ServiceLocator ejbLocator = new ServiceLocator ();
  tablelocalHome =(TableLocalHome)ejbLocator.getLocalHome(Constants.TABLE_EJBPATH);
  
  cityCollection =
  (Collection) tablelocalHome.findAllCities(province);
  .
  .
  .
 }
 .
 .
 .

2- In the business method use the cityCollection for retrieving the DB fileds
public String[] listCitiesInProvince(String province) throws …
{
 .
 .
 .
 try
 {
 .
 .
 .
  Iterator cityIterator = cityCollection.iterator();
  
  String[] cityList = new String[cityCollection.size()];
  while (recipientIterator.hasNext())
  {
  TableLocal tableLocal = (TableLocal) cityIterator.next();
  
  
  if (tableLocal!= null)
  {
   cityList[i] = tableLocal.getCity();
   System.out.println(cityList[i] + " is in " + state);
   i++;
  }
 }
 .
 .
 .

Results:
With Option A: Only one query at the load time. (Acceptable)
With Option C (With "cache data" set to expire every N minutes (WS5 specific feature): Only one query at the load time + one query every N minutes. (Acceptable)

This solution relies on the fact that the App Server will use the same instance of a stateless session bean to serve different clients as long as it doesn't have to scale. However, if the App Server decides to scale the session bean, the price is an extra query per each new instance of the session bean which is tolerable. I would like to have more opinions on this. Is this an anti-pattern? Any input is appreciated.

Thanks,
Habib

Option A behaves correctly. Container caches entity bean instances, but not the collection. So when you call findAll() it needs a query to re-get the collection.