Utilizing OpenSymphony Caching (OSCache) with iBatis

Background

My current project is ramping up to be deployed into a centrally hosted data center to be accessed by a large volume of users. In past deployments, we could expect, at most, 200-300 users to be logged into our web application. When translating that number to active users, we can expect somewhere in the range of 25 to 100 active users at any given point in time.

With deployment into a centrally hosted environment, our anticipated user base significantly increases to be approximately 1000 concurrent, active users. With this large number of users, we wanted to investigate caching frequently accessed objects.

The major area in our web application that currently utilizes caching is at the DAO level. Our DAOs are leveraging iBatis and already using iBatis in-memory cache implementation. Moving to a centrally hosted environment, the application will be clustered. Thus, supporting fail-over and high availability. However, using the iBatis cache, we risk the possibility that users see different objects depending upon their assigned clustered instance and when the iBatis cache is refreshed. To synchronize cache flushing across the cache, we decided to investigate incorporating a distributed caching mechanism. With iBatis supporting OSCache, we decided to start there.

Implementation

As mentioned previously, iBatis documentation refers to using OSCache for distributed caching within a clustered environment. Assuming you are using maven to build your project, configuring iBatis to use OSCache is extremely easy and well documented.

1. Modify the project's pom.xml to include oscache.jar (2.4) as a dependency
2. Identify and modify the sqlMap / DAO that you wish to use oscache. change cacheModel type to be "OSCACHE"
3. Optionally include an oscache.properties file on the class path. Within development or continuous integration (CI) environments, this file does not need included as default properties will be applied. Deployment in production or other test areas can include the file.

For configuration options and settings including clustering instructions, refer to OSCache web site. The clustering option sends a flush event that causes all caches within the cluster to be flushed. Currently, OSCache can be configured to send flush events via JMS or JavaGroups (multi-cast). All of the settings are nicely documented.

http://www.opensymphony.com/oscache/wiki/Configuration.html
http://www.opensymphony.com/oscache/wiki/Clustering.html

Supporting Multiple DBs using iBatis

I previously wrote about configuring multiple data sources within JBoss. When writing SQL, you may undoubtedly encounter cases where SQL is not ANSI 92 compliant. In other words, you have DB specific SQL statements. Maybe it's because of performance reasons. Maybe it's because the difference in handling sequential columns. How can you write, configure, & deploy the application without having specific SQL statements being handled in your Java code?

We encountered this issue on our current project and created an elegant, but simple solution.

Using iBatis for our Java to DB mapping framework, all of our SQL statements were contained within XML files. With the potential requirement to support multiple databases (SQL Server & at least Oracle), we wanted a way to reuse the statements that were compliant across both databases.

Step 1:
Within the MappedStatements XML files, we appended the DB name. For example, for Oracle specific SQL, the name would be 'getSomethingData-Oracle'. If the SQL was DB neutral, we omitted the DB designation. How do we modify the app to determine the appropriate SQL for the database at runtime?

Step 2:
Like good little programmers, we created a BaseDao class from which all concrete DAOs extended. Upon initialization of a DAO, our BaseDao class retrieved the connection meta data and determined the connected DB. Now, we know the runtime DB. But how do we choose the appropriate SQL?

Step 3:
We modified BaseDao to extend org.springframework.orm.ibatis.support.SqlMapClientDaoSupport providing our code the ability to retrieve a MappedStatement from the XML files. This class extension gives our code the ability to check for the existence of a MappedStatement - be it DB specific or non-DB specific.

Step 4:
Finally, within the concrete DAO classes, we called 'checkMappedStatement' when attempting to retrieve any MappedStatement. The BaseDao class handles retrieving the appropriate SQL for the runtime DB - be it specific or generic.

The concrete DAOs would retrieve the SQL from iBatis using the following construct. If DB specific SQL existed within the MappingStatement XML files for the runtime DB, that SQL would be returned.

getSqlMapClientTemplate().queryForList(checkMappedStatement("getSomethingData"));

Below is our simple BaseDao class. Quite simple.

public class BaseDaoiBatis extends SqlMapClientDaoSupport {
 private static final Logger log = Logger.getLogger(BaseDaoiBatis.class);
 private String dbProduct = null;

 protected String checkMappedStatement(String id) {
     MappedStatement ms;
     String statementId = id;

     try {
         ms = ((SqlMapClientImpl) getSqlMapClient()).getMappedStatement(id + "-" + getDbProduct());

         // Look for DB specific SQL. If found, return DB specific mapping id
         if (ms != null) {
             statementId = id + "-" + dbProduct;
         }
     }
     catch (SqlMapException sme) {
        // If not found, use default SQL mapping
        log.debug("DB-specific SQL not found, using default SQL mapping");
     }

     return statementId;
 }

  private void initDbProduct() {
      DatabaseMetaData dbMetaData;
      Connection conn = null;

      try {
          conn = getSqlMapClientTemplate().getDataSource().getConnection();
          dbMetaData = conn.getMetaData();

          log.info("Database product name is '" + dbMetaData.getDatabaseProductName() + "'");

          if (dbMetaData.getDatabaseProductName().indexOf("SQL Server") > 0) {
              dbProduct = "MSSQLServer"
          } else if (dbMetaData.getDatabaseProductName().indexOf("Oracle") > 0) {
              dbProduct = "Oracle"
          } else {
              dbProduct = dbMetaData.getDatabaseProductName();
          }

          log.info("Using " + dbProduct + " XML statements...");
      }
      catch (Exception e) {
          log.error("Exception occurred obtaining database information", e);
      }
      finally {
          if (conn != null) {
              try {
                  conn.close();
              } catch (SQLException e) {
                  log.error("SQLException thrown when closing connection");
              }
          }
      }
  }
}