Hello! Have you tried SELECT * FROM "PersonCache".PERSON?
Regards, -- Ilya Kasnacheev вс, 14 февр. 2021 г. в 01:33, PunxsutawneyPhil3 <[email protected]>: > 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/ >
