I am trying to configure Ignite to act as a cache with a Postgres database.
I have started with the example on the Ignite website, using an xml
configuration to define a mapping between a Person Object and a Person
table.
>From the client side I am able retrieve the cache by name, and after I call
loadCache(null), it has the correct size.
However when I try to execute queries on the cache, I get the following
error :
Failed to parse query. Table "PERSON" not found; SQL statement:
select * from PERSON
If I try and execute this query in Postgres directly it works however:
ngen=# select * from PERSON;
id | name
----+--------------
0 | Zappa, Frank
1 | Zappa, Moon
(2 rows)
I have also tried the following query variants with no luck, all reporting
that it cannot find the table:
"select * from person",
"select * from \"person\"",
"select * from \"Person\"",
"select * from PERSON",
"select * from public.PERSON",
"select * from public.person",
"select * from public.\"person\""
Any ideas on where I have this wrong?
*Client side Java code:
*
final String personCacheName = "PersonCache";
IgniteCache<Integer, Person> personCache =
WSUtil.getIgnite().cache(personCacheName);
log.info("cache size: " + personCache.size() );
personCache.loadCache(null);
log.info("cache size: " + personCache.size() );
SqlFieldsQuery sql = new SqlFieldsQuery("select * from PERSON");
try (QueryCursor<List<?>> cursor = idsCache.query(sql)) {
for (List<?> row : cursor)
System.out.println("person=" + row.get(0) +","+ row.get(1));
}catch(Exception e) {
log.error("SQL failed", e);
}
*Cache Configuration:*
<bean
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="PersonCache"/>
<property name="cacheMode" value="PARTITIONED"/>
<property name="atomicityMode" value="ATOMIC"/>
<property name="cacheStoreFactory">
<bean
class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean"
value="pgDataSource"/>
<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="PersonCache"/>
<property name="keyType"
value="java.lang.Integer"/>
<property name="valueType"
value="com.niksun.services.Person.Person"/>
<property name="databaseTable"
value="PERSON"/>
<property name="keyFields">
<list>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant
static-field="java.sql.Types.INTEGER"/>
</constructor-arg>
<constructor-arg
value="id"/>
<constructor-arg
value="int"/>
<constructor-arg
value="id"/>
</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.INTEGER"/>
</constructor-arg>
<constructor-arg
value="id"/>
<constructor-arg
value="int"/>
<constructor-arg
value="id"/>
</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="name"/>
<constructor-arg
value="java.lang.String"/>
<constructor-arg
value="name"/>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<property name="queryEntities">
<list>
<bean
class="org.apache.ignite.cache.QueryEntity">
<property name="keyType"
value="java.lang.Integer"/>
<property name="valueType"
value="com.niksun.services.Person.Person"/>
<property name="keyFieldName" value="id"/>
<property name="keyFields">
<list>
<value>id</value>
</list>
</property>
<property name="fields">
<map>
<entry key="name"
value="java.lang.String"/>
<entry key="id"
value="java.lang.Integer"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
*Table Definition:*
CREATE TABLE PERSON ( id INTEGER PRIMARY KEY, name VARCHAR(30));
*Stack Trace:*
[INFO] [Sat Feb 13 21:33:41 2021]
[ajp-nio-0:0:0:0:0:0:0:1-8009-exec-6|.....] cache size: 0
[INFO] [Sat Feb 13 21:33:57 2021]
[ajp-nio-0:0:0:0:0:0:0:1-8009-exec-6|.....] cache size: 2
[ERROR] [Sat Feb 13 21:40:33 2021]
[ajp-nio-0:0:0:0:0:0:0:1-8009-exec-6|.....] SQL failed
javax.cache.CacheException: Failed to parse query. Table "PERSON" not found;
SQL statement:
select * from PERSON [42102-197]
at com.company.dao.....
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown
Source)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy59....(Unknown Source)
at com.company.........
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown
Source)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy61.retrieveAllRule(Unknown Source)
at com.company......
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown
Source)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at
org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
at
org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:124)
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
