6.10.3. 使用 SQL 查询来加载数据并执行操作

SQL 查询缓存存储使您可以从多个数据库表加载缓存,包括数据库表中的子列,并执行插入、更新和删除操作。

先决条件

  • 具有 JDBC 连接详情。
    您可以将 JDBC 连接工厂直接添加到您的缓存配置中。
    对于生产环境中的远程缓存,您应该将受管数据源添加到 Data Grid Server 配置中,并在缓存配置中指定 JNDI 名称。
  • 为任何复合密钥或复合值生成 Protobuf 模式,并使用 Data Grid 注册您的模式。

    提示

    数据网格建议使用 Protobuf 处理器生成 Protobuf 模式。对于远程缓存,您可以通过数据网格控制台、CLI 或 REST API 来添加模式来注册模式。

流程

  1. 在 Data Grid 部署中添加数据库驱动程序。

    • 远程缓存:将数据库驱动程序复制到 Data Grid 服务器安装的 server/lib 目录中。
    • 嵌入式缓存:将 infinispan-cachestore-sql 依赖关系添加到您的 pom 文件,并确保数据库驱动程序在您的应用程序类路径上。

      <dependency>
        <groupId>org.infinispan</groupId>
        <artifactId>infinispan-cachestore-sql</artifactId>
      </dependency>
  2. 打开 Data Grid 配置进行编辑。
  3. 添加 SQL 查询缓存存储。

    声明性

    query-jdbc-store xmlns="urn:infinispan:config:store:jdbc:13.0"

    programmatic

    persistence().addStore(QueriesJdbcStoreConfigurationBuilder.class)

  4. 使用 dialect=""dialect () 指定数据库死信,如 dialect="H2"dialect="postgres"
  5. 使用您需要的属性配置 SQL 缓存存储,例如:

    • 要在集群中使用相同的缓存存储,请设置 shared="true"shared (true)
    • 要创建只读缓存存储,请设置 read-only="true".ignoreModifications (true)
  6. 定义 SQL 查询语句,该语句使用 查询 元素或 queries () 方法来加载数据并修改数据库表。

    query 语句描述

    选择

    将单个条目加载到缓存中。您可以使用通配符,但必须为键指定参数。您可以使用标记的表达式。

    选择所有

    将多个条目加载到缓存中。如果返回的列数与 key 和 value 列匹配,您可以使用 * 通配符。您可以使用标记的表达式。

    SIZE

    计算缓存中条目的数量。

    DELETE

    从缓存中删除单个条目。

    全部删除

    从缓存中删除所有条目。

    UPSERT

    修改缓存中的条目。

    注意

    DELETE、DELEALLUPSERT 语句不适用于只读缓存存储,但如果缓存存储允许修改,则需要使用。

    DELETE 语句中的参数必须与 SELECT 语句中的参数完全匹配。

    UPSERT 语句中的变量必须具有与 SELECTSELECT ALL 语句返回相同的唯一命名变量数。例如,如果 SELECT 返回 foobar this 语句必须仅采用 :foo:bar 作为变量。但是,您可以在声明中多次应用同一命名变量。

    SQL 查询可以包含 JOINON 以及数据库支持的任何其他子句。

  7. 添加 schema 元素或 .schemaJdbcConfigurationBuilder () 方法,并为复合键或值添加 Protobuf 模式配置。

    1. 使用 package 属性或 package () 方法指定软件包名称。
    2. 使用 message-name 属性或 messageName () 方法指定复合值。
    3. 使用 key-message-name 属性或 keyMessageName () 方法指定复合键。
    4. 如果您的 schema 包含值 in values 中的键,则为 嵌入式-key 属性或嵌入式Key () 方法设置值 true
  8. 保存对您的配置的更改。

6.10.3.1. SQL 查询存储配置

本节提供了 SQL 查询缓存存储示例配置,它使用两个数据库表中的数据来加载分布式缓存:"person"和"address"。

SQL 语句

用于"个人"和"地址"表的 SQL 数据定义语言(DDL)语句如下:

"人"表的 SQL 语句

CREATE TABLE Person (
  name VARCHAR(255) NOT NULL,
  picture VARBINARY(255),
  sex VARCHAR(255),
  birthdate TIMESTAMP,
  accepted_tos BOOLEAN,
  notused VARCHAR(255),
  PRIMARY KEY (name)
);

"address"表的 SQL 语句

CREATE TABLE Address (
  name VARCHAR(255) NOT NULL,
  street VARCHAR(255),
  city VARCHAR(255),
  zip INT,
  PRIMARY KEY (name)
);

protobuf schema

protobuf 用于 "person" 和 "address" 表的 schema,如下所示:

protobuf schema for the "person" 表

package com.example

enum Sex {
   FEMALE = 1;
   MALE = 2;
}

message Person {
   optional string name = 1;
   optional Address address = 2;
   optional bytes picture = 3;
   optional Sex sex = 4;
   optional fixed64 birthDate = 5 [default = 0];
   optional bool accepted_tos = 6 [default = false];
}

protobuf schema for the "address" 表

package com.example

message Address {
   optional string street = 1;
   optional string city = 2 [default = "San Jose"];
   optional int32 zip = 3 [default = 0];
}

缓存配置

以下示例使用包含 JOIN 子句的 SQL 查询从"person"和"地址"表中加载分布式缓存:

XML

<distributed-cache>
  <persistence>
    <query-jdbc-store xmlns="urn:infinispan:config:store:jdbc:13.0"
                      dialect="POSTGRES"
                      shared="true">
      <queries key-columns="name">
        <select-single>SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name WHERE t1.name = :name</select-single>
        <select-all>SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name</select-all>
        <delete-single>DELETE FROM Person t1 WHERE t1.name = :name; DELETE FROM Address t2 where t2.name = :name</delete-single>
        <delete-all>DELETE FROM Person; DELETE FROM Address</delete-all>
        <upsert>INSERT INTO Person (name,  picture, sex, birthdate, accepted_tos) VALUES (:name, :picture, :sex, :birthdate, :accepted_tos); INSERT INTO Address(name, street, city, zip) VALUES (:name, :street, :city, :zip)</upsert>
        <size>SELECT COUNT(*) FROM Person</size>
      </queries>
      <schema message-name="Person"
              package="com.example"
              embedded-key="true"/>
    </query-jdbc-store>
  </persistence>
<distributed-cache>

JSON

{
  "distributed-cache": {
    "persistence": {
      "query-jdbc-store": {
        "dialect": "POSTGRES",
        "shared": "true",
        "key-columns": "name",
        "queries": {
          "select-single": "SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name WHERE t1.name = :name",
          "select-all": "SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name",
          "delete-single": "DELETE FROM Person t1 WHERE t1.name = :name; DELETE FROM Address t2 where t2.name = :name",
          "delete-all": "DELETE FROM Person; DELETE FROM Address",
          "upsert": "INSERT INTO Person (name,  picture, sex, birthdate, accepted_tos) VALUES (:name, :picture, :sex, :birthdate, :accepted_tos); INSERT INTO Address(name, street, city, zip) VALUES (:name, :street, :city, :zip)",
          "size": "SELECT COUNT(*) FROM Person"
        },
        "schema": {
          "message-name": "Person",
          "package": "com.example",
          "embedded-key": "true"
        }
      }
    }
  }
}

YAML

distributedCache:
  persistence:
    queryJdbcStore:
      dialect: "POSTGRES"
      shared: "true"
      keyColumns: "name"
      queries:
        selectSingle: "SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name WHERE t1.name = :name"
        selectAll: "SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name"
        deleteSingle: "DELETE FROM Person t1 WHERE t1.name = :name; DELETE FROM Address t2 where t2.name = :name"
        deleteAll: "DELETE FROM Person; DELETE FROM Address"
        upsert: "INSERT INTO Person (name,  picture, sex, birthdate, accepted_tos) VALUES (:name, :picture, :sex, :birthdate, :accepted_tos); INSERT INTO Address(name, street, city, zip) VALUES (:name, :street, :city, :zip)"
        size: "SELECT COUNT(*) FROM Person"
      schema:
        messageName: "Person"
        package: "com.example"
        embeddedKey: "true"

ConfigurationBuilder

ConfigurationBuilder builder = new ConfigurationBuilder();
builder.persistence().addStore(QueriesJdbcStoreConfigurationBuilder.class)
      .dialect(DatabaseType.POSTGRES)
      .shared("true")
      .keyColumns("name")
      .queriesJdbcConfigurationBuilder()
         .select("SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name WHERE t1.name = :name")
         .selectAll("SELECT t1.name, t1.picture, t1.sex, t1.birthdate, t1.accepted_tos, t2.street, t2.city, t2.zip FROM Person t1 JOIN Address t2 ON t1.name = t2.name")
         .delete("DELETE FROM Person t1 WHERE t1.name = :name; DELETE FROM Address t2 where t2.name = :name")
         .deleteAll("DELETE FROM Person; DELETE FROM Address")
         .upsert("INSERT INTO Person (name,  picture, sex, birthdate, accepted_tos) VALUES (:name, :picture, :sex, :birthdate, :accepted_tos); INSERT INTO Address(name, street, city, zip) VALUES (:name, :street, :city, :zip)")
         .size("SELECT COUNT(*) FROM Person")
      .schemaJdbcConfigurationBuilder()
         .messageName("Person")
         .packageName("com.example")
         .embeddedKey(true);