18.6. JDBC Based Cache Stores
JdbcBinaryStore
.JdbcStringBasedStore
.JdbcMixedStore
.
18.6.1. 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.1.1. JdbcBinaryStore Configuration (Remote Client-Server Mode)
JdbcBinaryStore
using Red Hat JBoss Data Grid's Remote Client-Server mode with Passivation enabled:
<local-cache> ... <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.1.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"> ... <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.1.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)
Create a New Configuration Builder
Use theConfigurationBuilder
to create a new configuration object.Add the
JdbcBinaryStoreConfigurationBuilder
Add theJdbcBinaryStore
configuration builder to build a specific configuration related to this store.Set Up Persistence
fetchPersistentState
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.Set Modifications
ignoreModifications
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.Configure Purging
purgeOnStartup
specifies whether the cache is purged when initially started.Configure the Table
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
TheconnectionPool
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.2. 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.2.1. JdbcStringBasedStore Configuration (Remote Client-Server Mode)
JdbcStringBasedStore
for Red Hat JBoss Data Grid's Remote Client-Server mode:
<local-cache> ... <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.2.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"> ... <persistence> <stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:6.0" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false" key2StringMapper="org.infinispan.loaders.keymappers.DefaultTwoWayKey2StringMapper"> <connectionPool connectionUrl="jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1" username="sa" driverClass="org.h2.Driver"/> <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.2.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 ... > ... <replicated-cache> ... <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.2.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") .connectionPool() .connectionUrl("jdbc:h2:mem:infinispan_binary_based;DB_CLOSE_DELAY=-1") .username("sa") .driverClass("org.h2.Driver");
Procedure 18.5. Configure the JdbcStringBasedStore Programmatically
Create a New Configuration Builder
Use theConfigurationBuilder
to create a new configuration object.Add
JdbcStringBasedStoreConfigurationBuilder
Add theJdbcStringBasedStore
configuration builder to build a specific configuration related to this store.Set Up Persistence
fetchPersistentState
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.Set Modifications
ignoreModifications
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.Configure Purging
purgeOnStartup
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.idColumnName
TheidColumnName
property defines the column where the cache key or bucket ID is stored.dataColumnName
ThedataColumnName
property specifies the column where the cache entry or bucket is stored.timestampColumnName
ThetimestampColumnName
element specifies the column where the time stamp of the cache entry or bucket is stored.
The connectionPool Element
TheconnectionPool
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.3. JdbcMixedStores
JdbcMixedStore
is a hybrid implementation that delegates keys based on their type to either the JdbcBinaryStore
or JdbcStringBasedStore
.
18.6.3.1. JdbcMixedStore Configuration (Remote Client-Server Mode)
JdbcMixedStore
for Red Hat JBoss Data Grid's Remote Client-Server mode:
<local-cache> <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.3.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"> ... <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.3.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
Create a New Configuration Builder
Use theConfigurationBuilder
to create a new configuration object.Add
JdbcMixedStoreConfigurationBuilder
Add theJdbcMixedStore
configuration builder to build a specific configuration related to this store.Set Up Persistence
fetchPersistentState
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 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.Set Modifications
ignoreModifications
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.Configure Purging
purgeOnStartup
specifies whether the cache is purged when initially started.Configure the Table
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
TheconnectionPool
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.4. Cache Store Troubleshooting
18.6.4.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.