SQL Store Persistence

What You Will Learn

How to configure SQL table and query stores so that Infinispan caches load and persist data from a relational database. You will define Protobuf entities with @Proto annotations and register the generated schema on the server.

Prerequisites

Java 17+
An Infinispan Server running on localhost:11222 (or Docker/Podman available for Testcontainers)

Step 1: Define the Data Model

Define your entities as Java records with @Proto annotations. For example, the Author entity:

@Proto
public record Author(int id, String isbn, String name, String country) {}

The Book entity follows the same pattern with fields like isbn, title, authorName, and country. A @ProtoSchema interface generates the Protobuf schema for both entities.

Step 2: Configure the SQL Table Store

Define the cache with a table-jdbc-store that maps directly to a database table:

<distributed-cache name="sqlTableCache">
    <encoding media-type="application/x-protostream"/>
    <persistence>
        <table-jdbc-store
                read-only="true"
                shared="false"
                dialect="H2"
                table-name="AUTHORS">
            <connection-pool connection-url="jdbc:h2:tcp://localhost:9123/~/example;DB_CLOSE_DELAY=-1"
                             username="infinispan"
                             password="secret"
                             driver="org.h2.Driver"/>
            <schema message-name="Author" package="library" embedded-key="true"/>
        </table-jdbc-store>
    </persistence>
</distributed-cache>

Step 3: Connect, Register the Schema, and Read Data

      ConfigurationBuilder configurationBuilder = TutorialsConnectorHelper.connectionConfig();
      configurationBuilder.addContextInitializer(new TechLibrarySchemaImpl());
      URI authorsCacheURI = SQLStoreLibraryMain.class.getClassLoader().getResource("sqlTableCache.xml").toURI();
      URI booksCacheURI = SQLStoreLibraryMain.class.getClassLoader().getResource("sqlQueryCache.xml").toURI();
      configurationBuilder.remoteCache(AUTHORS_CACHE).configurationURI(authorsCacheURI);
      configurationBuilder.remoteCache(BOOKS_CACHE).configurationURI(booksCacheURI);

      RemoteCacheManager cacheManager = TutorialsConnectorHelper.connect(configurationBuilder);

      ProtostreamSchemaUploader schemaUploader = new ProtostreamSchemaUploader(cacheManager);
      System.out.println("Register proto schema in the server...");
      schemaUploader.registerSchema();

The caches automatically load data from the database tables. You can read them with cacheManager.getCache(cacheName).values().

Step 4: Run the Tutorial

mvn package exec:java

The tutorial starts an embedded H2 database, populates it, and displays the cached data loaded from the SQL store.

What’s Next

Getting started with basic cache operations
Query your data with Ickle queries