18.6. JDBC Based Cache Stores
JdbcBinaryStore
.JdbcStringBasedStore
.JdbcMixedStore
.
18.6.1. JDBC Datasource Configuration
- connectionPool
- dataSource
- simpleConnection
A connectionPool
uses a JDBC driver to establish a connection, along with creating a pool of threads which may be used for any requests. This configuration is typically used in stand-alone environments that require ready access to a database without having the overhead of creating the database connection on each invocation.
A dataSource
is a connection factory that understands how to reference a JNDI tree and delegate connections to the datasource. This configuration is typically used in environments where a datasource has already been configured outside of JBoss Data Grid.
The simpleConnection
is similar to connectionPool
in that it uses a JDBC driver to establish each connection; however, instead of creating a pool of threads that are available a new connection is created on each invocation. This configuration is typically used in test environments, or where only a single connection is required.
<connectionPool />
, <dataSource />
, or <simpleConnection />
elements. Alternatively, these may be configured programmatically with the connectionPool()
, dataSource()
, or simpleConnection()
methods on the JdbcBinaryStoreConfigurationBuilde
, JdbcMixedStoreConfigurationBuilder
, or JdbcBinaryStoreConfigurationBuilder
classes.
18.6.2. JdbcBinaryStores
JdbcBinaryStore
supports all key types. It stores all keys with the same hash value (hashCode
method on the key) in the same table row/blob. The hash value common to the included keys is set as the primary key for the table row/blob. As a result of this hash value, JdbcBinaryStore
offers excellent flexibility but at the cost of concurrency and throughput.
k1
, k2
and k3
) have the same hash code, they are stored in the same table row. If three different threads attempt to concurrently update k1
, k2
and k3
, they must do it sequentially because all three keys share the same row and therefore cannot be simultaneously updated.
18.6.2.1. JdbcBinaryStore Configuration (Remote Client-Server Mode)
JdbcBinaryStore
using Red Hat JBoss Data Grid's Remote Client-Server mode with Passivation enabled:
<local-cache name="customCache"> <!-- Additional configuration elements here --> <binary-keyed-jdbc-store datasource="java:jboss/datasources/JdbcDS" passivation="${true/false}" preload="${true/false}" purge="${true/false}"> <binary-keyed-table prefix="JDG"> <id-column name="id" type="${id.column.type}"/> <data-column name="datum" type="${data.column.type}"/> <timestamp-column name="version" type="${timestamp.column.type}"/> </binary-keyed-table> </binary-keyed-jdbc-store> </local-cache>
18.6.2.2. JdbcBinaryStore Configuration (Library Mode)
JdbcBinaryStore
:
<infinispan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:infinispan:config:6.0 http://www.infinispan.org/schemas/infinispan-config-6.0.xsd urn:infinispan:config:jdbc:6.0 http://www.infinispan.org/schemas/infinispan-cachestore-jdbc-config-6.0.xsd" xmlns="urn:infinispan:config:6.0"> <!-- Additional configuration elements here --> <persistence> <binaryKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:6.0" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false"> <connectionPool connectionUrl="jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1" username="sa" driverClass="org.h2.Driver"/> <binaryKeyedTable dropOnExit="true" createOnStart="true" prefix="ISPN_BUCKET_TABLE"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BINARY" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </binaryKeyedTable> </binaryKeyedJdbcStore> </persistence>
18.6.2.3. JdbcBinaryStore Programmatic Configuration
JdbcBinaryStore
:
ConfigurationBuilder builder = new ConfigurationBuilder(); builder.persistence() .addStore(JdbcBinaryStoreConfigurationBuilder.class) .fetchPersistentState(false) .ignoreModifications(false) .purgeOnStartup(false) .table() .dropOnExit(true) .createOnStart(true) .tableNamePrefix("ISPN_BUCKET_TABLE") .idColumnName("ID_COLUMN").idColumnType("VARCHAR(255)") .dataColumnName("DATA_COLUMN").dataColumnType("BINARY") .timestampColumnName("TIMESTAMP_COLUMN").timestampColumnType("BIGINT") .connectionPool() .connectionUrl("jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1") .username("sa") .driverClass("org.h2.Driver");
Procedure 18.4. JdbcBinaryStore Programmatic Configuration (Library Mode)
- Use the
ConfigurationBuilder
to create a new configuration object. - Add the
JdbcBinaryStore
configuration builder to build a specific configuration related to this store. - The
fetchPersistentState
element determines whether or not to fetch the persistent state of a cache and apply it to the local cache store when joining the cluster. If the cache store is shared the fetch persistent state is ignored, as caches access the same cache store. A configuration exception will be thrown when starting the cache service if more than one cache loader has this property set totrue
. ThefetchPersistentState
property isfalse
by default. - The
ignoreModifications
element determines whether write methods are pushed to the specific cache loader by allowing write operations to the local file cache loader, but not the shared cache loader. In some cases, transient application data should only reside in a file-based cache loader on the same server as the in-memory cache. For example, this would apply with a further JDBC based cache loader used by all servers in the network.ignoreModifications
isfalse
by default. - The
purgeOnStartup
element specifies whether the cache is purged when initially started. - Configure the table as follows:
dropOnExit
determines if the table will be dropped when the cache store is stopped. This is set tofalse
by default.createOnStart
creates the table when starting the cache store if no table currently exists. This method istrue
by default.tableNamePrefix
sets the prefix for the name of the table in which the data will be stored.- The
idColumnName
property defines the column where the cache key or bucket ID is stored. - The
dataColumnName
property specifies the column where the cache entry or bucket is stored. - The
timestampColumnName
element specifies the column where the time stamp of the cache entry or bucket is stored.
- The
connectionPool
element specifies a connection pool for the JDBC driver using the following parameters:- The
connectionUrl
parameter specifies the JDBC driver-specific connection URL. - The
username
parameter contains the user name used to connect via theconnectionUrl
. - The
driverClass
parameter specifies the class name of the driver used to connect to the database.
Note
18.6.3. JdbcStringBasedStores
JdbcStringBasedStore
stores each entry in its own row in the table, instead of grouping multiple entries into each row, resulting in increased throughput under a concurrent load. It also uses a (pluggable) bijection that maps each key to a String
object. The Key2StringMapper
interface defines the bijection.
DefaultTwoWayKey2StringMapper
that handles primitive types.
18.6.3.1. JdbcStringBasedStore Configuration (Remote Client-Server Mode)
JdbcStringBasedStore
for Red Hat JBoss Data Grid's Remote Client-Server mode:
<local-cache name="customCache"> <!-- Additional configuration elements here --> <string-keyed-jdbc-store datasource="java:jboss/datasources/JdbcDS" passivation="true" preload="false" purge="false" shared="false" singleton="true"> <string-keyed-table prefix="JDG"> <id-column name="id" type="${id.column.type}"/> <data-column name="datum" type="${data.column.type}"/> <timestamp-column name="version" type="${timestamp.column.type}"/> </string-keyed-table> </string-keyed-jdbc-store> </local-cache>
18.6.3.2. JdbcStringBasedStore Configuration (Library Mode)
JdbcStringBasedStore
:
<infinispan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:infinispan:config:6.0 http://www.infinispan.org/schemas/infinispan-config-6.0.xsd urn:infinispan:config:jdbc:6.0 http://www.infinispan.org/schemas/infinispan-cachestore-jdbc-config-6.0.xsd" xmlns="urn:infinispan:config:6.0"> <!-- Additional configuration elements here --> <persistence> <stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:6.0" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false" key2StringMapper="org.infinispan.loaders.keymappers.DefaultTwoWayKey2StringMapper"> <dataSource jndiUrl="java:jboss/datasources/JdbcDS"/> <stringKeyedTable dropOnExit="true" createOnStart="true" prefix="ISPN_STRING_TABLE"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BINARY" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </stringKeyedTable> </stringKeyedJdbcStore> </persistence>
18.6.3.3. JdbcStringBasedStore Multiple Node Configuration (Remote Client-Server Mode)
JdbcStringBasedStore
in Red Hat JBoss Data Grid's Remote Client-Server mode. This configuration is used when multiple nodes must be used.
<subsystem xmlns="urn:infinispan:server:core:6.1" default-cache-container="default"> <cache-container <!-- Additional configuration information here --> > <!-- Additional configuration elements here --> <replicated-cache> <!-- Additional configuration elements here --> <string-keyed-jdbc-store datasource="java:jboss/datasources/JdbcDS" fetch-state="true" passivation="false" preload="false" purge="false" shared="false" singleton="true"> <string-keyed-table prefix="JDG"> <id-column name="id" type="${id.column.type}"/> <data-column name="datum" type="${data.column.type}"/> <timestamp-column name="version" type="${timestamp.column.type}"/> </string-keyed-table> </string-keyed-jdbc-store> </replicated-cache> </cache-container> </subsystem>
18.6.3.4. JdbcStringBasedStore Programmatic Configuration
JdbcStringBasedStore
:
ConfigurationBuilder builder = new ConfigurationBuilder(); builder.persistence().addStore(JdbcStringBasedStoreConfigurationBuilder.class) .fetchPersistentState(false) .ignoreModifications(false) .purgeOnStartup(false) .table() .dropOnExit(true) .createOnStart(true) .tableNamePrefix("ISPN_STRING_TABLE") .idColumnName("ID_COLUMN").idColumnType("VARCHAR(255)") .dataColumnName("DATA_COLUMN").dataColumnType("BINARY") .timestampColumnName("TIMESTAMP_COLUMN").timestampColumnType("BIGINT") .dataSource() .jndiUrl("java:jboss/datasources/JdbcDS");
Procedure 18.5. Configure the JdbcStringBasedStore Programmatically
- Use the
ConfigurationBuilder
to create a new configuration object. - Add the
JdbcStringBasedStore
configuration builder to build a specific configuration related to this store. - The
fetchPersistentState
parameter determines whether or not to fetch the persistent state of a cache and apply it to the local cache store when joining the cluster. If the cache store is shared the fetch persistent state is ignored, as caches access the same cache store. A configuration exception will be thrown when starting the cache service if more than one cache loader has this property set totrue
. ThefetchPersistentState
property isfalse
by default. - The
ignoreModifications
parameter determines whether write methods are pushed to the specific cache loader by allowing write operations to the local file cache loader, but not the shared cache loader. In some cases, transient application data should only reside in a file-based cache loader on the same server as the in-memory cache. For example, this would apply with a further JDBC based cache loader used by all servers in the network.ignoreModifications
isfalse
by default. - The
purgeOnStartup
parameter specifies whether the cache is purged when initially started. - Configure the Table
dropOnExit
determines if the table will be dropped when the cache store is stopped. This is set tofalse
by default.createOnStart
creates the table when starting the cache store if no table currently exists. This method istrue
by default.tableNamePrefix
sets the prefix for the name of the table in which the data will be stored.- The
idColumnName
property defines the column where the cache key or bucket ID is stored. - The
dataColumnName
property specifies the column where the cache entry or bucket is stored. - The
timestampColumnName
element specifies the column where the time stamp of the cache entry or bucket is stored.
- The
dataSource
element specifies a data source using the following parameters:- The
jndiUrl
specifies the JNDI URL to the existing JDBC.
Note
18.6.4. JdbcMixedStores
JdbcMixedStore
is a hybrid implementation that delegates keys based on their type to either the JdbcBinaryStore
or JdbcStringBasedStore
.
18.6.4.1. JdbcMixedStore Configuration (Remote Client-Server Mode)
JdbcMixedStore
for Red Hat JBoss Data Grid's Remote Client-Server mode:
<local-cache name="customCache"> <mixed-keyed-jdbc-store datasource="java:jboss/datasources/JdbcDS" passivation="true" preload="false" purge="false"> <binary-keyed-table prefix="MIX_BKT2"> <id-column name="id" type="${id.column.type}"/> <data-column name="datum" type="${data.column.type}"/> <timestamp-column name="version" type="${timestamp.column.type}"/> </binary-keyed-table> <string-keyed-table prefix="MIX_STR2"> <id-column name="id" type="${id.column.type}"/> <data-column name="datum" type="${data.column.type}"/> <timestamp-column name="version" type="${timestamp.column.type}"/> </string-keyed-table> </mixed-keyed-jdbc-store> </local-cache>
18.6.4.2. JdbcMixedStore Configuration (Library Mode)
mixedKeyedJdbcStore
:
<infinispan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:infinispan:config:6.0 http://www.infinispan.org/schemas/infinispan-config-6.0.xsd urn:infinispan:config:jdbc:6.0 http://www.infinispan.org/schemas/infinispan-cachestore-jdbc-config-6.0.xsd" xmlns="urn:infinispan:config:6.0"> <!-- Additional configuration elements here --> <persistence> <mixedKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:6.0" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false" key2StringMapper="org.infinispan.persistence.keymappers.DefaultTwoWayKey2StringMapper"> <connectionPool connectionUrl="jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1" username="sa" driverClass="org.h2.Driver"/> <binaryKeyedTable dropOnExit="true" createOnStart="true" prefix="ISPN_BUCKET_TABLE_BINARY"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BINARY" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </binaryKeyedTable> <stringKeyedTable dropOnExit="true" createOnStart="true" prefix="ISPN_BUCKET_TABLE_STRING"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BINARY" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </stringKeyedTable> </mixedKeyedJdbcStore> </persistence>
18.6.4.3. JdbcMixedStore Programmatic Configuration
JdbcMixedStore
:
ConfigurationBuilder builder = new ConfigurationBuilder(); builder.persistence().addStore(JdbcMixedStoreConfigurationBuilder.class) .fetchPersistentState(false) .ignoreModifications(false) .purgeOnStartup(false) .stringTable() .dropOnExit(true) .createOnStart(true) .tableNamePrefix("ISPN_MIXED_STR_TABLE") .idColumnName("ID_COLUMN").idColumnType("VARCHAR(255)") .dataColumnName("DATA_COLUMN").dataColumnType("BINARY") .timestampColumnName("TIMESTAMP_COLUMN").timestampColumnType("BIGINT") .binaryTable() .dropOnExit(true) .createOnStart(true) .tableNamePrefix("ISPN_MIXED_BINARY_TABLE") .idColumnName("ID_COLUMN").idColumnType("VARCHAR(255)") .dataColumnName("DATA_COLUMN").dataColumnType("BINARY") .timestampColumnName("TIMESTAMP_COLUMN").timestampColumnType("BIGINT") .connectionPool() .connectionUrl("jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1") .username("sa") .driverClass("org.h2.Driver");
Procedure 18.6. Configure JdbcMixedStore Programmatically
- Use the
ConfigurationBuilder
to create a new configuration object. - Add the
JdbcMixedStore
configuration builder to build a specific configuration related to this store. - The
fetchPersistentState
parameter determines whether or not to fetch the persistent state of a cache and apply it to the local cache store when joining the cluster. If the cache store is shared the fetch persistent state is ignored, as caches access the same cache store. A configuration exception will be thrown when starting the cache service if more than one cache loader has this property set totrue
. ThefetchPersistentState
property isfalse
by default. - The
ignoreModifications
parameter determines whether write methods are pushed to the specific cache loader by allowing write operations to the local file cache loader, but not the shared cache loader. In some cases, transient application data should only reside in a file-based cache loader on the same server as the in-memory cache. For example, this would apply with a further JDBC based cache loader used by all servers in the network.ignoreModifications
isfalse
by default. - The
purgeOnStartup
parameter specifies whether the cache is purged when initially started. - Configure the table as follows:
dropOnExit
determines if the table will be dropped when the cache store is stopped. This is set tofalse
by default.createOnStart
creates the table when starting the cache store if no table currently exists. This method istrue
by default.tableNamePrefix
sets the prefix for the name of the table in which the data will be stored.- The
idColumnName
property defines the column where the cache key or bucket ID is stored. - The
dataColumnName
property specifies the column where the cache entry or bucket is stored. - The
timestampColumnName
element specifies the column where the time stamp of the cache entry or bucket is stored.
- The
connectionPool
element specifies a connection pool for the JDBC driver using the following parameters:- The
connectionUrl
parameter specifies the JDBC driver-specific connection URL. - The
username
parameter contains the username used to connect via theconnectionUrl
. - The
driverClass
parameter specifies the class name of the driver used to connect to the database.
Note
18.6.5. Cache Store Troubleshooting
18.6.5.1. IOExceptions with JdbcStringBasedStore
JdbcStringBasedStore
indicates that your data column type is set to VARCHAR
, CLOB
or something similar instead of the correct type, BLOB
or VARBINARY
. Despite its name, JdbcStringBasedStore
only requires that the keys are strings while the values can be any data type, so that they can be stored in a binary column.