配置点燃缓存与Postgresql作为第三方持久化使用复合主键表

Question

我想配置一个点燃缓存。缓存应该装载来自postgresql数据库的数据。我设法为具有简单(一列)主键的表配置了一些缓存。现在我遇到了一些问题配置缓存的表与复合主键(由2列组成)。我正在使用tpc-h数据库模式。问题发生在缓存表"lineitem"时。主键由两列组成&;l_orderkey&;和";l_linenumber"。我尝试了以下配置:

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/util  http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!-- Data source bean -->
<bean id="postgresDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://postgres-service:5432/db" />
<property name="username" value="#################" />
<property name="password" value="#################" />
</bean>
<!-- Ignite Configuration -->
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<!-- Configuration for LineitemCache -->
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="LineitemCache"/>
<property name="cacheMode" value="REPLICATED"/>
<property name="atomicityMode" value="ATOMIC"/>
<property name="cacheStoreFactory">
<bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean" value="postgresDataSource"/>
<property name="dialect">
<bean class="org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect"/>
</property>
<property name="types">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcType">
<property name="cacheName" value="LineitemCache"/>
<property name="keyType" value="###.###.LineitemKey"/>
<property name="valueType" value="###.###.Lineitem"/>
<!--Specify the schema if applicable -->
<property name="databaseSchema" value="public"/>
<property name="databaseTable" value="lineitem"/>
<property name="keyFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_orderkey"/>
<constructor-arg value="long"/>
<constructor-arg value="l_orderkey"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_linenumber"/>
<constructor-arg value="long"/>
<constructor-arg value="l_linenumber"/>
</bean>
</list>
</property>
<property name="valueFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_orderkey"/>
<constructor-arg value="long"/>
<constructor-arg value="l_orderkey"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_partkey"/>
<constructor-arg value="long"/>
<constructor-arg value="l_partkey"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_suppkey"/>
<constructor-arg value="long"/>
<constructor-arg value="l_suppkey"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.BIGINT"/>
</constructor-arg>
<constructor-arg value="l_linenumber"/>
<constructor-arg value="long"/>
<constructor-arg value="l_linenumber"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DOUBLE"/>
</constructor-arg>
<constructor-arg value="l_quantity"/>
<constructor-arg value="double"/>
<constructor-arg value="l_quantity"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DOUBLE"/>
</constructor-arg>
<constructor-arg value="l_extendedprice"/>
<constructor-arg value="double"/>
<constructor-arg value="l_extendedprice"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DOUBLE"/>
</constructor-arg>
<constructor-arg value="l_discount"/>
<constructor-arg value="double"/>
<constructor-arg value="l_discount"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DOUBLE"/>
</constructor-arg>
<constructor-arg value="l_tax"/>
<constructor-arg value="double"/>
<constructor-arg value="l_tax"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.CHAR"/>
</constructor-arg>
<constructor-arg value="l_returnflag"/>
<constructor-arg value="char"/>
<constructor-arg value="l_returnflag"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.CHAR"/>
</constructor-arg>
<constructor-arg value="l_linestatus"/>
<constructor-arg value="char"/>
<constructor-arg value="l_linestatus"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DATE"/>
</constructor-arg>
<constructor-arg value="l_shipdate"/>
<constructor-arg value="java.sql.Date"/>
<constructor-arg value="l_shipdate"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DATE"/>
</constructor-arg>
<constructor-arg value="l_commitdate"/>
<constructor-arg value="java.sql.Date"/>
<constructor-arg value="l_commitdate"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.DATE"/>
</constructor-arg>
<constructor-arg value="l_receiptdate"/>
<constructor-arg value="java.sql.Date"/>
<constructor-arg value="l_receiptdate"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
<constructor-arg value="l_shipinstruct"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="l_shipinstruct"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
<constructor-arg value="l_shipmode"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="l_shipmode"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
<constructor-arg value="l_comment"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="l_comment"/>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<!-- Configure query entities if you want to use SQL queries -->
<property name="queryEntities">
<list>
<bean class="org.apache.ignite.cache.QueryEntity">
<property name="keyType" value="###.###.LineitemKey"/>
<property name="valueType" value="###.###.Lineitem"/>
<property name="keyFields">
<list>
<value>l_orderkey</value>
<value>l_linenumber</value>
</list>
</property>
<property name="fields">
<map>
<entry key="l_orderkey" value="java.lang.Long"/>
<entry key="l_partkey" value="java.lang.Long"/>
<entry key="l_suppkey" value="java.lang.Long"/>
<entry key="l_linenumber" value="java.lang.Long"/>
<entry key="l_quantity" value="java.lang.Double"/>
<entry key="l_extendedprice" value="java.lang.Double"/>
<entry key="l_discount" value="java.lang.Double"/>
<entry key="l_tax" value="java.lang.Double"/>
<entry key="l_returnflag" value="java.lang.Char"/>
<entry key="l_linestatus" value="java.lang.Char"/>
<entry key="l_shipdate" value="java.sql.Date"/>
<entry key="l_commitdate" value="java.sql.Date"/>
<entry key="l_receiptdate" value="java.sql.Date"/>
<entry key="l_shipinstruct" value="l_shipinstruct"/>
<entry key="l_shipmode" value="l_shipmode"/>
<entry key="l_comment" value="l_comment"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</beans>

LineitemKey.java

public class LineitemKey {
private long l_orderkey;
private long l_linenumber;

public LineitemKey(long l_orderkey, long l_linenumber) {
this.l_linenumber = l_linenumber;
this.l_orderkey = l_orderkey;
}

public LineitemKey() {};

public long getL_orderkey() {
return l_orderkey;
}
public void setL_orderkey(long l_orderkey) {
this.l_orderkey = l_orderkey;
}
public long getL_linenumber() {
return l_linenumber;
}
public void setL_linenumber(long l_linenumber) {
this.l_linenumber = l_linenumber;
}
}

Lineitem.java

import java.io.Serializable;
import java.sql.Date;
public class Lineitem implements Serializable {
private static final long serialVersionUID = 1L;
private long l_orderkey;
private long l_partkey;
private long l_suppkey;
private long l_linenumber;
private double l_quantity;
private double l_extendedprice;
private double l_discount;
private double l_tax;
private char l_returnflag;
private char l_linestatus;
private Date l_shipdate;
private Date l_commitdate;
private Date l_receiptdate;
private String l_shipinstruct;
private String l_shipmode;
private String l_comment;   

public Lineitem() {
}
public long getL_orderkey() {
return l_orderkey;
}
public void setL_orderkey(long l_orderkey) {
this.l_orderkey = l_orderkey;
}
public long getL_partkey() {
return l_partkey;
}
public void setL_partkey(long l_partkey) {
this.l_partkey = l_partkey;
}
public long getL_suppkey() {
return l_suppkey;
}
public void setL_suppkey(long l_suppkey) {
this.l_suppkey = l_suppkey;
}
public long getL_linenumber() {
return l_linenumber;
}
public void setL_linenumber(long l_linenumber) {
this.l_linenumber = l_linenumber;
}
public double getL_quantity() {
return l_quantity;
}
public void setL_quantity(double l_quantity) {
this.l_quantity = l_quantity;
}
public double getL_extendedprice() {
return l_extendedprice;
}
public void setL_extendedprice(double l_extendedprice) {
this.l_extendedprice = l_extendedprice;
}
public double getL_discount() {
return l_discount;
}
public void setL_discount(double l_discount) {
this.l_discount = l_discount;
}
public double getL_tax() {
return l_tax;
}
public void setL_tax(double l_tax) {
this.l_tax = l_tax;
}
public char getL_returnflag() {
return l_returnflag;
}
public void setL_returnflag(char l_returnflag) {
this.l_returnflag = l_returnflag;
}
public char getL_linestatus() {
return l_linestatus;
}
public void setL_linestatus(char l_linestatus) {
this.l_linestatus = l_linestatus;
}
public Date getL_shipdate() {
return l_shipdate;
}
public void setL_shipdate(Date l_shipdate) {
this.l_shipdate = l_shipdate;
}
public Date getL_commitdate() {
return l_commitdate;
}
public void setL_commitdate(Date l_commitdate) {
this.l_commitdate = l_commitdate;
}
public Date getL_receiptdate() {
return l_receiptdate;
}
public void setL_receiptdate(Date l_receiptdate) {
this.l_receiptdate = l_receiptdate;
}
public String getL_shipinstruct() {
return l_shipinstruct;
}
public void setL_shipinstruct(String l_shipinstruct) {
this.l_shipinstruct = l_shipinstruct;
}
public String getL_shipmode() {
return l_shipmode;
}
public void setL_shipmode(String l_shipmode) {
this.l_shipmode = l_shipmode;
}
public String getL_comment() {
return l_comment;
}
public void setL_comment(String l_comment) {
this.l_comment = l_comment;
}
}

对我来说这似乎是一个正确的配置。使用此配置运行集群将导致空缓存。没有数据从postgresql加载到缓存。如果我将一个只有一列作为主键的配置传递给节点，那么只有一些行(而不是所有行)被加载到缓存中。对于选定的(单个)主键行，每个键只能有一行。

问题:如何配置缓存以使用组合键来标识每一行?

Answer 1

看起来像是某个地方配置错误。看一下源代码测试，有PERSON_COMPLEX的定义:

storeTypes[2] = new JdbcType();
storeTypes[2].setDatabaseSchema("PUBLIC");
storeTypes[2].setDatabaseTable("PERSON_COMPLEX");
storeTypes[2].setKeyType("org.apache.ignite.cache.store.jdbc.model.PersonComplexKey");
storeTypes[2].setKeyFields(
new JdbcTypeField(Types.INTEGER, "ID", int.class, "id"),
new JdbcTypeField(Types.INTEGER, "ORG_ID", int.class, "orgId"),
new JdbcTypeField(Types.INTEGER, "CITY_ID", int.class, "cityId"));

Key POJO和DDL:

stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " +
"Person_Complex (id integer not null, org_id integer not null, city_id integer not null, " +
"name varchar(50), salary integer, PRIMARY KEY(id, org_id, city_id))");