Infinispan SQL cache store v7.0.2

Cache loader / writer for an SQL database backend.

Requirements

  • Infinispan 14+
  • Java 11+
  • Supported SQL databases: MySQL, PostgreSQL 9.5+, MS SQL Server 2016+, H2

Features

  • Implements the complete AdvancedLoadWriteStore SPI
  • Provides an interface for transforming Infinispan entries to / from SQL records, with SQL dialect awareness
  • Provides an optional interface for executing arbitrary SQL queries against the database, bypassing the standard Infinispan load store API
  • Provides an optional interface to facilitate table add, modify and drop column changes when the SQL store is started
  • Hikari SQL connection pool
  • System property interpolation for all configuration properties using a ${sys-prop-name:default-value} format
  • Multi-level logging via Log4j2
  • Open source (Apache 2.0 license)

Usage

  1. Add the Maven dependency for the SQL cache store, or make sure its JAR is present in the CLASSPATH of your project.
  2. Implement SQLRecordTransformer to translate between Infinispan entries (key / value pairs with optional metadata) and SQL records.
  3. Configure an SQL store for each Infinispan cache that requires one, by setting the properties specified in SQLStoreConfiguration. Also, see the example below. Note that the SQL store can safely shared between multiple replicated / distributed instances of a cache. It can also be used in read-only mode.

Maven

Maven coordinates:

    <groupId>com.nimbusds</groupId>
    <artifactId>infinispan-cachestore-sql</artifactId>
    <version>[ version ]</version>

where [ version ] should be the latest stable version.

Example configuration

Example Infinispan configuration for a cache backed by an H2 SQL database:

<infinispan xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="urn:infinispan:config:14.0 http://www.infinispan.org/schemas/infinispan-config-14.0.xsd"
            xmlns="urn:infinispan:config:14.0"
            xmlns:sql="urn:infinispan:config:store:sql:3.0">

    <cache-container name="myCacheContainer" default-cache="myMap" statistics="true">
        <jmx duplicate-domains="true"/>
        <local-cache name="myMap">
            <eviction type="COUNT" size="100"/>
            <persistence passivation="false">
                <sql:sql-store
                           shared="true"
                           segmented="false"
                           record-transformer="com.nimbusds.infinispan.persistence.sql.UserRecordTransformer"
                           sql-dialect="H2"
                           create-table-if-missing="true">

                    <property name="jdbcUrl">jdbc:h2:mem:test;DATABASE_TO_UPPER=false</property>
                    <property name="username">admin</property>
                    <property name="password">secret</property>

                </sql:sql-store>
            </persistence>
        </local-cache>
    </cache-container>

</infinispan>
Packages 
Package Description
com.nimbusds.infinispan.persistence.sql
SQL store for Infinispan 8.2+ caches and maps.
com.nimbusds.infinispan.persistence.sql.config
Configuration classes.
com.nimbusds.infinispan.persistence.sql.query
SQL query executor interfaces.
com.nimbusds.infinispan.persistence.sql.transformers
Transformation utilities for Java collections and other complex classes.