[[jdbc_cache_store]]
=== JDBC String based Cache Store
A cache store which relies on the provided JDBC driver to load/store values in the underlying database.

Each key in the cache is stored in its own row in the database. In order to store each key in its own row, this store relies
on a (pluggable) bijection that maps the each key to a String object. The bijection is defined by the Key2StringMapper interface.
{brandname}s ships a default implementation (smartly named DefaultTwoWayKey2StringMapper) that knows how to handle primitive types.

[NOTE]
====
By default {brandname} shares are not stored, meaning that all nodes in the cluster will write to the underlying store upon each update.
If you wish for an operation to only be written to the underlying database once, you must configure the JDBC store to be shared.
====

//Exclude segmentation from productized docs.
ifndef::productized[]
[NOTE]
====
The JDBC string-based cache store does not support segmentation. Support will
be available in a future release.
====
endif::productized[]

==== Connection management (pooling)
In order to obtain a connection to the database the JDBC cache store relies on a link:{javadocroot}/org/infinispan/persistence/jdbc/connectionfactory/ConnectionFactory.html[ConnectionFactory]
implementation. The connection factory is specified programmatically using one of the connectionPool(), dataSource()
or simpleConnection() methods on the JdbcStringBasedStoreConfigurationBuilder class or declaratively using one of the
`<connectionPool />`, `<dataSource />` or `<simpleConnection />` elements.

{brandname} ships with three ConnectionFactory implementations:

*  link:{javadocroot}/org/infinispan/persistence/jdbc/configuration/PooledConnectionFactoryConfigurationBuilder.html[PooledConnectionFactoryConfigurationBuilder]
is a factory based on link:https://github.com/brettwooldridge/HikariCP[HikariCP]. Additional properties for HikariCP can
be provided by a properties file, either via placing a `hikari.properties` file on the classpath or by specifying the
path to the file via `PooledConnectionFactoryConfiguration.propertyFile` or `properties-file` in the connection pool's
xml config. N.B. a properties file specified explicitly in the configuration is loaded instead of the `hikari.properties`
file on the class path and Connection pool characteristics which are explicitly set in PooledConnectionFactoryConfiguration
always override the values loaded from a properties file.

Refer to the official link:https://github.com/brettwooldridge/HikariCP[documentation] for details of all configuration properties.

*  link:{javadocroot}/org/infinispan/persistence/jdbc/configuration/ManagedConnectionFactoryConfigurationBuilder.html[ManagedConnectionFactoryConfigurationBuilder]
is a connection factory that can be used within managed environments, such as application servers. It knows how to look
into the JNDI tree at a certain location (configurable) and delegate connection management to the DataSource.

*  link:{javadocroot}/org/infinispan/persistence/jdbc/configuration/SimpleConnectionFactoryConfigurationBuilder.html[SimpleConnectionFactoryConfigurationBuilder]
is a factory implementation that will create database connection on a per invocation basis. Not recommended in production.

The `PooledConnectionFactory` is generally recommended for stand-alone deployments (i.e. not running within AS or servlet container).
`ManagedConnectionFactory` can be used when running in a managed environment where a `DataSource` is present, so that
connection pooling is performed within the `DataSource`.

==== Sample configurations

Below is a sample configuration for the link:{javadocroot}/org/infinispan/persistence/jdbc/stringbased/JdbcStringBasedStore.html[JdbcStringBasedStore].
For detailed description of all the parameters used refer to the link:{javadocroot}/org/infinispan/persistence/jdbc/stringbased/JdbcStringBasedStore.html[JdbcStringBasedStore].

[source,xml]
----
<persistence>
   <string-keyed-jdbc-store xmlns="urn:infinispan:config:store:jdbc:9.2" shared="true" fetch-state="false" read-only="false" purge="false">
      <connection-pool connection-url="jdbc:h2:mem:infinispan_string_based;DB_CLOSE_DELAY=-1" username="sa" driver="org.h2.Driver"/>
      <string-keyed-table drop-on-exit="true" create-on-start="true" prefix="ISPN_STRING_TABLE">
         <id-column name="ID_COLUMN" type="VARCHAR(255)" />
         <data-column name="DATA_COLUMN" type="BINARY" />
         <timestamp-column name="TIMESTAMP_COLUMN" type="BIGINT" />
      </string-keyed-table>
   </string-keyed-jdbc-store>
</persistence>

----

[source,java]
----

ConfigurationBuilder builder = new ConfigurationBuilder();
builder.persistence().addStore(JdbcStringBasedStoreConfigurationBuilder.class)
      .fetchPersistentState(false)
      .ignoreModifications(false)
      .purgeOnStartup(false)
      .shared(true)
      .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_string_based;DB_CLOSE_DELAY=-1")
         .username("sa")
         .driverClass("org.h2.Driver");

----

Finally, below is an example of a JDBC cache store with a managed connection factory, which is chosen implicitly by specifying a datasource JNDI location:

[source,xml]
----

<string-keyed-jdbc-store xmlns="urn:infinispan:config:store:jdbc:9.2" shared="true" fetch-state="false" read-only="false" purge="false">
   <data-source jndi-url="java:/StringStoreWithManagedConnectionTest/DS" />
   <string-keyed-table drop-on-exit="true" create-on-start="true" prefix="ISPN_STRING_TABLE">
      <id-column name="ID_COLUMN" type="VARCHAR(255)" />
      <data-column name="DATA_COLUMN" type="BINARY" />
      <timestamp-column name="TIMESTAMP_COLUMN" type="BIGINT" />
   </string-keyed-table>
</string-keyed-jdbc-store>

----

[source,java]
----

ConfigurationBuilder builder = new ConfigurationBuilder();
builder.persistence().addStore(JdbcStringBasedStoreConfigurationBuilder.class)
      .fetchPersistentState(false)
      .ignoreModifications(false)
      .purgeOnStartup(false)
      .shared(true)
      .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:/StringStoreWithManagedConnectionTest/DS");

----

.Apache Derby users
NOTE: If you're connecting to an Apache Derby database, make sure you set dataColumnType to BLOB: `<data-column name="DATA_COLUMN" type="BLOB"/>`
