Apache Commons logo

Commons JCS™

JDBC Disk Auxiliary Cache

The JDBC disk cache uses a relational database such as MySQL as a persistent store. It works with Oracle, MySQL and HSQL. The cache elements are serialized and written into a BLOB. Multiple regions can share a single table. You can define multiple, differently configured JDBC disk caches in one JCS instance. This allows you to use different tables for different cache regions.

If you want to use numerous JDBC disk cache instances that talk to the same database, you can configure them to share a connection pool. You might want to use several different tables to partition the data. Some operations, such as index building on a MyISAM storage engine take longer if there are more items in the table.

Example #1 cache.ccf (MySQL)

					
##############################################################
################## DEFAULT CACHE REGION  #####################
# sets the default aux value for any non configured caches
jcs.default=MYSQL,RCluster
jcs.default.cacheattributes=org.apache.commons.jcs3.engine.CompositeCacheAttributes
jcs.default.cacheattributes.MaxObjects=5000
jcs.default.cacheattributes.MemoryCacheName=org.apache.commons.jcs3.engine.memory.lru.LRUMemoryCache
jcs.default.cacheattributes.UseMemoryShrinker=true
jcs.default.cacheattributes.MaxMemoryIdleTimeSeconds=7200
jcs.default.cacheattributes.ShrinkerIntervalSeconds=60
jcs.default.elementattributes=org.apache.commons.jcs3.engine.ElementAttributes
jcs.default.elementattributes.IsEternal=false
jcs.default.elementattributes.MaxLife=14400
jcs.default.elementattributes.IdleTime=14400
jcs.default.elementattributes.IsSpool=true
jcs.default.elementattributes.IsRemote=true
jcs.default.elementattributes.IsLateral=true

##############################################################
################## CACHE REGIONS AVAILABLE ###################

##############################################################
################## AUXILIARY CACHES AVAILABLE ################
# MYSQL disk cache used for flight options
jcs.auxiliary.MYSQL=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheFactory
jcs.auxiliary.MYSQL.attributes=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheAttributes
jcs.auxiliary.MYSQL.attributes.userName=myUsername
jcs.auxiliary.MYSQL.attributes.password=myPassword
jcs.auxiliary.MYSQL.attributes.url=jdbc:mysql://localhost:3306/YOURDBNAME?autoReconnect=true
jcs.auxiliary.MYSQL.attributes.driverClassName=com.mysql.jdbc.Driver
jcs.auxiliary.MYSQL.attributes.tableName=JCS_STORE
jcs.auxiliary.MYSQL.attributes.testBeforeInsert=false
jcs.auxiliary.MYSQL.attributes.maxActive=100
jcs.auxiliary.MYSQL.attributes.MaxPurgatorySize=10000000
jcs.auxiliary.MYSQL.attributes.UseDiskShrinker=true
jcs.auxiliary.MYSQL.attributes.ShrinkerInterval=1800
jcs.auxiliary.MYSQL.attributes.allowRemoveAll=false
jcs.auxiliary.MYSQL.attributes.EventQueueType=POOLED
jcs.auxiliary.MYSQL.attributes.EventQueuePoolName=disk_cache_event_queue

##############################################################
################## OPTIONAL THREAD POOL CONFIGURATION #########
# Disk Cache pool
thread_pool.disk_cache_event_queue.useBoundary=true
thread_pool.disk_cache_event_queue.boundarySize=1000
thread_pool.disk_cache_event_queue.maximumPoolSize=50
thread_pool.disk_cache_event_queue.minimumPoolSize=10
thread_pool.disk_cache_event_queue.keepAliveTime=3500
thread_pool.disk_cache_event_queue.whenBlockedPolicy=RUN
thread_pool.disk_cache_event_queue.startUpSize=10
        
				

Example #2 cache.ccf (MySQL)

This example uses two JDBC Disk Cache instances and a shared connection pool.

					
jcs.default=JDBC_0
jcs.default.cacheattributes=org.apache.commons.jcs3.engine.CompositeCacheAttributes
jcs.default.cacheattributes.MaxObjects=100
jcs.default.cacheattributes.MemoryCacheName=org.apache.commons.jcs3.engine.memory.lru.LRUMemoryCache
jcs.default.cacheattributes.UseMemoryShrinker=false
jcs.default.cacheattributes.MaxMemoryIdleTimeSeconds=3600
jcs.default.cacheattributes.ShrinkerIntervalSeconds=60
jcs.default.elementattributes=org.apache.commons.jcs3.engine.ElementAttributes
jcs.default.elementattributes.IsEternal=false
jcs.default.elementattributes.MaxLife=700
jcs.default.elementattributes.IdleTime=1800
jcs.default.elementattributes.IsSpool=true
jcs.default.elementattributes.IsRemote=true
jcs.default.elementattributes.IsLateral=true

# #############################################################
# ################# CONFIGURED REGIONS ########################

jcs.region.testCache1=JDBC_1
jcs.region.testCache1.cacheattributes.MaxObjects=10000

# #############################################################
# ################# AUXILIARY CACHES AVAILABLE ################
# JDBC disk cache
jcs.auxiliary.JDBC_0=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheFactory
jcs.auxiliary.JDBC_0.attributes=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheAttributes
jcs.auxiliary.JDBC_0.attributes.tableName=JCS_STORE_0
jcs.auxiliary.JDBC_0.attributes.testBeforeInsert=false
jcs.auxiliary.JDBC_0.attributes.allowRemoveAll=true
jcs.auxiliary.JDBC_0.attributes.MaxPurgatorySize=10000000
jcs.auxiliary.JDBC_0.attributes.connectionPoolName=MySharedPool
jcs.auxiliary.JDBC_0.attributes.EventQueueType=POOLED
jcs.auxiliary.JDBC_0.attributes.EventQueuePoolName=disk_cache_event_queue

jcs.auxiliary.JDBC_1=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheFactory
jcs.auxiliary.JDBC_1.attributes=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheAttributes
jcs.auxiliary.JDBC_1.attributes.tableName=JCS_STORE_1
jcs.auxiliary.JDBC_1.attributes.testBeforeInsert=false
jcs.auxiliary.JDBC_1.attributes.allowRemoveAll=true
jcs.auxiliary.JDBC_1.attributes.MaxPurgatorySize=10000000
jcs.auxiliary.JDBC_1.attributes.connectionPoolName=MySharedPool
jcs.auxiliary.JDBC_1.attributes.EventQueueType=POOLED
jcs.auxiliary.JDBC_1.attributes.EventQueuePoolName=disk_cache_event_queue

# #############################################################
# ######## OPTIONAL SHARED CONNECTION POOL CONFIGURATION ######
# My Shared Pool

jcs.jdbcconnectionpool.MySharedPool.attributes.userName=sa
jcs.jdbcconnectionpool.MySharedPool.attributes.password=
jcs.jdbcconnectionpool.MySharedPool.attributes.url=jdbc:hsqldb:target/cache_hsql_db
jcs.jdbcconnectionpool.MySharedPool.attributes.driverClassName=org.hsqldb.jdbcDriver
jcs.jdbcconnectionpool.MySharedPool.attributes.maxActive=15

# #############################################################
# ################# OPTIONAL THREAD POOL CONFIGURATION #########
# Disk Cache pool
thread_pool.disk_cache_event_queue.useBoundary=false
thread_pool.disk_cache_event_queue.boundarySize=500
thread_pool.disk_cache_event_queue.maximumPoolSize=15
thread_pool.disk_cache_event_queue.minimumPoolSize=10
thread_pool.disk_cache_event_queue.keepAliveTime=3500
thread_pool.disk_cache_event_queue.whenBlockedPolicy=RUN
thread_pool.disk_cache_event_queue.startUpSize=10
        
				

Example #3 cache.ccf (JNDI)

This example uses the two JDBC Disk Cache instances from the example above and a JNDI data source.

                    
jcs.default=JDBC_0
jcs.default.cacheattributes=org.apache.commons.jcs3.engine.CompositeCacheAttributes
jcs.default.cacheattributes.MaxObjects=100
jcs.default.cacheattributes.MemoryCacheName=org.apache.commons.jcs3.engine.memory.lru.LRUMemoryCache
jcs.default.cacheattributes.UseMemoryShrinker=false
jcs.default.cacheattributes.MaxMemoryIdleTimeSeconds=3600
jcs.default.cacheattributes.ShrinkerIntervalSeconds=60
jcs.default.elementattributes=org.apache.commons.jcs3.engine.ElementAttributes
jcs.default.elementattributes.IsEternal=false
jcs.default.elementattributes.MaxLife=700
jcs.default.elementattributes.IdleTime=1800
jcs.default.elementattributes.IsSpool=true
jcs.default.elementattributes.IsRemote=true
jcs.default.elementattributes.IsLateral=true

# #############################################################
# ################# CONFIGURED REGIONS ########################

jcs.region.testCache1=JDBC_1
jcs.region.testCache1.cacheattributes.MaxObjects=10000

# #############################################################
# ################# AUXILIARY CACHES AVAILABLE ################
# JDBC disk cache
jcs.auxiliary.JDBC_0=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheFactory
jcs.auxiliary.JDBC_0.attributes=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheAttributes
jcs.auxiliary.JDBC_0.attributes.tableName=JCS_STORE_0
jcs.auxiliary.JDBC_0.attributes.testBeforeInsert=false
jcs.auxiliary.JDBC_0.attributes.allowRemoveAll=true
jcs.auxiliary.JDBC_0.attributes.MaxPurgatorySize=10000000
jcs.auxiliary.JDBC_0.attributes.connectionPoolName=JNDI
jcs.auxiliary.JDBC_0.attributes.EventQueueType=POOLED
jcs.auxiliary.JDBC_0.attributes.EventQueuePoolName=disk_cache_event_queue

jcs.auxiliary.JDBC_1=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheFactory
jcs.auxiliary.JDBC_1.attributes=org.apache.commons.jcs3.auxiliary.disk.jdbc.JDBCDiskCacheAttributes
jcs.auxiliary.JDBC_1.attributes.tableName=JCS_STORE_1
jcs.auxiliary.JDBC_1.attributes.testBeforeInsert=false
jcs.auxiliary.JDBC_1.attributes.allowRemoveAll=true
jcs.auxiliary.JDBC_1.attributes.MaxPurgatorySize=10000000
jcs.auxiliary.JDBC_1.attributes.connectionPoolName=JNDI
jcs.auxiliary.JDBC_1.attributes.EventQueueType=POOLED
jcs.auxiliary.JDBC_1.attributes.EventQueuePoolName=disk_cache_event_queue

# #############################################################
# ######## OPTIONAL JNDI CONNECTION POOL CONFIGURATION ######
# JNDI Pool

jcs.jdbcconnectionpool.JNDI.attributes.jndiPath=java:comp/env/jdbc/MyDB
jcs.jdbcconnectionpool.JNDI.attributes.jndiTTL=300000

# #############################################################
# ################# OPTIONAL THREAD POOL CONFIGURATION #########
# Disk Cache pool
thread_pool.disk_cache_event_queue.useBoundary=false
thread_pool.disk_cache_event_queue.boundarySize=500
thread_pool.disk_cache_event_queue.maximumPoolSize=15
thread_pool.disk_cache_event_queue.minimumPoolSize=10
thread_pool.disk_cache_event_queue.keepAliveTime=3500
thread_pool.disk_cache_event_queue.whenBlockedPolicy=RUN
thread_pool.disk_cache_event_queue.startUpSize=10
        
                

Table Creation Script (MySQL)

					
drop TABLE JCS_STORE;

CREATE TABLE JCS_STORE
(
  CACHE_KEY                   VARCHAR(250)          NOT NULL,
  REGION                      VARCHAR(250)          NOT NULL,
  ELEMENT                     BLOB,
  CREATE_TIME                 DATETIME,
  CREATE_TIME_SECONDS         BIGINT,
  MAX_LIFE_SECONDS            BIGINT,
  SYSTEM_EXPIRE_TIME_SECONDS  BIGINT,
  IS_ETERNAL                  CHAR(1),
  PRIMARY KEY (CACHE_KEY, REGION)
);

alter table JCS_STORE MAX_ROWS = 10000000;

alter table JCS_STORE AVG_ROW_LENGTH = 2100;

create index JCS_STORE_DELETE_IDX on JCS_STORE (SYSTEM_EXPIRE_TIME_SECONDS,IS_ETERNAL,REGION);