Java Code Examples for org.apache.ignite.IgniteCache#query()

@Override
public long getNumberOfAssociations(SessionFactory sessionFactory) {
	int associationCount = 0;
	IgniteDatastoreProvider datastoreProvider = getProvider( sessionFactory );
	for ( CollectionPersister collectionPersister : ( (SessionFactoryImplementor) sessionFactory ).getCollectionPersisters().values() ) {
		AssociationKeyMetadata associationKeyMetadata = ( (OgmCollectionPersister) collectionPersister ).getAssociationKeyMetadata();
		if ( associationKeyMetadata.getAssociationKind() == AssociationKind.ASSOCIATION ) {
			IgniteCache<Object, BinaryObject> associationCache = getAssociationCache( sessionFactory, associationKeyMetadata );
			StringBuilder query = new StringBuilder( "SELECT " )
										.append( StringHelper.realColumnName( associationKeyMetadata.getColumnNames()[0] ) )
										.append( " FROM " ).append( associationKeyMetadata.getTable() );
			SqlFieldsQuery sqlQuery = datastoreProvider.createSqlFieldsQueryWithLog( query.toString(), null );
			Iterable<List<?>> queryResult = associationCache.query( sqlQuery );
			Set<Object> uniqs = new HashSet<>();
			for ( List<?> row : queryResult ) {
				Object value = row.get( 0 );
				if ( value != null ) {
					uniqs.add( value );
				}
			}
			associationCount += uniqs.size();
		}
	}
	return associationCount;
}
 

/**
 *
 */
@Test
public void testMergeFromSubquery() {
    IgniteCache p = ignite(0).cache("S2P").withKeepBinary();

    p.query(new SqlFieldsQuery("merge into String (_key, _val) values ('s', ?), " +
        "('a', ?)").setArgs("Sergi", "Alex").setLocal(true));

    assertEquals("Sergi", p.get("s"));
    assertEquals("Alex", p.get("a"));

    p.query(new SqlFieldsQuery("merge into Person(_key, id, firstName) " +
        "(select substring(lower(_val), 0, 2), cast(length(_val) as int), _val from String)"));

    assertEquals(createPerson(5, "Sergi"), p.get("se"));

    assertEquals(createPerson(4, "Alex"), p.get("al"));
}
 

/**
 * @param cache Ignite cache.
 * @throws Exception If fail.
 */
private void executeContinuousQuery(IgniteCache<Object, Object> cache) throws Exception {
    ContinuousQuery<Integer, String> qry = new ContinuousQuery<>();

    qry.setLocalListener(
        new CacheEntryUpdatedListener<Integer, String>() {
            @Override public void onUpdated(Iterable<CacheEntryEvent<? extends Integer, ? extends String>> events)
                throws CacheEntryListenerException {
                for (CacheEntryEvent<? extends Integer, ? extends String> event : events)
                    System.out.println("Key = " + event.getKey() + ", Value = " + event.getValue());
            }
        }
    );

    Class<CacheEntryEventSerializableFilter> remoteFilterCls = (Class<CacheEntryEventSerializableFilter>)
        extLdr.loadClass(EXT_FILTER_CLASS);

    qry.setRemoteFilterFactory(new ClassFilterFactory(remoteFilterCls));

    cache.query(qry);

    for (int i = 0; i < 100; i++)
        cache.put(i, "Message " + i);
}
 

/** Check that joins are working on keyFieldName, valueFieldName columns */
@Test
public void testJoinKeyValFields() throws Exception {
    IgniteEx client = grid(NODE_CLIENT);
    IgniteCache<Integer, Person> cache = client.cache(CACHE_PERSON);
    IgniteCache<Integer, Integer> cache2 = client.cache(CACHE_JOB);

    checkInsert(cache, "insert into Person (id, v) values (?, ?)", 1, new Person("Bob", 30));
    checkInsert(cache, "insert into Person (id, v) values (?, ?)", 2, new Person("David", 35));
    checkInsert(cache2, "insert into Integer (_key, _val) values (?, ?)", 100, 1);
    checkInsert(cache2, "insert into Integer (_key, _val) values (?, ?)", 200, 2);

    QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("select p.id, j._key from Person p, \"" + CACHE_JOB + "\".Integer j where p.id = j._val"));
    List<List<?>> results = cursor.getAll();
    assertEquals(2, results.size());
    assertEquals(1, results.get(0).get(0));
    assertEquals(100, results.get(0).get(1));
    assertEquals(2, results.get(1).get(0));
    assertEquals(200, results.get(1).get(1));
}
 

/** */
@Test
public void testContinuousQuery() throws Exception {
    IgniteCache<Integer, Integer> cache = ignite0.createCache("cache-1");

    assertTrue(execute(ignite0, "SELECT * FROM SYS.CONTINUOUS_QUERIES").isEmpty());
    assertTrue(execute(ignite1, "SELECT * FROM SYS.CONTINUOUS_QUERIES").isEmpty());

    try (QueryCursor qry = cache.query(new ContinuousQuery<>()
        .setInitialQuery(new ScanQuery<>())
        .setPageSize(100)
        .setTimeInterval(1000)
        .setLocalListener(evts -> {
            // No-op.
        })
        .setRemoteFilterFactory(() -> evt -> true)
    )) {
        for (int i = 0; i < 100; i++)
            cache.put(i, i);

        checkContinuouQueryView(ignite0, true);
        checkContinuouQueryView(ignite1, false);
    }

    assertTrue(execute(ignite0, "SELECT * FROM SYS.CONTINUOUS_QUERIES").isEmpty());
    assertTrue(waitForCondition(() ->
        execute(ignite1, "SELECT * FROM SYS.CONTINUOUS_QUERIES").isEmpty(), getTestTimeout()));
}
 

/**
 * Test that dropping a non-existent index does not yield an error with {@code IF EXISTS}.
 */
@Test
public void testDropMissingIndexIfExists() {
    final IgniteCache<KeyClass, ValueClass> cache = cache();

    cache.query(new SqlFieldsQuery("DROP INDEX IF EXISTS \"" + IDX_NAME_1_ESCAPED + "\""));
}
 

/**
 *
 */
@Test
public void testMergeWithDynamicKeyInstantiation() {
    IgniteCache<Key, Person> p = ignite(0).cache("K2P").withKeepBinary();

    p.query(new SqlFieldsQuery(
        "merge into Person (key, id, firstName) values (1, ?, ?), (2, 2, 'Alex')").setArgs(1, "Sergi"));

    assertEquals(createPerson(1, "Sergi"), p.get(new Key(1)));

    assertEquals(createPerson(2, "Alex"), p.get(new Key(2)));
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testSelectProducesTransaction() throws Exception {
    ccfg = cacheConfiguration(cacheMode(), FULL_SYNC, 2, DFLT_PARTITION_COUNT)
        .setIndexedTypes(Integer.class, MvccTestSqlIndexValue.class);

    startGridsMultiThreaded(4);

    Random rnd = ThreadLocalRandom.current();

    Ignite node = grid(rnd.nextInt(4));

    IgniteCache<Object, Object> cache = node.cache(DEFAULT_CACHE_NAME);

    SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO MvccTestSqlIndexValue (_key, idxVal1) values (1,1),(2,2),(3,3)");

    try (FieldsQueryCursor<List<?>> cur = cache.query(qry)) {
        assertEquals(3L, cur.iterator().next().get(0));
    }

    SqlFieldsQueryEx qryEx = new SqlFieldsQueryEx("SELECT * FROM MvccTestSqlIndexValue", true);

    qryEx.setAutoCommit(false);

    try (FieldsQueryCursor<List<?>> cur = cache.query(qryEx)) {
        assertEquals(3, cur.getAll().size());
    }

    try (GridNearTxLocal tx = cache.unwrap(IgniteEx.class).context().cache().context().tm().userTx()) {
        assertNotNull(tx);
    }
}
 

/**
 * Start first, attribute-bearing, node, create new cache and launch continuous query on it.
 *
 * @return Node.
 * @throws Exception if failed.
 */
private Ignite startNodeWithCache() throws Exception {
    Ignite node1 = startGrid("node1", getConfiguration("node1", true, null));

    CacheConfiguration<Integer, Integer> ccfg = new CacheConfiguration<>(DEFAULT_CACHE_NAME);
    ccfg.setName("attrsTestCache");
    ccfg.setNodeFilter(new IgnitePredicate<ClusterNode>() {
        /** {@inheritDoc} */
        @Override public boolean apply(ClusterNode node) {
            return "data".equals(node.attribute("node-type"));
        }
    });

    IgniteCache<Integer, Integer> cache = node1.createCache(ccfg);

    ContinuousQuery<Integer, Integer> qry = new ContinuousQuery<>();

    qry.setRemoteFilterFactory(new RemoteFilterFactory());
    qry.setLocalListener(new CacheEntryUpdatedListener<Integer, Integer>() {
        /** {@inheritDoc} */
        @Override public void onUpdated(Iterable<CacheEntryEvent<? extends Integer, ? extends Integer>> evts) {
            // No-op.
        }
    });

    RemoteFilterFactory.clsLdr = getExternalClassLoader();

    cache.query(qry);

    // Switch class loader before starting the second node.
    RemoteFilterFactory.clsLdr = getClass().getClassLoader();

    return node1;
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testInsertAndFastDeleteWithoutVersionConflict() throws Exception {
    ccfg = cacheConfiguration(cacheMode(), FULL_SYNC, 2, DFLT_PARTITION_COUNT)
        .setIndexedTypes(Integer.class, Integer.class);

    startGridsMultiThreaded(2);

    IgniteCache<?, ?> cache0 = grid(0).cache(DEFAULT_CACHE_NAME);

    try (Transaction tx1 = grid(0).transactions().txStart(PESSIMISTIC, REPEATABLE_READ)) {
        // obtain tx version
        cache0.query(new SqlFieldsQuery("select * from Integer where _key = 1"));

        runAsync(() -> {
            cache0.query(new SqlFieldsQuery("insert into Integer(_key, _val) values(?, ?)").setArgs(1, 1));
        }).get();

        cache0.query(new SqlFieldsQuery("delete from Integer where _key = ?").setArgs(1));

        tx1.commit();
    }
    catch (Exception e) {
        e.printStackTrace();

        fail("Exception is not expected here");
    }
}
 

/**
 * Computes number of entries selected from the cache by the query.
 *
 * @param cache Ignite cache with upstream data.
 * @param qry Cache query.
 * @param transformer Upstream transformer.
 * @param <K> Type of a key in {@code upstream} data.
 * @param <V> Type of a value in {@code upstream} data.
 * @return Number of entries supplied by the iterator.
 */
private static <K, V> long computeCount(
    IgniteCache<K, V> cache,
    ScanQuery<K, V> qry,
    UpstreamTransformer transformer) {
    try (QueryCursor<UpstreamEntry<K, V>> cursor = cache.query(qry,
        e -> new UpstreamEntry<>(e.getKey(), e.getValue()))) {

        return computeCount(transformer.transform(Utils.asStream(cursor.iterator()).map(x -> (UpstreamEntry<K, V>)x)).iterator());
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testOrderByOnly() throws Exception {
    IgniteCache<Integer, Integer> cache = jcache(Integer.class, Integer.class);

    for (int i = 0; i < 10; i++)
        cache.put(i, i);

    QueryCursor<Cache.Entry<Integer, Integer>> q =
        cache.query(new SqlQuery<Integer, Integer>(Integer.class, "_key >= 0"));

    Collection<Cache.Entry<Integer, Integer>> res = q.getAll();

    assertEquals(10, res.size());

    if (cacheMode() != PARTITIONED) {
        Iterator<Cache.Entry<Integer, Integer>> it = res.iterator();

        for (Integer i = 0; i < 10; i++) {
            assertTrue(it.hasNext());

            Cache.Entry<Integer, Integer> e = it.next();

            assertEquals(i, e.getKey());
            assertEquals(i, e.getValue());
        }
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testSimpleIndexingSpi() throws Exception {
    indexingSpi = new MyIndexingSpi();

    Ignite ignite = startGrid(0);

    CacheConfiguration<Integer, Integer> ccfg = cacheConfiguration(DEFAULT_CACHE_NAME);

    IgniteCache<Integer, Integer> cache = ignite.createCache(ccfg);

    for (int i = 0; i < 10; i++)
        cache.put(i, i);

    QueryCursor<Cache.Entry<Integer, Integer>> cursor = cache.query(new SpiQuery<Integer, Integer>().setArgs(2, 5));

    for (Cache.Entry<Integer, Integer> entry : cursor)
        System.out.println(entry);
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testQueryFastDeleteObjectStaticCache() throws Exception {
    ccfg = cacheConfiguration(cacheMode(), FULL_SYNC, 2, DFLT_PARTITION_COUNT)
        .setIndexedTypes(Integer.class, MvccTestSqlIndexValue.class);

    startGridsMultiThreaded(4);

    Random rnd = ThreadLocalRandom.current();

    Ignite checkNode = grid(rnd.nextInt(4));
    Ignite updateNode = grid(rnd.nextInt(4));

    IgniteCache cache = checkNode.cache(DEFAULT_CACHE_NAME);

    cache.putAll(F.asMap(
        1, new MvccTestSqlIndexValue(1),
        2, new MvccTestSqlIndexValue(2),
        3, new MvccTestSqlIndexValue(3)));

    assertEquals(new MvccTestSqlIndexValue(1), cache.get(1));
    assertEquals(new MvccTestSqlIndexValue(2), cache.get(2));
    assertEquals(new MvccTestSqlIndexValue(3), cache.get(3));

    try (Transaction tx = updateNode.transactions().txStart(PESSIMISTIC, REPEATABLE_READ)) {
        tx.timeout(TX_TIMEOUT);

        SqlFieldsQuery qry = new SqlFieldsQuery("DELETE FROM MvccTestSqlIndexValue WHERE _key = 1");

        IgniteCache<Object, Object> cache0 = updateNode.cache(DEFAULT_CACHE_NAME);

        try (FieldsQueryCursor<List<?>> cur = cache0.query(qry)) {
            assertEquals(1L, cur.iterator().next().get(0));
        }

        tx.commit();
    }

    assertNull(cache.get(1));
    assertEquals(new MvccTestSqlIndexValue(2), cache.get(2));
    assertEquals(new MvccTestSqlIndexValue(3), cache.get(3));
}
 

/**
 * Run example.
 */
public static void main(String[] args) throws IOException {
    System.out.println();
    System.out.println(">>> Random Forest regression algorithm over cached dataset usage example started.");
    // Start ignite grid.
    try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) {
        System.out.println(">>> Ignite grid started.");

        IgniteCache<Integer, Vector> dataCache = null;
        try {
            dataCache = new SandboxMLCache(ignite).fillCacheWith(MLSandboxDatasets.BOSTON_HOUSE_PRICES);

            AtomicInteger idx = new AtomicInteger(0);
            RandomForestRegressionTrainer trainer = new RandomForestRegressionTrainer(
                IntStream.range(0, dataCache.get(1).size() - 1).mapToObj(
                    x -> new FeatureMeta("", idx.getAndIncrement(), false)).collect(Collectors.toList())
            ).withAmountOfTrees(101)
                .withFeaturesCountSelectionStrgy(FeaturesCountSelectionStrategies.ONE_THIRD)
                .withMaxDepth(4)
                .withMinImpurityDelta(0.)
                .withSubSampleSize(0.3)
                .withSeed(0);

            trainer.withEnvironmentBuilder(LearningEnvironmentBuilder.defaultBuilder()
                .withParallelismStrategyTypeDependency(ParallelismStrategy.ON_DEFAULT_POOL)
                .withLoggingFactoryDependency(ConsoleLogger.Factory.LOW)
            );

            System.out.println(">>> Configured trainer: " + trainer.getClass().getSimpleName());

            Vectorizer<Integer, Vector, Integer, Double> vectorizer = new DummyVectorizer<Integer>()
                .labeled(Vectorizer.LabelCoordinate.FIRST);
            ModelsComposition randomForestMdl = trainer.fit(ignite, dataCache, vectorizer);

            System.out.println(">>> Trained model: " + randomForestMdl.toString(true));

            double mse = 0.0;
            double mae = 0.0;
            int totalAmount = 0;

            try (QueryCursor<Cache.Entry<Integer, Vector>> observations = dataCache.query(new ScanQuery<>())) {
                for (Cache.Entry<Integer, Vector> observation : observations) {
                    Vector val = observation.getValue();
                    Vector inputs = val.copyOfRange(1, val.size());
                    double groundTruth = val.get(0);

                    double prediction = randomForestMdl.predict(inputs);

                    mse += Math.pow(prediction - groundTruth, 2.0);
                    mae += Math.abs(prediction - groundTruth);

                    totalAmount++;
                }

                System.out.println("\n>>> Evaluated model on " + totalAmount + " data points.");

                mse /= totalAmount;
                System.out.println("\n>>> Mean squared error (MSE) " + mse);

                mae /= totalAmount;
                System.out.println("\n>>> Mean absolute error (MAE) " + mae);

                System.out.println(">>> Random Forest regression algorithm over cached dataset usage example completed.");
            }
        }
        finally {
            if (dataCache != null)
                dataCache.destroy();
        }
    }
    finally {
        System.out.flush();
    }
}
 

/**
 * Run example.
 */
public static void main(String[] args) throws IgniteCheckedException, IOException {
    System.out.println(">>> Decision tree classification trainer example started.");

    // Start ignite grid.
    try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) {
        System.out.println(">>> Ignite grid started.");

        // Dummy cache is required to perform SQL queries.
        CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME)
            .setSqlSchema("PUBLIC");

        IgniteCache<?, ?> cache = null;
        try {
            cache = ignite.getOrCreateCache(cacheCfg);

            System.out.println(">>> Creating table with training data...");
            cache.query(new SqlFieldsQuery("create table titanic_train (\n" +
                "    passengerid int primary key,\n" +
                "    pclass int,\n" +
                "    survived int,\n" +
                "    name varchar(255),\n" +
                "    sex varchar(255),\n" +
                "    age float,\n" +
                "    sibsp int,\n" +
                "    parch int,\n" +
                "    ticket varchar(255),\n" +
                "    fare float,\n" +
                "    cabin varchar(255),\n" +
                "    embarked varchar(255)\n" +
                ") with \"template=partitioned\";")).getAll();

            System.out.println(">>> Creating table with test data...");
            cache.query(new SqlFieldsQuery("create table titanic_test (\n" +
                "    passengerid int primary key,\n" +
                "    pclass int,\n" +
                "    survived int,\n" +
                "    name varchar(255),\n" +
                "    sex varchar(255),\n" +
                "    age float,\n" +
                "    sibsp int,\n" +
                "    parch int,\n" +
                "    ticket varchar(255),\n" +
                "    fare float,\n" +
                "    cabin varchar(255),\n" +
                "    embarked varchar(255)\n" +
                ") with \"template=partitioned\";")).getAll();

            loadTitanicDatasets(ignite, cache);

            System.out.println(">>> Prepare trainer...");
            DecisionTreeClassificationTrainer trainer = new DecisionTreeClassificationTrainer(4, 0);

            System.out.println(">>> Perform training...");
            DecisionTreeNode mdl = trainer.fit(
                new SqlDatasetBuilder(ignite, "SQL_PUBLIC_TITANIC_TRAIN"),
                new BinaryObjectVectorizer<>("pclass", "age", "sibsp", "parch", "fare")
                    .withFeature("sex", BinaryObjectVectorizer.Mapping.create().map("male", 1.0).defaultValue(0.0))
                    .labeled("survived")
            );

            System.out.println("Tree is here: " + mdl.toString(true));

            System.out.println(">>> Perform inference...");
            try (QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("select " +
                "pclass, " +
                "sex, " +
                "age, " +
                "sibsp, " +
                "parch, " +
                "fare from titanic_test"))) {
                for (List<?> passenger : cursor) {
                    Vector input = VectorUtils.of(new Double[]{
                        asDouble(passenger.get(0)),
                        "male".equals(passenger.get(1)) ? 1.0 : 0.0,
                        asDouble(passenger.get(2)),
                        asDouble(passenger.get(3)),
                        asDouble(passenger.get(4)),
                        asDouble(passenger.get(5)),
                    });

                    double prediction = mdl.predict(input);

                    System.out.printf("Passenger %s will %s.\n", passenger, prediction == 0 ? "die" : "survive");
                }
            }

            System.out.println(">>> Example completed.");
        } finally {
            cache.query(new SqlFieldsQuery("DROP TABLE titanic_train"));
            cache.query(new SqlFieldsQuery("DROP TABLE titanic_test"));
            cache.destroy();
        }
    } finally {
        System.out.flush();
    }
}
 

/**
 *
 */
@Test
public void testDeleteSimple() {
    IgniteCache p = cache();

    QueryCursor<List<?>> c = p.query(new SqlFieldsQuery("delete from Person p where length(p._key) = 2 " +
        "or p.secondName like '%ite'"));

    c.iterator();

    c = p.query(new SqlFieldsQuery("select _key, _val, * from Person order by id"));

    List<List<?>> leftovers = c.getAll();

    assertEquals(2, leftovers.size());

    assertEqualsCollections(Arrays.asList("SecondKey", createPerson(2, "Joe", "Black"), 2, "Joe", "Black"),
        leftovers.get(0));

    assertEqualsCollections(Arrays.asList("f0u4thk3y", createPerson(4, "Jane", "Silver"), 4, "Jane", "Silver"),
        leftovers.get(1));
}
 

private void getUsingTheCache(Integer employeeId) {

        Ignite ignite = Ignition.ignite();

        IgniteCache<Integer, EmployeeDTO> cache = ignite.cache("baeldungCache");

        EmployeeDTO employeeDTO = cache.get(employeeId);

        System.out.println(employeeDTO);

        SqlFieldsQuery sql = new SqlFieldsQuery(
                "select * from EmployeeDTO where isEmployed = 'true'");

        QueryCursor<List<?>> cursor = cache.query(sql);
    }
 

/**
 * @param total Expected total entries.
 * @param cntrs Expected per-node entries count.
 */
private void checkLocalScan(int total, Map<UUID, Integer> cntrs) {
    Set<DbKey> allKeys = new HashSet<>();

    for (int i = 0; i < gridCount(); i++) {
        Ignite ignite0 = grid(i);

        IgniteCache<DbKey, DbValue> cache0 = ignite0.cache("non-primitive");

        int cnt = 0;

        ScanQuery<DbKey, DbValue> qry = new ScanQuery<>();

        qry.setLocal(true);

        QueryCursor<Cache.Entry<DbKey, DbValue>> cur = cache0.query(qry);

        Map<Integer, Integer> partCntrs = new HashMap<>();

        Affinity<Object> aff = ignite0.affinity(cache0.getName());

        for (Cache.Entry<DbKey, DbValue> e : cur) {
            cnt++;

            allKeys.add(e.getKey());
            assertEquals(e.getKey().val, e.getValue().iVal);

            int part = aff.partition(e.getKey());

            Integer partCntr = partCntrs.get(part);

            if (partCntr == null)
                partCntr = 1;
            else
                partCntr += 1;

            partCntrs.put(part, partCntr);
        }

        assertEquals(cntrs.get(ignite0.cluster().localNode().id()), (Integer)cnt);

        checkScanPartition(ignite0, cache0, partCntrs, true);
    }

    assertEquals(total, allKeys.size());
}
 

/**
 * Queries the specified cache using the specified filter.
 *
 * @param upstreamCache Ignite cache with {@code upstream} data.
 * @return Query cursor.
 */
private QueryCursor<Cache.Entry<K, V>> query(IgniteCache<K, V> upstreamCache) {
    ScanQuery<K, V> qry = new ScanQuery<>();

    return upstreamCache.query(qry);
}