javax.cache.CacheException: Failed to parse query. Table not found

Question

My code starts the node and executes the loadCache() method but when SQL query is applied to it, it gives the error mentioned in the title. Here is my code for configuration, cachestore and loadcache:

Configuration.java:

public class Configuration {

/** Helper class for datasource creation. **/
public static class DataSources {
    public static final JdbcDataSource dataSource = createdataSource();

    private static JdbcDataSource createdataSource() {
        JdbcDataSource dataSource = new JdbcDataSource();

        dataSource.setURL("");
        dataSource.setUser("");
        dataSource.setPassword("");

        return dataSource;
    }
}

/**
 * Configure grid.
 * 
 * @return Ignite configuration.
 * @throws Exception If failed to construct Ignite configuration instance.
 **/
public static IgniteConfiguration createConfiguration() throws Exception {
    //int cpus = Runtime.getRuntime().availableProcessors();
    IgniteConfiguration cfg = new IgniteConfiguration();

    cfg.setClientMode(true);
    cfg.setIgniteInstanceName("Reports");

    TcpDiscoverySpi discovery = new TcpDiscoverySpi();

    TcpDiscoveryMulticastIpFinder ipFinder = new TcpDiscoveryMulticastIpFinder();

    ipFinder.setAddresses(Collections.singletonList(""));

    discovery.setIpFinder(ipFinder);

    cfg.setDiscoverySpi(discovery);


    //cfg.setPeerClassLoadingEnabled(true);

    cfg.setCacheConfiguration(cacheOutputReportCache());

    return cfg;
}



 //Configuration for cache "OutputReportCache".
public static CacheConfiguration<Long, OutputReport> cacheOutputReportCache() throws Exception {
    CacheConfiguration<Long, OutputReport> ccfg = new CacheConfiguration<>();

    ccfg.setName("OutputReportCache");
    ccfg.setCacheMode(CacheMode.PARTITIONED);
    ccfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);

    CacheJdbcPojoStoreFactory<Long, OutputReport> cacheStoreFactory = new CacheJdbcPojoStoreFactory<>();

    cacheStoreFactory.setDataSourceFactory(new Factory<DataSource>() {
        /**
         * 
         */
        private static final long serialVersionUID = 1L;

        @Override 
        public DataSource create() {
            return DataSources.dataSource;
        };
    });

    cacheStoreFactory.setDialect(new OracleDialect());

    ccfg.setCacheStoreFactory(cacheStoreFactory);

    ccfg.setReadThrough(true);
    ccfg.setWriteThrough(true);

    ArrayList<QueryEntity> qryEntities = new ArrayList<>();

    QueryEntity qryEntity = new QueryEntity();

    qryEntity.setKeyType(String.class.getName());
    qryEntity.setValueType(OutputReport.class.getName());

    LinkedHashMap<String, String> fields = new LinkedHashMap<>();

    fields.put("sid", "java.lang.String");
    fields.put("sName", "java.lang.String");
    fields.put("cname", "java.lang.String");
    fields.put("cnumber", "java.lang.String");
    fields.put("cname", "java.lang.String");
    fields.put("cid", "java.lang.String");
    fields.put("r", "java.lang.String");
    fields.put("b", "java.lang.String");
    fields.put("Date", "java.lang.String");
    fields.put("Side", "java.lang.String");
    fields.put("Quant", "java.lang.Float");
    fields.put("price", "java.lang.Float");
    fields.put("local", "java.lang.Float");
    fields.put("usd", "java.lang.Float");
    fields.put("trcy", "java.lang.String");
    fields.put("Count", "java.lang.Integer");
    fields.put("Type", "java.lang.String");
    fields.put("Category", "java.lang.String");
    fields.put("Subcategory", "java.lang.String");
    fields.put("Country", "java.lang.String");
    fields.put("eCountry", "java.lang.String");
    fields.put("Desc", "java.lang.String");
    fields.put("Sector", "java.lang.String");
    fields.put("isector", "java.lang.String");
    fields.put("Flag", "java.lang.String");
    fields.put("Region", "java.lang.String");
    fields.put("rowNum", "java.lang.Long");

    qryEntity.setFields(fields);

 // Listing indexes.
    Collection<QueryIndex> indexes = new ArrayList<>(3);

    indexes.add(new QueryIndex("sid"));
    indexes.add(new QueryIndex("Region"));
    indexes.add(new QueryIndex("cnumber"));
    indexes.add(new QueryIndex("eCountry"));

    qryEntity.setIndexes(indexes);

    qryEntities.add(qryEntity);

    ccfg.setQueryEntities(qryEntities);

    return ccfg;
}
}

OutputReportStore.java:

public class OutputReportStore implements CacheStore<Long, OutputReport> {
    //@SpringResource(resourceName = "dataSource")
    private DataSource dataSource = DataSources.dataSource;
    // This method is called whenever IgniteCache.loadCache() method is called.
    @Override
    public void loadCache(IgniteBiInClosure<Long, OutputReport> clo, @Nullable Object... objects) throws CacheLoaderException {
        System.out.println(">> Loading cache from store...");
        try (Connection conn = dataSource.getConnection()) {
            try (PreparedStatement st = conn.prepareStatement("select * from OUTPUTREPORT")) {
                try (ResultSet rs = st.executeQuery()) {
                    while (rs.next()) {
                        OutputReport outputreport = new OutputReport(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getFloat(11), rs.getFloat(12), rs.getFloat(13), rs.getFloat(14), rs.getString(15), rs.getInt(16), rs.getString(17), rs.getString(18), rs.getString(19), rs.getString(20), rs.getString(21), rs.getString(22), rs.getString(23), rs.getString(24), rs.getString(25), rs.getString(26), rs.getLong(27));
                        clo.apply(outputreport.getrowNum(), outputreport);
                    }
                }
            }
        }
        catch (SQLException e) {
            throw new CacheLoaderException("Failed to load values from cache store.", e);
        }
    }

    // Other CacheStore method implementations.
    @Override
    public OutputReport load(Long arg0) throws CacheLoaderException {
        // TODO Auto-generated method stub
        return null;
    }
    @Override
    public void delete(Object arg0) throws CacheWriterException {
        // TODO Auto-generated method stub

    }
    @Override
    public void write(Entry<? extends Long, ? extends OutputReport> arg0)
            throws CacheWriterException {
        // TODO Auto-generated method stub

    }
    @Override
    public Map<Long, OutputReport> loadAll(Iterable<? extends Long> arg0)
            throws CacheLoaderException {
        // TODO Auto-generated method stub
        return null;
    }
    @Override
    public void deleteAll(Collection<?> arg0) throws CacheWriterException {
        // TODO Auto-generated method stub

    }
    @Override
    public void writeAll(
            Collection<Entry<? extends Long, ? extends OutputReport>> arg0)
            throws CacheWriterException {
        // TODO Auto-generated method stub

    }
    @Override
    public void sessionEnd(boolean arg0) throws CacheWriterException {
        // TODO Auto-generated method stub

    }

}

LoadCaches.java:

public class LoadCaches {
    //Load caches from database.
    //1. Start cluster
    //2. Start this utility and wait while load complete

    public static void main(String[] args) throws Exception {
        try (Ignite ignite = Ignition.start(Configuration.createConfiguration())) {

            System.out.println(">>> Loading cache: OutputReportCache");
            IgniteCache<Long, OutputReport> cache = ignite.getOrCreateCache("OutputReportCache");
    cache.loadCache(null);

    System.out.println(">>> Cache loaded!");

    QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("select count(*) from outputreport"));
    System.out.println(cursor.getAll());
        }
    }
}

Here is the stack trace:

[16:59:22] Ignite node started OK (id=90c9464d, instance name=Reports) [16:59:22] Topology snapshot [ver=29, servers=1, clients=6, CPUs=12, offheap=90.0GB, heap=36.0GB] [16:59:22] ^-- Node [id=90C9464D-A48A-4DB7-8812-65CE5FD4B8FD, clusterState=ACTIVE]

Loading cache: OutputReportCache Cache loaded! Exception in thread "main" javax.cache.CacheException: Failed to parse query. Table "OUTPUTREPORT" not found; SQL statement: select count() from outputreport [42102-196] at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676) at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615) at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356) at project4.LoadCaches.main(LoadCaches.java:26) Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query. Table "OUTPUTREPORT" not found; SQL statement: select count() from outputreport [42102-196] at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2026) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:1796) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1652) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2035) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2030) at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2578) at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2044) at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:664) ... 3 more Caused by: org.h2.jdbc.JdbcSQLException: Table "OUTPUTREPORT" not found; SQL statement: select count(*) from outputreport [42102-196] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.command.Parser.readTableOrView(Parser.java:5552) at org.h2.command.Parser.readTableFilter(Parser.java:1266) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1946) at org.h2.command.Parser.parseSelectSimple(Parser.java:2095) at org.h2.command.Parser.parseSelectSub(Parser.java:1940) at org.h2.command.Parser.parseSelectUnion(Parser.java:1755) at org.h2.command.Parser.parseSelect(Parser.java:1743) at org.h2.command.Parser.parsePrepared(Parser.java:449) at org.h2.command.Parser.parse(Parser.java:321) at org.h2.command.Parser.parse(Parser.java:293) at org.h2.command.Parser.prepareCommand(Parser.java:258) at org.h2.engine.Session.prepareLocal(Session.java:578) at org.h2.engine.Session.prepareCommand(Session.java:519) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepare0(IgniteH2Indexing.java:484) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:452) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:419) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2008) ... 11 more

How can this be resolved?

Answer 1

I guess, the root cause of NullPointerException exception is that the CacheJdbcPojoStoreFactory#types is not defined in your code. It can be done via Java API as follows:

JdbcType storeType = new JdbcType;
storeType.setDatabaseSchema("Your-custom-schema");
storeType.setDatabaseTable("Table-name");
storeType.setKeyType(Long.class);
storeType.setValueType(OutputReport.class);
storeType.setValueFields(new JdbcTypeField(Types.VARCHAR, "sName", String.class, null));
...

cacheStoreFactory.setTypes(storeType);

You can find an example here: CacheJdbcPojoStoreTest