JDBCCacheLoader

Introduction:JBossCache is ditributed with the JDBC-based CacheLoader implementation that stores/loads nodes' state into relational database. The implementing class is org.jboss.cache.loader.JDBCCacheLoader.

 

Current implementation uses just one table. Each row in the table represents one node and contains three columns:

  • column for FQN (which is also a primary key column)

  • column for node's contents (attribute/value pairs)

  • column for parent FQN

 

FQN's are stored as strings. Node's content is stored as a BLOB. WARNING: TreeCache does not impose any limitations on types of objects used in FQN. But this implementation of CacheLoader requires FQN to contain only objects of type java.lang.String. Another limitation for FQN is its length. Since FQN is a primary key, the default column type for it is VARCHAR which can store text values up to some maximum length determined by a database.

 

Table Configuration:

Table and column names as well as column types are configurable with the following properties.

 

  • cache.jdbc.table.name - the name of the table. The default value is 'jbosscache'.

  • cache.jdbc.table.create - can be true or false. Indicates whether to create table at start phase. If true, the table is created if it does not already exist. The default value is true.

  • cache.jdbc.table.drop - can be true or false. Indicates whether to drop the table at stop phase.

  • cache.jdbc.fqn.column - FQN column name. The default value is 'fqn'.

  • cache.jdbc.fqn.type - FQN column type. The default value is 'varchar(255)'.

  • cache.jdbc.node.column - node's contents column name. The default value is 'node'.

  • cache.jdbc.node.type - node's contents column type. The default value is 'blob'.

 

Issues identified when using various DBMS

MySQL

  • When using the default data type (BLOB) for cache.jdbc.node.type, the data stream gets corrupted and NPE.

    • The root cause of the problem is the size limitation of BLOB in MySQL. BLOB data type has limitation of 65k.

    • To correct this issue, change the node column data type in the in JBossCache configuration file with cache.jdbc.node.type=LONGBLOB

    • See MySQL data types and data type storage requirements for further details to optimize the use of JDBCCacheLoader in your application.

Oracle

  • When using Oracle, Objects over 4k get truncated.

    • The root cause of the problem lies within older versions of Oracle JDBC driver.

    • To correct this issue, get a jdbc driver from Oracle that is newer than 10g Release2. The driver will work with both 9i and 10g databases. If you're stuck with an older driver, you can try to set a driver property. The property is SetBigStringTryClob=true

PostgreSQL

  • In order to be able to use PostgreSQL, change the node column data type in the in JBossCache configuration file with cache.jdbc.node.type=bytea .

Ingres

  • You must use Ingres version 9.2 or later.
  • In order to be able to use Ingres, change the node column data type in the in JBossCache configuration file with cache.jdbc.node.type=long byte .

Hypersonic (hsqldb)

  • Set cache.jdbc.node.type=OBJECT in order to store blobs.

 

Performance Test